Phillip Pearson - web + electronics notes

tech notes and web hackery from a new zealander who was vaguely useful on the web back in 2002 (see: python community server, the blogging ecosystem, the new zealand coffee review, the internet topic exchange).

2005-2-10

Interesting game: Mono

Another game for the to-play list: Mono.

(Recommended by Nelson Minar.)

... more like this: []

"It's no coincidence that the star-nosed mole's claws are curved like parentheses"

Brilliant Paul Graham parody: Taste for the Web.

(via effbot. More from the author.)

... more like this: []

Make sure your indices are actually being used

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.

... more like this: []

SET AUTOCOMMIT=0

If you are going to do a lot of INSERT queries into an InnoDB table, you can save a lot of time by wrapping them all up into a single transaction. MySQL flushes various things to disk at the end of a transaction, so you save a lot of time waiting for disks to flush their caches if you have a million little transactions rather than one huge one.

Before you do your big job, send this query:

SET AUTOCOMMIT=0

and at the end:

COMMIT

(you might want to SET AUTOCOMMIT=1 if you want to go back to normal behaviour for the rest of the script / db connection).

... more like this: []