MySQL Explain Plans

Published: Thursday, 8 December 2005
mysql

Use the EXPLAIN keyword before your select statement.

GUI - phpMyAdmin

If you are hosted by us, you can access this by using phpMyAdmin in the webhosting control panel.

a slow select statement

In the example above, the query took over 16 seconds! To find out what’s taking so long, you can use the Explain SQL button to hint at which parts of the query will take the longest.

explained select statement - slow

The rows column gives a good indication about the amount of data that needs to be processed. COALESCE was not allowing the unique id index to be used on the suburb table.

Changing COALESCE(1, s.id) to 1 gives us a more efficient response by allowing the unique index to be used.

explained select statement - improved

The cost of the suburbs table has now been reduced from 3296 rows to 1.