Random Musings on MySQL Order By Optimizations

Was attempting to optimize a query for some additional functionality we are going to be releasing soon and sadly to say I failed. I was hoping to get the query execution time down to 0.01 seconds, but the best I could muster was a measly 0.03 seconds.

The goal of the query is to pull all unanswered questions (note: the design of the database predates me by over 5 years), sort them by a specific product (stored as a varchar), and to next include the products sibling products (all hair dryers for instance). The fact that the products model must be a varchar and is not numeric already increases execution time and the answered column is a varchar as well when it should really by a boolean or tinyint. Those things are out of my control though.

The first thought that comes to my head is using an IF statement to return a 1 or a 0 based on matching the product_id value and then ordering by the ‘relevance’ column. This executes in 0.0323 sec.

Next I try some strange conditional ORDER BY with a SWITCH statement mixed in. Kinda ugly and kinda slower at 0.0568 sec.

Worst yet is the UNION executing in 0.0666 sec.

Is there a better way to do what I’m attempting?

3 Comments