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.
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.
COALESCE(1, s.id) 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.