# fulltext search with additional condition in the where clause

**URL:** <https://forums.percona.com/t/fulltext-search-with-additional-condition-in-the-where-clause/2066>\
**Category:** Sphinx & Full-Text Search\
**Created:** [August 22, 2007, 10:54pm UTC](https://forums.percona.com/t/fulltext-search-with-additional-condition-in-the-where-clause/2066 "2007-08-22T22:54:32Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![dimitar](https://avatars.discourse-cdn.com/v4/letter/d/aca169/32.png) [@dimitar](https://forums.percona.com/u/dimitar)\
**Post date:** [August 22, 2007, 10:54pm UTC](https://forums.percona.com/t/fulltext-search-with-additional-condition-in-the-where-clause/2066/1 "2007-08-22T22:54:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [September 3, 2007, 7:02am UTC](https://forums.percona.com/t/fulltext-search-with-additional-condition-in-the-where-clause/2066/2 "2007-09-03T07:02:11Z")

</div>

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.