This post originated from an RSS feed registered with Python Buzz by Phillip Pearson. Original Post: Make sure your indices are actually being used Feed Title: Second p0st Feed URL: http://www.myelin.co.nz/post/rss.xml Feed Description: Tech notes and web hackery from the guy that brought you bzero, Python Community Server, the Blogging Ecosystem and the Internet Topic Exchange Latest Python Buzz Posts Latest Python Buzz Posts by Phillip Pearson Latest Posts From Second p0st

MySQL tuning tip: check that your indices are correct, and that they are actually being used.

Part 1: getting your indexes right

MySQL uses one index per query. If you do two things in a query, they have to be do-able with the single index. For example, you might want to select out some rows and order them. Here's an example table:

CREATE TABLE foo (a INT, b VARCHAR(255),
KEY foo_a (a), KEY foo_b (b));

We've got two keys here, but only one will be used per query. So both of these queries will be quick:

SELECT * FROM foo WHERE a=1
SELECT * FROM foo WHERE b="asdf"

... but this query will require a filesort:

SELECT * FROM foo WHERE a=1 ORDER BY b

To make the last query fast, you need to have a key on (a, b). Your table would probably end up like this:

CREATE TABLE foo (a INT, b VARCHAR(255),
KEY foo_a_b (a, b), KEY foo_b (b));

This way, queries involving just column a, or both a and b, will use foo_a_b, and queries involving just column b will use foo_b.

Part 2: making sure they are being used

Even though you have an index that is perfect for your query, MySQL might not be using it.

I had a table with about 150,000 rows that looked like this:

CREATE TABLE foo (a INT, b VARCHAR(255), c INT,
KEY foo_a (a), KEY foo_a_b (a, b), KEY foo_a_c (a, c));

.. and was executing a query that looked like this like this:

SELECT * FROM foo WHERE a=1 ORDER BY c DESC LIMIT 10

This will work best with the (a, c) key, but it seems that MySQL's query optimiser didn't consider the ORDER BY clause, and it ended up choosing the (a) key, and resulting in a filesort on 150K rows - ouch!

The (a) key is redundant anyway, so I got rid of it:

ALTER TABLE foo DROP KEY foo_a

Now EXPLAIN SELECT was telling me that it would use the (a, b) key. Not good. Sometimes I've been able to fix this by re-analyzing the table:

ANALYZE TABLE foo

In this case, it worked, and EXPLAIN SELECT then found that the (a, c) key would let it do the query without a filesort. If the ANALYZE TABLE hadn't helped, I could have changed the query to look like this:

SELECT * FROM foo USE INDEX(foo_a_c) WHERE a=1 ORDER BY c DESC LIMIT 10

Final note

If I said anything in this blog entry that you didn't already know about, try reading the excellent High Performance MySQL (see O'Reilly catalog entry), which covers this sort of thing in great detail.

Tomorrow: How MySQL fails to optimise LIMIT.

Comment