# Fulltext index not being used **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 6, 2007, 9:50am UTC](https://forums.percona.com/t/fulltext-index-not-being-used/377 "2007-07-06T09:50:48Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![tucj7](https://avatars.discourse-cdn.com/v4/letter/t/a88e4f/32.png) [@tucj7](https://forums.percona.com/u/tucj7)\ **Post date:** [July 6, 2007, 9:50am UTC](https://forums.percona.com/t/fulltext-index-not-being-used/377/1 "2007-07-06T09:50:48Z")
I have a Product table with a fulltext index on the name field and the description field. I want to do a select for products that match text with the name being more important than the description - hence the order by clause. They also need to fit into certain categories. I also only want to show Products that have an offer in the Offers table. The Product table has over 500 000 rows and the ProductCategory has the same number of rows which just maps the Products to a Category. Here is what I’ve tried, but the number of rows scanned is far too much, and I can’t think of any other info to make an index on. SELECT DISTINCT p.id, MATCH (p.name) AGAINST (‘the colour purple’) AS name\_score, MATCH (p.description) AGAINST (‘the colour purple’) AS description\_score FROM Product as p INNER JOIN ProductCategory as pc on p.id=pc.product\_id INNER JOIN Category as c on c.id=pc.category\_id WHERE ( pc.category\_id=‘1’ OR pc.category\_id IN ( ‘18’ , ‘20’ , ‘21’ , ‘22’ , ‘23’ , ‘24’ , ‘25’ , ‘26’ , ‘27’ , ‘28’ , ‘29’ , ‘30’ , ‘31’ , ‘32’ , ‘33’ , ‘34’ , ‘35’ , ‘36’ , ‘37’ , ‘38’ , ‘39’ , ‘40’ , ‘41’ , ‘42’ , ‘43’ , ‘44’ , ‘45’ , ‘46’ , ‘47’ , ‘48’ , ‘49’ , ‘50’ , ‘51’ , ‘52’ , ‘53’ , ‘54’ , ‘55’ , ‘56’ , ‘57’ , ‘58’ , ‘59’ , ‘60’ , ‘61’ , ‘62’ , ‘63’ , ‘64’ , ‘65’ , ‘66’ , ‘67’ , ‘19’ , ‘68’ , ‘69’ , ‘70’ , ‘71’ , ‘72’ , ‘73’ , ‘74’ , ‘821’ , ‘822’ ) ) AND ( ( (MATCH (p.name) AGAINST (‘the colour purple’)) OR (MATCH (p.description) AGAINST (‘the colour purple’)) ) ) AND p.id IN ( SELECT product\_id FROM Offer AS o WHERE o.price \> 0 AND o.deleted = 0 ) AND p.needs\_verification=0 ORDER BY name\_score DESC, description\_score DESC LIMIT 0, 20; ±—±-------------------±------±---------------±------- --------------------±-------------------±--------±------- ----------------------------±-------±--------------------- ------------------------+ | id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra | ±—±-------------------±------±---------------±------- --------------------±-------------------±--------±------- ----------------------------±-------±--------------------- ------------------------+ | 1 | PRIMARY | p | ref | PRIMARY,needs\_verification | needs\_verification | 1 | const | 463119 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | pc | eq\_ref | product\_id,category\_id | product\_id | 3 | priceche\_pricecheck.p.id | 1 | Using where; Distinct | | 1 | PRIMARY | c | eq\_ref | PRIMARY | PRIMARY | 3 | priceche\_pricecheck.pc.category\_id | 1 | Using index; Distinct | | 2 | DEPENDENT SUBQUERY | o | index\_subquery | product\_id,price,deleted | product\_id | 3 | func | 2 | Using index; Using where | ±—±-------------------±------±---------------±------- --------------------±-------------------±--------±------- ----------------------------±-------±--------------------- ------------------------+ ---
**Author:** ![marcello](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcello/32/759_2.png) [@marcello](https://forums.percona.com/u/marcello)\ **Post date:** [July 10, 2007, 11:33pm UTC](https://forums.percona.com/t/fulltext-index-not-being-used/377/2 "2007-07-10T23:33:06Z")
Hello there, Here are a couple of observations that I can make, without knowing your tables… 1 - You probably want the number of times a pattern is found in either name or description instead of its relevance percentage, so I’ve added the COUNT. 2 - MySQL Query Optimizer uses only one index for query for table. And you want to have a separated count of occurrences, by name or description. So I’d rather have one query for each MATCH and explicitly call the specific index for that column. That will also spare you that OR condition. OR are famous to confuse the optimizer for the number of rows needed. I assume you have created the indexes for name and description separately. 3 - I included the category\_id 1 inside your IN list, and took the ’ from the number, assuming they are integers. SELECT DISTINCT id, name\_score, description\_score FROM(SELECT p.id, COUNT(IF(MATCH (p.name) AGAINST (‘the colour purple’),1,NULL) AS name\_scoreFROM Product as p USE INDEX (name), ProductCategory as pc, Category as cWHERE p.id=pc.product\_id AND on c.id=pc.category\_id AND pc.category\_id IN (1, 18 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 19 , 68 , 69 , 70 , 71’ , 72 , 73 , 74 , 821 , 822 ) AND MATCH (p.name) AGAINST (‘the colour purple’) AND p.needs\_verification = 0)UNION(SELECT p.id, COUNT(IF(MATCH (p.description) AGAINST (‘the colour purple’),1,NULL) AS description\_scoreFROM Product as p USE INDEX (description), ProductCategory as pc, Category as cWHERE p.id=pc.product\_id AND on c.id=pc.category\_id AND pc.category\_id IN (1, 18 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 19 , 68 , 69 , 70 , 71’ , 72 , 73 , 74 , 821 , 822 ) AND MATCH (p.name) AGAINST (‘the colour purple’) AND p.needs\_verification = 0)WHEREid IN ( SELECT product\_id FROM Offer AS o WHERE o.price \> 0 AND o.deleted = 0 )ORDER BY name\_score DESC, description\_score DESCLIMIT 0, 20; Best wishes, – Marcello ---
**Author:** ![tucj7](https://avatars.discourse-cdn.com/v4/letter/t/a88e4f/32.png) [@tucj7](https://forums.percona.com/u/tucj7)\ **Post date:** [July 11, 2007, 4:22am UTC](https://forums.percona.com/t/fulltext-index-not-being-used/377/3 "2007-07-11T04:22:51Z")
Hi Marcello, Thnaks so much for this… I had to make some modifications, because I am in fact looking for the relevance. But, the query you gave me is so much more efficient!!! like 600000X more efficient ) I’ve modified it like this: SELECT DISTINCT t1.id, t1.name\_score, t1.description\_score FROM((SELECT p.id, MATCH (p.name) AGAINST (‘the color purple’) AS name\_score, 0 as description\_scoreFROM Product as p USE INDEX (title\_search), ProductCategory as pc, Category as cWHERE p.id=pc.product\_id AND c.id=pc.category\_id AND pc.category\_id IN (1, 18 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 19 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 821 , 822 ) AND MATCH (p.name) AGAINST (‘the color purple’) AND p.needs\_verification = 0 GROUP BY p.id)UNION(SELECT p.id, 0 as name\_score, MATCH (p.description) AGAINST (‘the color purple’) AS description\_scoreFROM Product as p USE INDEX (description), ProductCategory as pc, Category as cWHERE p.id=pc.product\_id AND c.id=pc.category\_id AND pc.category\_id IN (1, 18 , 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 , 44 , 45 , 46 , 47 , 48 , 49 , 50 , 51 , 52 , 53 , 54 , 55 , 56 , 57 , 58 , 59 , 60 , 61 , 62 , 63 , 64 , 65 , 66 , 67 , 19 , 68 , 69 , 70 , 71 , 72 , 73 , 74 , 821 , 822 ) AND MATCH (p.description) AGAINST (‘the color purple’) AND p.needs\_verification = 0 GROUP BY p.id))as t1WHEREid IN ( SELECT product\_id FROM Offer AS o WHERE o.price \> 0 AND o.deleted = 0 )ORDER BY t1.name\_score DESC, t1.description\_score DESCLIMIT 0, 20; However, I can’t figure out how to get the name\_score and the description\_score for each product\_id. As it is, it returns results as follows: first the products with name\_scores, then products with description\_scores, but I would like them combined ±-------±-----------------±------------------+| id | name\_score | description\_score |±-------±-----------------±------------------+| 85913 | 13.5209255218506 | 0 || 278269 | 13.5209255218506 | 0 || 562550 | 13.2236194610596 | 0 || 563006 | 10.31312084198 | 0 || 585079 | 9.44657039642334 | 0 || 563359 | 9.04449462890625 | 0 || 564192 | 9.04449462890625 | 0 || 563912 | 9.03781032562256 | 0 || 561789 | 8.9131498336792 | 0 || 563430 | 8.9131498336792 | 0 || 563538 | 8.9131498336792 | 0 || 564222 | 8.9131498336792 | 0 || 585593 | 8.80795288085938 | 0 || 564747 | 8.74496555328369 | 0 || 577924 | 8.74496555328369 | 0 || 574275 | 8.42759227752686 | 0 || 574276 | 8.42759227752686 | 0 || 564467 | 8.29081344604492 | 0 || 563892 | 8.15531826019287 | 0 || 561622 | 7.94574594497681 | 0 |±-------±-----------------±------------------+20 rows in set (1.80 sec) Thanks, Kevin