homepage Welcome to WebmasterWorld Guest from 54.227.41.242
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Query slow - sits in "Sending Data" state
MySQL query is very slow - sits in "Sending Data" state
ferly_man




msg:4260896
 12:44 am on Feb 1, 2011 (gmt 0)

So I have a query, that when not in query cache, it sits in "Sending Data" state before it's executed on the server. The query execution takes under a second. What could the query be doing to be sitting in the Sending Data state for that long?

I have - what I believe - good indexes on the tables.

Here is the query in question:

SELECT
`listings`.`id`,
`listings`.`price` as price,
`listings`.`bedrooms` as bedrooms,
`listings`.`bathrooms` as bathrooms,
`listings`.`year_built` as year,
`listings`.`square_feet` as sq_ft,
`listings`.`property_type` as property_type,
`listings`.`category` as category,
`listings`.`listing_date` as listed,
`listings`.`listing_update_date` as updated,
`listings`.`category` as listing_status,
`addresses`.`address_full` as address,
`addresses`.`postal_code` as zip,
`addresses`.`latitude` as lat,
`addresses`.`longitude` as lng,
(SELECT `name` FROM `regions_cities` WHERE `regions_cities`.`id`=`addresses`.`city`) as `city`,
(SELECT `name` FROM `regions_states` WHERE `regions_states`.`id`=`addresses`.`state`) as `state`,
(SELECT `name` FROM `regions_countries` WHERE `regions_countries`.`id`=`addresses`.`country`) as `country`,
(SELECT `large` FROM `photos` WHERE `photos`.`listing_id`=`listings`.`id` AND is_main = '1') as `thumbnail`
FROM
`listings`
LEFT JOIN `addresses` ON `listings`.`id`=`addresses`.`listing_id`
WHERE
`listings`.`visible` = 1
AND `addresses`.`city` = 597
AND `addresses`.`state` = 13
AND `addresses`.`country` = 2
AND `listings`.`category` = 1



Here is a link/image to my EXPLAIN query: [IMG]http://img256.imageshack.us/img256/2270/image10ho.jpg[/IMG] [img256.imageshack.us]


Here are the INDEXES for the `addresses` table: [IMG]http://img211.imageshack.us/img211/6923/image2af.jpg[/IMG] [img211.imageshack.us]


Here are the INDEXES for the `listings` table: [IMG]http://img220.imageshack.us/img220/8879/image3ye.jpg[/IMG] [img220.imageshack.us]


Here are the INDEXES for the `photos` table: [IMG]http://img843.imageshack.us/img843/3686/image4iy.jpg[/IMG] [img843.imageshack.us]


Here is my my.cnf file:


log_slow_queries = /var/log/mysql/mysql-slow.log

long_query_time = 5

safe-show-database

tmp_table_size = 128M

max_heap_table_size = 128M

query_cache_limit=4M

query_cache_size=128M ## 32MB for every 1GB of RAM

query_cache_type=1

max_connections=100

collation_server=utf8_unicode_ci

character_set_server=utf8

delayed_insert_timeout=40

interactive_timeout=10

wait_timeout=28800

connect_timeout=20

thread_cache_size=64

key_buffer=128M ## 32MB for every 1GB of RAM

join_buffer=4M

max_connect_errors=20

max_allowed_packet=400M

table_cache=1024

record_buffer=1M

sort_buffer_size=4M ## 1MB for every 1GB of RAM

read_buffer_size=4M ## 1MB for every 1GB of RAM

read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM

thread_concurrency=2 ## Number of CPUs x 2

myisam_sort_buffer_size=16M



Here are the Server stats:

Processor #1
Vendor GenuineIntel
Name Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
Speed 2261.052 MHz
Cache 8192 KB

Processor #2
Vendor GenuineIntel
Name Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
Speed 2261.052 MHz
Cache 8192 KB

Processor #3
Vendor GenuineIntel
Name Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
Speed 2261.052 MHz
Cache 8192 KB


Liek I said, it seems to take fooooooorever to get through the Sending Data state.

If any more information is required, I will be more than happy to supply it.

And for the record, I'm not 100% sure if my my.cnf settings are set properly.

Marc

 

rocknbil




msg:4261770
 5:18 pm on Feb 2, 2011 (gmt 0)

You have three sub selects,

(SELECT `name` FROM `regions_cities` WHERE `regions_cities`.`id`=`addresses`.`city`) as `city`,
(SELECT `name` FROM `regions_states` WHERE `regions_states`.`id`=`addresses`.`state`) as `state`,
(SELECT `name` FROM `regions_countries` WHERE `regions_countries`.`id`=`addresses`.`country`) as `country`,

Did you try just doing left joins on those three? You'd have to append the field list,

....
`addresses`.`longitude` as lng,
`regions_cities`.`name` as city,
`regions_state`.`name` as state,
`regions_countries`.`name` as country
from listings left join ....
left join ...
left join ...

You'd just join them on the addresses table like you're doing with listings -> addresses. Also I don't know if this would help, but when you do "as" those are variables stored in memory, you probably don't need most of them, just use as for the ones that have multiple identifiers (like `name`.)

ferly_man




msg:4261781
 5:37 pm on Feb 2, 2011 (gmt 0)

Hey rocknbil, thank you very much for the reply.

I'm actually in the process of switching out the subqueries to JOINS, so I will post back with those results.

I was also unaware that using "as `abc`" stored those names in memory. I will definitely be removing them then.

I'll be back shortly. Thanks again.

rocknbil




msg:4262350
 5:35 pm on Feb 3, 2011 (gmt 0)

The savings may be trivial on "as", just a thought.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved