MySQL Manual
5 MySQL Optimisation
- 5.2 Optimising
SELECTs and Other Queries- 5.2.1
EXPLAINSyntax (Get Information About aSELECT) - 5.2.2 Estimating Query Performance
- 5.2.3 Speed of
SELECTQueries - 5.2.4 How MySQL Optimises
WHEREClauses - 5.2.5 How MySQL Optimises
IS NULL - 5.2.6 How MySQL Optimises
DISTINCT - 5.2.7 How MySQL Optimises
LEFT JOINandRIGHT JOIN - 5.2.8 How MySQL Optimises
ORDER BY - 5.2.9 How MySQL Optimises
LIMIT - 5.2.10 Speed of
INSERTQueries - 5.2.11 Speed of
UPDATEQueries - 5.2.12 Speed of
DELETEQueries - 5.2.13 Other Optimisation Tips
- 5.2.1
5.2.3 Speed of SELECT Queries
In general, when you want to make a slow SELECT ... WHERE faster, the
first thing to check is whether you can add an index. See section 5.4.3 How MySQL Uses Indexes. All references between different tables
should usually be done with indexes. You can use the EXPLAIN command
to determine which indexes are used for a SELECT.
See section 5.2.1 EXPLAIN Syntax (Get Information About a SELECT).
Some general tips:
-
To help MySQL optimise queries better, run
myisamchk --analyzeon a table after it has been loaded with relevant 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, of course.) MySQL will use this to decide which index to choose when you connect two tables with 'a non-constant expression'. You can check the result from theanalyzerun by doingSHOW INDEX FROM table_nameand examining theCardinalitycolumn. -
To sort an index and data according to an index, use
myisamchk --sort-index --sort-records=1(if you want to sort on index 1). If you have a unique index from which you want to read all records in order according to that index, this is a good way to make that faster. Note, however, that this sorting isn't written optimally and will take a long time for a large table!
