fulltext search with additional condition in the where clause

Polyglot Projects Sphinx & Full-Text Search
1

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

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

2

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.