DBA > Job Interview Questions > MySQL and SQL

MySQL - Speed of SELECT Queries

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

MySQL - Speed of SELECT Queries

In general, when you want to make a slow SELECT ... WHERE faster, the first thing to check is whether or not you can add an index.

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.

Some general tips:

To help MySQL optimize queries better, run myisamchk --analyze on 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 the analyze run by doing SHOW INDEX FROM table_name and examining the Cardinality column.
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!

(Continued on next question...)

Other Job Interview Questions