Blog‎ > ‎

Query Optimization

posted Nov 2, 2016, 6:15 PM by Matt Himrod   [ updated Nov 2, 2016, 6:21 PM ]
So... who makes a blog and leaves it empty for months... apparently this guy.

I struggle with blogging because I don't often think that noteworthy events occur, but last week I had something that I wanted to write about. Query optimization. Specifically optimizing a query in a way that surprised me.

I've worked with MySQL for nearly 9 years. I've gotten into the habit of trying to write queries with efficiency in the back of my mind. We have some rather large customer databases, and I'm impatient, so if I can write a query that I don't have to wait for, I win. 

Unfortunately with this example, I can't really give specifics. I was writing a query against our largest customer's database in the database's largest table. It seemed rather simple:

SELECT MAX(indexed_date_field)
FROM very_big_table
WHERE indexed_foreign_key > 100 AND 
      unindexed_field = 'enumerated_value';

This is a fast query in the average customer's database, but in this one, I waited for over 10 minutes before giving up. Running it with EXPLAIN yielded the culprit: the query plan included inspecting over 10 million records! I couldn't leave anything out of this query. I added a constraint limiting the indexed_date_field to more recent dates, but the query plan was still examining millions of records. On a whim, I said to myself, "I could do this by hand faster by sorting by the indexed_date_field and finding the first one where unindexed_field equals the enumerated_value! So, just for kicks, I rewrote it like this:

SELECT indexed_date_field
FROM very_big_table
WHERE indexed_foreign_key > 100 AND 
      unindexed_field = 'enumerated_value'
ORDER BY indexed_date_field DESC

It ran in .2 seconds. The query plan examined 2 records. It make sense because it sorts with the index and then finds the first record meeting the criteria in the WHERE clause. I've vaguely recalled reading about this sort of query optimization but it's something I always forget about. Still...



I don't think I'll forget this one anytime soon.