Ok, things have improved 10-fold.
Here is the new query as per your suggestions:
SELECT SQL_NO_CACHE
`listings`.`id`,
`listings`.`price`,
`listings`.`bedrooms`,
`listings`.`bathrooms`,
`listings`.`square_feet`,
`listings`.`property_type`,
`listings`.`category`,
`listings`.`listing_date`,
`listings`.`feature`,
`listings`.`large`,
`listings`.`mls_number`,
`addresses`.`listing_id`,
`addresses`.`city`,
`addresses`.`state`,
`addresses`.`country`,
`addresses`.`postal_code`,
`addresses`.`latitude`,
`addresses`.`longitude`,
`addresses`.`address_full`,
`regions_cities`.`id` as `city_id`,
`regions_cities`.`name` as `city`,
`regions_states`.`id` as `state_id`,
`regions_states`.`name` as `state`,
`regions_countries`.`id` as `country_id`,
`regions_countries`.`name` as `country`
FROM `listings`
JOIN `addresses` ON `listings`.`id`=`addresses`.`listing_id`
JOIN `regions_countries` ON `addresses`.`country`=`regions_countries`.`id`
JOIN `regions_states` ON `addresses`.`state`=`regions_states`.`id`
JOIN `regions_cities` ON `addresses`.`city`=`regions_cities`.`id`
WHERE `listings`.`visible` = 1
AND `addresses`.`city` = 597
AND `addresses`.`state` = 13
AND `addresses`.`country` = 2
AND `listings`.`category`=1
ORDER BY `listings`.`feature` ASC LIMIT 0,10
Here is an image the EXPLAIN I ran against that query:
http://img826.imageshack.us/img826/7672/image4ju.jpg
Seems the `addresses` table is still finding 25,813 results. I need to look into getting that down to make things even better.
Here is the preceding query that counts records for a pagination set which is causing some long load times now too .. 7 seconds +/- at times:
SELECT
SQL_NO_CACHE
COUNT(`listings`.`id`) as `total`
FROM `listings`
JOIN `addresses`
ON `listings`.`id`=`addresses`.`listing_id`
WHERE `listings`.`visible` = 1
AND `listings`.`category` = 1
AND `addresses`.`city` = 597
AND `addresses`.`state` = 13
AND `addresses`.`country` = 2
Here's an image containing the EXPLAIN:
http://img40.imageshack.us/img40/8901/image3zo.jpg
*Is there a better way to create a pagination system? What I mean is, is there a way to grab the appropriate results on the fly in pagination form instead of having to do the initial COUNT() query? Or is COUNT() as good as it gets, and is actually not that bad if tables are indexed/managed properly?
Things are definitely going in the right direction, that's for sure.
Thank you very much for your help so far!
PS. I'm aware that I'm using SQL_NO_CACHE ;)
- Marc
Edited 2 time(s). Last edit at 02/02/2011 09:42PM by Marc Ferland.