Programmer's Mindset

Mysql Order By Clause Performance

Just had a strange query happen that I thought I would share.  I am by no means a Mysql expert, I know enough to usually write decent queries that are performant.

I had one today that was baffling me.  First, I know this should be extracted into reusable code, but right now that’s not the plan.  In one part of our system a query was happening pretty quickly.  In a new part that I am working on it was taking forever, to the point I had to kill the query (after 30+ seconds) so that it didn’t bring the entire system down.

I was trying everything to figure out why one was worse than the other.  They both seemed like terrible queries from first sight.  I rearranged the WHERE clause so that they matched, and that didn’t fix the problem.

The good query had: ORDER BY dateadded desc, id

The bad query had: ORDER BY dateadded desc

I guess that is something to try when you have non performant queries.  There was a subquery happening as well, so I think the order by may be helping the subquery.

It was just interesting to me and I thought I would share.