Interesting game: Mono
Another game for the to-play list: Mono.
(Recommended by Nelson Minar.)
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).
Another game for the to-play list: Mono.
(Recommended by Nelson Minar.)
Brilliant Paul Graham parody: Taste for the Web.
(via effbot. More from the author.)
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.
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).