| fulltext search with additional condition in the where clause [message #1675] |
Thu, 23 August 2007 00:24  |
 dimitar
Messages: 1
Registered: August 2007 |
Junior Member |
|
|
Hello,
I have a table "products" with around 1,000,000 records.
The table structure is:
CREATE TABLE products (
product_id int(15) unsigned NOT NULL default '0',
store_id int(7) unsigned NOT NULL default '0',
title varchar(255) NOT NULL default '',
description text,
PRIMARY KEY (product_id),
KEY products_sid (store_id),
KEY products_pidsid (product_id,store_id),
FULLTEXT KEY products_name (title),
FULLTEXT KEY products_des (description),
FULLTEXT KEY products_namedes (title,description)
)
1. I am having a performance problems using the fulltext search with additional condition in the where clause:
SELECT * FROM products WHERE store_id = '101' AND MATCH (title, description) AGAINST ('"size M"' IN BOOLEAN MODE ) LIMIT 0,20;
EXPLAIN shows:
table: products
type: fulltext
possible keys: products_sid,products_namedesc
key: products_namedesc
key_len: 0
ref:
rows: 1
extra: Using where
It takes around 30 sec to finish the query. Without the store_id='101' it takes around 0.5-1 sec
2. I tried to rewrite this query using INNER JOIN like this:
SELECT p1.* FROM products p1 INNER JOIN products p2 USE INDEX (products_pidsid) ON (p1.pid=p2.pid) WHERE MATCH (p1.title, p1.description) AGAINST ('"size M"' IN BOOLEAN MODE ) AND p1.sid='101' LIMIT 0,20;
EXPLAIN shows:
table: p1
type: fulltext
possible keys: PRIMARY,products_sid,products_pidsid,products_namedesc
key: products_namedesc
key_len: 0
ref:
rows: 1
extra: Using where
table: p2
type: ref
possible keys: products_pidsid
key: products_pidsid
key_len: 4
ref: p1.pid
rows: 1
extra: Using index
This second query is even a little slower than the first one.
Any suggestions how I can optimize it?
Best Regards,
Dimitar
[Updated on: Thu, 23 August 2007 00:27]
|
|
|
| Re: fulltext search with additional condition in the where clause [message #1727 is a reply to message #1675 ] |
Mon, 03 September 2007 08:32  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
This is expected because MySQL has to check store=101 or all matches it gets from full text search engine which means reading a lot of rows and can get very slow.
You can try adding store as another keyworkd and add STRE101 keyword to the search match
Or you can look into Sphinx if you need higher performance.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
