MySQL Forums
Forum List  »  Performance

Re: Slow Query stuck on "Sending Data"
Posted by: Marc Ferland
Date: February 02, 2011 09:18PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
12678
January 31, 2011 09:59PM
4429
February 02, 2011 12:40AM
2762
February 02, 2011 09:12AM
Re: Slow Query stuck on "Sending Data"
3442
February 02, 2011 09:18PM
2577
February 03, 2011 12:35AM
1863
February 03, 2011 08:00AM
1701
February 11, 2011 02:19PM
1918
February 11, 2011 03:28PM
2174
February 11, 2011 08:00PM
2045
February 11, 2011 08:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.