Jump to content

Fulltext Speed


neoform

Recommended Posts

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`)
);

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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).

 

 

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.