MySQL Performance: FullText => fulltext search with additional condition in the where clause

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]

Report message to a moderator