When examining the execution plan of troublesome queries in MySQL, most users are aware of using EXPLAIN. However, an often overlooked, yet very helpful extension of EXPLAIN, is EXPLAIN EXTENDED coupled with the SHOW WARNINGS command.
The reason being is because it provides a little more information about how the optimizer processes the query, and thus it could help to quickly identify a problem that you might not otherwise recognize with just EXPLAIN.
For instance, here is a common query which could be inefficient:
SELECT id FROM t WHERE id='1';
And here is the CREATE TABLE output:
mysql> show create table tG[Read more...]