Re:Get to know your ORM and take control of joins.
by jiggy2011 on Wednesday July 18, 2012 @10:31AM
(#4260860)
The test was done with DB and HTTP servers on the same box.
I tested both the HTTP response times from another box and also the time for the SQL queries to return inside the same box. Indexes were setup for the joins and there would have been enough RAM to store the dataset.
I did not really bare IO in mind doing the test because if IO was the bottleneck then I would have optimised enough.
Certainly a bad test from anything approaching a scientific view but I had expected to see a performance difference even in this scenario (especially when testing the turnaround between the Java app and Mysql).
I think my faulty logic at the time was that N+1 selects would be slow because of the overhead of parsing multiple queries. What led to to that conclusion was that I had previously optimised an old PHP app that was using mysql_query() to use joins rather than millions of selects and more or less got 10x performance back.
Re:Get to know your ORM and take control of joins.
by fatalmind on Wednesday July 18, 2012 @08:35AM
(#4260417)
Besides your points, we must also consider that MySQL does neither support hash joins nor sort/merge join. MySQL it just not very good at joining.
Nevertheless, a MySQL join should never be slower than the N+1 select approach.
Benchmarks are hard.
What were you testing exactly? The behaviour you are seeing is surprising, but if the database server is on the same physical server as the app server and you are testing for response time without loading the server I could see how it could happen.
Did you have indexes setup to support the joins?
Did you have sufficient RAM for the join to be done in memory?
Was the database server hitting IO limits?