MySQL Forums
Forum List  »  Performance

Re: Adding keyword search (fulltext) to query
Posted by: Marc Ferland
Date: October 25, 2012 06:54AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Adding keyword search (fulltext) to query
1797
October 25, 2012 06:54AM


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.