Thanks for the response, James. Below is the requested information.
LISTINGS TABLE;
mysql> show create table listings\G -- engine, indexes
*************************** 1. row ***************************
Table: listings
Create Table: CREATE TABLE `listings` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`feed_id` tinyint(3) NOT NULL,
`feed_listing_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`agent_id` bigint(20) unsigned NOT NULL,
`category` tinyint(1) unsigned NOT NULL,
`property_type` tinyint(3) NOT NULL,
`price` int(10) NOT NULL,
`listing_date` char(26) COLLATE utf8_unicode_ci NOT NULL,
`listing_update_date` char(26) COLLATE utf8_unicode_ci NOT NULL,
`detailed_view_url` varchar(500) 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,
`feature` tinyint(1) NOT NULL DEFAULT '9',
`mls_number` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`large` varchar(255) 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` decimal(9,6) DEFAULT NULL,
`longitude` decimal(9,6) DEFAULT NULL,
`street_number` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`street_name` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
`address_full` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`address_visible` tinyint(1) unsigned NOT NULL,
`in_progress` tinyint(1) NOT NULL DEFAULT '0',
`agent_img` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`bump_up` tinyint(1) NOT NULL DEFAULT '0',
`bump_up_date` char(26) COLLATE utf8_unicode_ci NOT NULL,
`num_photos` tinyint(3) unsigned NOT NULL,
`urgent` tinyint(1) NOT NULL DEFAULT '0',
`vanity_url` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`price_from_to` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`contact_email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`rental_period` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`listing_expire_date` bigint(20) unsigned NOT NULL,
`tax_year` smallint(4) unsigned NOT NULL,
`lot` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`lot_legal` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`pets` smallint(3) NOT NULL DEFAULT '0',
`pets_comments` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`timestamp_alerts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `agent_id` (`agent_id`),
KEY `wo_neighborhood` (`country`,`state`,`city`,`visible`,`category`,`bump_up_date`),
KEY `w_neighborhood` (`country`,`state`,`city`,`neighborhood`,`visible`,`category`,`bump_up_date`),
KEY `feed_listing_id` (`feed_listing_id`),
KEY `city` (`city`),
KEY `category` (`category`,`state`,`country`),
KEY `feed_id` (`feed_id`,`visible`),
KEY `price` (`category`,`price`),
KEY `main_search` (`category`,`city`,`visible`,`feature`,`bump_up_date`),
KEY `cat_visible_state_orderby_feature_bump_up_date` (`category`,`visible`,`state`,`feature`,`bump_up_date`),
KEY `latitude` (`latitude`,`longitude`),
KEY `mls_number` (`mls_number`,`visible`),
KEY `homepage_latest_listings` (`visible`,`category`,`id`),
KEY `id` (`id`,`agent_id`,`category`,`property_type`,`visible`,`bump_up_date`),
KEY `state` (`state`,`visible`,`city`),
KEY `popular_cities_mc_hp` (`country`,`visible`,`city`),
KEY `postal_code` (`postal_code`,`category`,`visible`)
) ENGINE=MyISAM AUTO_INCREMENT=5518932 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Name: listings
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 3579152
Avg_row_length: 604
Data_length: 2314660516
Max_data_length: 281474976710655
Index_length: 974722048
Data_free: 152692056
Auto_increment: 5518932
Create_time: 2012-10-11 16:29:50
Update_time: 2012-10-25 08:41:03
Check_time: 2012-10-11 16:35:14
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
mysql> explain select `id` from `listings` where `category` = 1 and `city` = 35862 and `visible` = 1;
+----+-------------+----------+------+---------------------------------------------------------------------------------------------------------+-------------+---------+-------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------------------------------------------------------------------------------------------------+-------------+---------+-------------------+------+-------+
| 1 | SIMPLE | listings | ref | city,category,price,main_search,cat_visible_state_orderby_feature_bump_up_date,homepage_latest_listings | main_search | 5 | const,const,const | 330 | |
+----+-------------+----------+------+---------------------------------------------------------------------------------------------------------+-------------+---------+-------------------+------+-------+
DESCRIPTIONS_TEST TABLE;
mysql> show create table descriptions_test\G -- engine, indexes
*************************** 1. row ***************************
Table: descriptions_test
Create Table: CREATE TABLE `descriptions_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`listing_id` bigint(20) NOT NULL,
`title` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`description` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `listing_id` (`listing_id`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM AUTO_INCREMENT=2005356 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
mysql> show table status like 'descriptions_test'\G -- sizes
*************************** 1. row ***************************
Name: descriptions_test
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2005355
Avg_row_length: 577
Data_length: 1157813576
Max_data_length: 281474976710655
Index_length: 2434226176
Data_free: 0
Auto_increment: 2005356
Create_time: 2012-10-06 12:41:04
Update_time: 2012-10-06 21:13:29
Check_time: 2012-10-06 12:41:04
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
mysql> EXPLAIN SELECT `listing_id` FROM `descriptions_test` WHERE MATCH (`description`) AGAINST ('swimming pool' IN BOOLEAN MODE);
+----+-------------+-------------------+----------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+----------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | descriptions_test | fulltext | description | description | 0 | | 1 | Using where |
+----+-------------+-------------------+----------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> show variables like '%buffer%'; -- cache size
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 16777216 |
| key_buffer_size | 2147483648 |
| myisam_sort_buffer_size | 33554432 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 8388608 |
| read_rnd_buffer_size | 33554432 |
| sort_buffer_size | 25165824 |
| sql_buffer_result | OFF |
+-------------------------+------------+
SELECT `listings`.`id` FROM listings
WHERE `listings`.`category` = 1
AND `listings`.`city` = 35862
AND `listings`.`visible` = 1;
Showing rows 0 - 29 ( 549 total, Query took 0.0011 sec)
SELECT `descriptions_test`.`listing_id` FROM `descriptions_test`
WHERE MATCH (`descriptions_test`.`description`) AGAINST ('swimming pool' IN BOOLEAN MODE) ;
Showing rows 0 - 29 ( 318,920 total, Query took 0.0896 sec)
Edited 2 time(s). Last edit at 10/26/2012 01:27PM by Marc Ferland.