Fulltext Speed

[neoform]

I'm doing a query on a 6GB table, when searching two FULLTEXT indexed fields, the query takes 4-10 seconds even though the indexes are being used.

 

Is there a way to speed this up?

[fenway]

I assume you mean you have a FULLTEXT across multiple columns....

[neoform]

yeah.

 

-- Table "shop_products" DDL

CREATE TABLE `shop_products` (
  `id` mediumint( unsigned NOT NULL auto_increment,
  `upc` bigint(20) unsigned NOT NULL,
  `gtin` bigint(20) unsigned NOT NULL,
  `ean` bigint(20) unsigned NOT NULL,
  `url_name` char(50) NOT NULL,
  `name` varchar(300) NOT NULL,
  `description_short` varchar(2000) NOT NULL,
  `description_long` text NOT NULL,
  `attributes` text NOT NULL,
  `image` mediumint( unsigned NOT NULL,
  `weight` varchar(20) NOT NULL,
  `dimensions` varchar(20) NOT NULL,
  `man_id` mediumint( unsigned NOT NULL,
  `man_code` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `upc` (`upc`),
  KEY `name` (`url_name`),
  KEY `man_id` (`man_id`),
  FULLTEXT KEY `full` (`name`,`description_short`)
);

[neoform]

mysql> explain SELECT products.id, products.name, products.url_name, products.description_short, info.merchant_id, info.url, info.price, info.price_sale, info.price_shipping, info.currency, info.available, info.stock, info.pcondition FROM shop_products products STRAIGHT_JOIN shop_products_listings listings ON listings.product_id = products.id STRAIGHT_JOIN shop_products_listings_info info ON info.product_id = listings.product_id AND listings.merchant_id = info.merchant_id WHERE MATCH(products.name, products.description_short) AGAINST('bath') AND listings.status = 'active' LIMIT 20, 10 ;
+----+-------------+----------+----------+-----------------+---------+---------+--------------------------------------------------------------------+--------+-------------+
| id | select_type | table    | type     | possible_keys   | key     | key_len | ref                                                                | rows   | Extra       |
+----+-------------+----------+----------+-----------------+---------+---------+--------------------------------------------------------------------+--------+-------------+
|  1 | SIMPLE      | products | fulltext | PRIMARY,full    | full    | 0       |                                                                    |      1 | Using where |
|  1 | SIMPLE      | listings | ref      | status          | status  | 1       | const                                                              | 913786 | Using where |
|  1 | SIMPLE      | info     | eq_ref   | PRIMARY,user_id | PRIMARY | 6       | shopping_ca.listings.product_id,shopping_ca.listings.merchant_id   |      1 | Using where |
+----+-------------+----------+----------+-----------------+---------+---------+--------------------------------------------------------------------+--------+-------------+

 

I've never really used fulltext before, so I'm not sure what good speeds are for it, I've always used my own stemmed keyword approach, but I figured I'd give fulltext a whirl this time..  given that the joins are all ref and eq_ref, I thought this query would be fast, but it's anything but.

 

Not to mention that doing a COUNT to get total results found is so slow it's out of the question..

[fenway]

I don't think it's the fulltext that's slow -- that's only returning a singlle record from that table, no?

[neoform]

well, that particular search term does, but in general, the query takes about 10-20 seconds.

 

example:

 

SELECT id FROM shop_products WHERE MATCH(name, description_short) AGAINST('dancing');

 

takes 16 seconds and returns 826 rows.

 

mysql> explain SELECT id FROM shop_products WHERE MATCH(name, description_short) AGAINST('dancing');
+----+-------------+---------------+----------+---------------+------+---------+-----+------+-------------+
| id | select_type | table         | type     | possible_keys | key  | key_len | ref | rows | Extra       |
+----+-------------+---------------+----------+---------------+------+---------+-----+------+-------------+
|  1 | SIMPLE      | shop_products | fulltext | full          | full | 0       |     |    1 | Using where |
+----+-------------+---------------+----------+---------------+------+---------+-----+------+-------------+

 

Keep in mind the DB is 6GB and has 300,000 rows.. 

 

Does this sound right?

[fenway]

Perhaps... I've not really used FULLTEXT much in production.

[hitman6003]

How big is your MyISAM buffer?  Is it big enough to fix the fulltext index in memory?  If not, consider creating a buffer specifically for that index (assuming that the table and search speed is that vital to operations).

 

I think you have two options otherwise...use MySQL6's Falcon engine, which has a new (better!) fulltext index, or use an external fulltext index, such as Lucene or Sphinx.

 

The limiting factor is probably MyISAM's fulltext index itself, since the table is fairly large and I can only assume that a large part of that data is in the description (which is indexed, thus creating very large indexes).

 

 

[neoform]

I set my key buffer to 2GB (the machine has 4GB of ram)..  From what I can tell it's not paging, so I don't think that's the issue. 2Gb should be more than enough to fit the index.

[hitman6003]

MySQL administrator will tell you the size of the index on the table if you want to be sure.

 

Additionally, if you want to ensure the index is in memory, you can create a second key buffer and assign that table to it specifically.  See here: http://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html

[neoform]

The index is 110MB..  doesn't seem that big compared to some of the other indexes I have.

[neoform]

Perhaps... I've not really used FULLTEXT much in production.

 

Just wondering, what have you used for searches then?

 

I have a lot of text based content, what would be the right approach for search?

 

On other projects I used a keyword/link approach, where I would store stemmed words and links with weight to each word to each item (product). The reason I'm trying something new is because my actual search query was never very good, I'd get relevant words, but often the most relevant items (products) were not at the top. Not to mention the keyword index was massive (often bigger than the content itself), and the indexing process was very CPU intensive..

[fenway]

Well, there's always Lucene... but I too have a custom weighted search, so FULLTEXT was never the right match.

[neoform]

I'm giving Zend's wrapper for Lucene a try..

 

Everything I've read so far online suggests that fulltext is only good for small tables, not big ones.