I've been learning more about MySQL lately and particularly optimising SQL queries on large tables. Large, in this case, being at the moment hundreds of thousands of rows but soon to be millions. One of the problems I've had is that MySQL sometimes decides not to use an index even when a handy one seems to have been created for it. The root of this appears to be to that with B-tree indexes if there are a large number of records with similar looking values then the MySQL engine may decide that it's just as much effort using the index as to search the whole table.

The answer appears to be adding PACK_KEYS = 1 to the end of a create table, or running the SQL command ALTER TABLE MyTable PACK_KEYS = 1 once the table has been created. In effect, this takes account of the similarity of adjacent keys. In our case we have a large column of field type bigint(21) where the starting digits of the index are timestamp generated. So, at present, we end up with a few tens of thousand rows all starting with 108xx. Enabling packed keys means not only that the index is smaller as MySQL only needs to store the differences between keys (plus an extra byte to keep track of where the similarity starts) but also that the index is actually of some use i.e. doesn't become a large, flat structure.

One down side of using packed keys is that inserts are slower, but given that the system we are building is inserting each row once and then (in theory) never touching it again that's a small price to pay. The other major drawback, however, is that packed keys only works on MyISAM tables at present and not InnoDB. This actually isn't much use to me as the large inserts we occassionally have to do would end up with MyISAM locking the table for perhaps an hour or more.

Pack keys reference in the MySQL manual