Query slow - sits in "Sending Data" state

[mrMarcus]

So I have a query that, when not in query cache, 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`.`feed_listing_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 `feature_type` FROM `listing_features` WHERE `listing_features`.`listing_id`=`listings`.`id`) as `feature_type`, 
(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:

 

 

Here are the INDEXES for the `addresses` table:

 

 

Here are the INDEXES for the `listings` table:

 

 

Here are the INDEXES for the `photos` table:

 

 

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

[mikosiko]

Did you test changing those DEPENDENT SUBQUERYS for JOINS ?