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...)
