In general, when you want to make a slow
WHERE query faster, the first thing to check is
whether you can add an index. All references between different
tables should usually be done with indexes. You can use the
EXPLAIN statement to determine
which indexes are used for a
Section 7.2.1, “Optimizing Queries with EXPLAIN”, and
Section 7.4.3, “How MySQL Uses Indexes”.
Some general tips for speeding up queries on
To help MySQL better optimize queries, use
ANALYZE TABLE or run
myisamchk --analyze on a table after it
has been loaded with data. This updates a value for each
index part that indicates the average number of rows that
have the same value. (For unique indexes, this is always
1.) MySQL uses this to decide which index to choose when
you join two tables based on a nonconstant expression. You
can check the result from the table analysis by using
SHOW INDEX FROM
myisamchk --description --verbose shows
index distribution information.
To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (assuming that you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all rows in order according to the index. The first time you sort a large table this way, it may take a long time.