Query slow - sits in "Sending Data" state

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

[ferly_man]

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: [img256.imageshack.us]


Here are the INDEXES for the `addresses` table: [img211.imageshack.us]


Here are the INDEXES for the `listings` table: [img220.imageshack.us]


Here are the INDEXES for the `photos` table: [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]

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]

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]

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