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.
