EXPLAIN SELECT L.listing_id, L.category, L.property_name, L.island, L.location, L.district, L.hide, L.price, L.featured, L.hide, L.expiry, CATISL.category AS islandString, CATLOC.category AS locationString, CATDIS.category AS districtString, M.type, M.filename, M.caption
FROM listings AS L
LEFT JOIN categories AS CATISL ON L.island = CATISL.id
LEFT JOIN categories AS CATLOC ON L.location = CATLOC.id
LEFT JOIN categories AS CATDIS ON L.district = CATDIS.id
LEFT JOIN media AS M ON M.parent_listing = L.listing_id
AND M.type = 'photo'
WHERE L.cms_status = 'complete'
AND L.category = 'residential'
GROUP BY listing_id
ORDER BY hide ASC , expiry DESC , listing_id ASC
table type possible_keys key key_len ref rows Extra
L ref properpublic properpublic 16 const 1338 Using where; Using temporary; Using filesort
CATISL eq_ref PRIMARY PRIMARY 4 L.island 1
CATLOC eq_ref PRIMARY PRIMARY 4 L.location 1
CATDIS eq_ref PRIMARY PRIMARY 4 L.district 1
M ALL NULL NULL NULL NULL 2182