| 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.
|
|
|
