MySQL Forums
Forum List  »  Performance

Slow Query stuck on "Sending Data"
Posted by: Marc Ferland
Date: January 31, 2011 09:59PM

I'm terribly sorry that I have to post this. I've seen several postings related to this topic, but I just can't resolve this issue without some direct assistance.

I'm going to try and provide all the necessary information up front, but if I miss anything please let me know.

Here's the dreaded query:

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 `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
LIMIT
        250


mysql > SHOW CREATE TABLE `listings` -- engine, indexes

CREATE TABLE `listings` (
 `id` bigint(20) unsigned NOT NULL auto_increment,
 `feed_id` bigint(20) NOT NULL COMMENT,
 `feed_listing_id` bigint(20) unsigned NOT NULL,
 `agent_id` bigint(20) unsigned NOT NULL,
 `category` tinyint(1) unsigned NOT NULL,
 `property_type` tinyint(3) NOT NULL,
 `price` decimal(10,2) NOT NULL,
 `listing_date` char(20) collate utf8_unicode_ci NOT NULL,
 `listing_update_date` char(20) collate utf8_unicode_ci NOT NULL,
 `detailed_view_url` varchar(255) collate utf8_unicode_ci NOT NULL,
 `virtual_tour_url` varchar(255) collate utf8_unicode_ci NOT NULL,
 `tax_amount` varchar(15) collate utf8_unicode_ci NOT NULL,
 `style` varchar(75) collate utf8_unicode_ci NOT NULL,
 `square_feet` mediumint(8) NOT NULL,
 `year_built` smallint(4) NOT NULL,
 `bedrooms` smallint(5) NOT NULL,
 `bathrooms` float NOT NULL,
 `WebBugUrl` varchar(100) collate utf8_unicode_ci NOT NULL,
 `visible` tinyint(1) NOT NULL default '1',
 `listing_ok` bigint(20) NOT NULL,

 PRIMARY KEY  (`id`),
 KEY `price` (`price`),
 KEY `property_type` (`property_type`),
 KEY `bathrooms` (`bathrooms`),
 KEY `bedrooms` (`bedrooms`),
 KEY `listing_date` (`listing_date`),
 KEY `primary_category` (`id`,`category`),
 KEY `id_category_visible` (`id`,`category`,`visible`)
) ENGINE=MyISAM AUTO_INCREMENT=1199031 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


mysql > SHOW CREATE TABLE `addresses` -- engine, indexes

CREATE TABLE `addresses` (
 `id` bigint(20) unsigned NOT NULL auto_increment,
 `listing_id` bigint(20) unsigned NOT NULL,
 `street_number` varchar(15) collate utf8_unicode_ci NOT NULL,
 `street_name` varchar(35) collate utf8_unicode_ci NOT NULL,
 `neighborhood` mediumint(8) unsigned NOT NULL,
 `city` mediumint(8) unsigned NOT NULL,
 `state` smallint(5) unsigned NOT NULL,
 `country` tinyint(1) unsigned NOT NULL,
 `postal_code` varchar(11) collate utf8_unicode_ci NOT NULL,
 `latitude` varchar(15) collate utf8_unicode_ci NOT NULL,
 `longitude` varchar(15) collate utf8_unicode_ci NOT NULL,
 `address_full` varchar(50) collate utf8_unicode_ci NOT NULL,
 `address_full_slug` varchar(50) collate utf8_unicode_ci NOT NULL,
 `visible` tinyint(1) NOT NULL default '1',
 PRIMARY KEY  (`id`),
 KEY `listing_id` (`listing_id`),
 KEY `address_full` (`address_full`),
 KEY `location` (`country`,`state`,`city`)
) ENGINE=MyISAM AUTO_INCREMENT=1053027 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


mysql > SHOW CREATE TABLE `photos` -- engine, indexes

CREATE TABLE `photos` (
 `id` bigint(20) unsigned NOT NULL auto_increment,
 `listing_id` bigint(20) unsigned NOT NULL,
 `large` varchar(255) collate utf8_unicode_ci NOT NULL,
 `thumb` varchar(255) collate utf8_unicode_ci NOT NULL,
 `caption` text collate utf8_unicode_ci NOT NULL,
 `order` int(2) NOT NULL,
 `is_main` tinyint(1) unsigned NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `listing_id` (`listing_id`),
 KEY `large` (`large`),
 KEY `is_main` (`is_main`)
) ENGINE=MyISAM AUTO_INCREMENT=11613231 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


mysql > SHOW CREATE TABLE `regions_cities` -- engine, indexes

CREATE TABLE `regions_cities` (
 `id` bigint(20) unsigned NOT NULL auto_increment,
 `parent_id` tinyint(3) unsigned NOT NULL,
 `name` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `name` (`name`(20))
) ENGINE=MyISAM AUTO_INCREMENT=38815 DEFAULT CHARSET=latin1


mysql > SHOW CREATE TABLE `regions_states` -- engine, indexes

CREATE TABLE `regions_states` (
 `id` tinyint(3) unsigned NOT NULL auto_increment,
 `parent_id` tinyint(1) unsigned NOT NULL,
 `name` text collate utf8_unicode_ci NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=67 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


mysql > SHOW CREATE TABLE `regions_countries` -- engine, indexes

CREATE TABLE `regions_countries` (
 `id` tinyint(1) unsigned NOT NULL auto_increment,
 `name` text collate utf8_unicode_ci NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


EXPLAIN (sorry, I do not know how to have the EXPLAIN results display nicely .. please copy/paste the URL to see a screenshot): http://img256.imageshack.us/img256/2270/image10ho.jpg

SHOW VARIABLES LIKE '%buffer%'; -- cache size

bulk_insert_buffer_size		8388608
innodb_buffer_pool_awe_mem_mb	0
innodb_buffer_pool_size		8388608
innodb_log_buffer_size		1048576
join_buffer_size		4194304
key_buffer_size			134217728
myisam_sort_buffer_size		16777216
net_buffer_length		16384
preload_buffer_size		32768
read_buffer_size		4194304
read_rnd_buffer_size		4194304
sort_buffer_size		4194304


SHOW VARIABLES LIKE '%size'; -- tmp/heap settings

binlog_cache_size			32768
bulk_insert_buffer_size			8388608
delayed_queue_size			1000
innodb_additional_mem_pool_size		1048576
innodb_buffer_pool_size			8388608
innodb_log_buffer_size			1048576
innodb_log_file_size			5242880
join_buffer_size			4194304
key_buffer_size				134217728
key_cache_block_size			1024
large_page_size				0
max_binlog_cache_size			18446744073709547520
max_binlog_size				1073741824
max_heap_table_size			134217728
max_join_size				18446744073709551615
max_relay_log_size			0
myisam_data_pointer_size		6
myisam_max_sort_file_size		9223372036853727232
myisam_mmap_size			18446744073709551615
myisam_sort_buffer_size			16777216
preload_buffer_size			32768
query_alloc_block_size			8192
query_cache_size			134217728
query_prealloc_size			8192
range_alloc_block_size			4096
read_buffer_size			4194304
read_rnd_buffer_size			4194304
sort_buffer_size			4194304
thread_cache_size			64
tmp_table_size				134217728
transaction_alloc_block_size		8192
transaction_prealloc_size		4096


Again, the query just sits in "Sending Data" state. This query in particular - when is not in query_cache - has taken upwards of 80 seconds to return results.

Any assistance would be greatly appreciated.

- Marc



Edited 1 time(s). Last edit at 01/31/2011 10:01PM by Marc Ferland.

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Query stuck on "Sending Data"
8267
January 31, 2011 09:59PM
3259
February 02, 2011 12:40AM
2053
February 02, 2011 09:12AM
2710
February 02, 2011 09:18PM
1843
February 03, 2011 12:35AM
1344
February 03, 2011 08:00AM
1240
February 11, 2011 02:19PM
1417
February 11, 2011 03:28PM
1597
February 11, 2011 08:00PM
1411
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.