MySQL Explain Plans

MySQL Explain Plan

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.

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.

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

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

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

Published: Thursday, 8 December 2005