18.2.5 Use the EXPLAIN statementThis is NOT the latest copy of this book; click here for the latest version.
Precede any valid SELECT statement with the word "EXPLAIN" and MySQL will explain to you how it would process that SELECT - how many rows it would have returned, etc, which is a great way to spot problems. The faster of the two queries from above, when used with EXPLAIN, produces the following output:
+--------+------+---------------+------+---------+------+--------+------------+
| table | type | possible_keys | key | key_len | re f | rows | Extra |
+--------+------+---------------+------+---------+------+--------+------------+
| mydata | ALL | NULL | NULL | NULL | NULL | 199999 | where used |
+--------+------+---------------+------+---------+------+--------+------------+
The important parts are "possible_keys", "rows", and "Extra". Possible keys shows "NULL", which means MySQL cannot run the query using indexes - we will be solving that later. Rows shows how many rows MySQL would have had to search through, and "Extra" shows what extra functionality it would have used to perform the search - here we can see it would have filtered the search using the where clause.
|
Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
