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.