Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a basic table :

create table fullTextTest
(
    id INT(11) NOT NULL,
    superText CHAR(255) NOT NULL,
    superLongText TEXT NOT NULL,
    primary key (`id`),
    FULLTEXT KEY `superText` (`superText`),
    FULLTEXT KEY `superLongtext` (`superLongtext`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into fullTextTest
    set id=1,
    superText="Hi guys, how is it goin'?",
    superLongtext="Please give me some dummy text to search on!!!"
;

show index from fullTextTest;
| fullTextTest |          0 | PRIMARY       |            1 | id            | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| fullTextTest |          1 | superText     |            1 | superText     | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |
| fullTextTest |          1 | superLongtext |            1 | superLongText | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |

Now , let's see if MySQL is using this index correctly :

EXPLAIN select * from fullTextTest where match(superText) AGAINST ("guys" IN BOOLEAN MODE);
| id | select_type | table        | type     | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------------+----------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | fullTextTest | fulltext | superText     | superText | 0       |      |    1 | Using where |

The "Using Where" shows me that EXPLAIN don't understand FULLText.. Which is a shame, really.

Did I miss something ?

share|improve this question
add comment

1 Answer 1

I can understand why this happening

You only have one row in the table.

MySQL Query Optimizer will execute a plan around this rule-of-thumb : If the number of rows needed to examine the fulfillment of a query through an index exceeds 5% of the total of number of rows, the Query Optimizer will not use the index and will go with a table scan or an index scan instead.

How many rows in the table ? One

How many rows are indexed ? One

Since you will be examining 100% of the rows, a keyed up will not chosen. The query will be executed. You simply do not have enough rows to justify getting an index involved in the search. This applies in general for anything indexes. FULLTEXT indexes tends to throw the Query Optimizer under the bus at the most inconvenient times. I wrote about that back in Jan 26, 2012.

Try loading more rows into the table, at least 21 rows, and try again.

share|improve this answer
    
I have now 30 rows in my table, and still the same issue. –  Cooluhuru May 8 '12 at 7:25
    
I read many of your comments on FULLTEXT indexes yesterday, and found many of the links usefull. –  Cooluhuru May 8 '12 at 7:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.