I love joined subqueries in MySQL

So I was looking into ways of improving the load times of a project, and knowing SQL is one of my biggest weaknesses (in my own humble opinion), I figured there might be a fair bit to gain by researching a bit around it, which lead to a friend linking me to http://www.chrislondon.co/joins-vs-subqueries/

Judging by his benchmarks on a low amount of data points, he discovered a massive leap between a subquery and a joined subquery. I’ve been using join statements all along (and in some cases multiple queries like the schmuck I am) so I figured it was worth giving this a shot on the data I was working with. I mention the data points as it was put up in the comments that the amount of data he queried against was very low, I hit mine against ~50 000 rows of data

I made a dump of the live data from the system in production and started running queries.

The numbers I got out of it were quite amazing (I’ll leave the query I ran at the bottom for public scrutiny and humiliation).

The default view is to return 30 entries per page, with my old approach this took 0.6 seconds, which is a frighteningly long time in my opinion, even if it isn’t a front end system.

Then came the query I have below, the same 30 rows were returned in 0.02 seconds, a very substantial leap, so I got experimental, and I kept increasing the rows I pulled with the new query, wanting to see just how big of a leap I could make.

The final number? 5 000 rows of data to just barely break 0.6 seconds! That’s quite the improvement on the meager 30 rows it managed before, that’s for sure.

Now as you’ll notice, I only took the heavy parts of the query and put into subqueries for now, there are still a few regular join statements left in the query, I might experiment some more but didn’t want to mess up the entire system in one go.

And yes, it’s terrible, SQL is far from my strong suit, and I’ll gladly be told better ways of approaching things in the query so do let me know!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.