mysql fulltext query is very slow

Question

I have one table for User detail in MySql with about 500000 records in it. I have also created fulltext index on firstname, lastname field on this table. but when I am trying to search any single latter/alphabet (e.g. a to z, single character), it is responding very slow in first time. It's taking about 5-6 seconds to respond. after that, it's come down to 800 milliseconds. EXPLAIN command seems ok as It shows "fulltext" in type column, but I couldn't find why it is reacting very slow.

my query is looks like as follows.

SELECT  SQL_NO_CACHE usr.id, usr.uname, ifnull(usr.fullname,'') fullname,
        ifnull(ct.City, '') city,
        MATCH(usr.fname,usr.lname) AGAINST('a*' IN BOOLEAN MODE) ordfld 
FROM usertable usr
LEFT JOIN citymas ct ON ct.CityID = upm.CityID
WHERE usr.UserStatus IN(10,11)
AND usr.id <> 1
AND MATCH(usr.fname,usr.lname) AGAINST('a*' IN BOOLEAN MODE) > 0  
ORDER BY ( CASE WHEN usr.fullname = 'a' THEN 1
                WHEN usr.fname rlike 'a%' THEN 2 
                WHEN usr.lname LIKE 'a%' THEN 3 
                WHEN usr.fname like '%a' THEN 6 
                WHEN usr.lname LIKE '%a' THEN 7 
                WHEN usr.fullname LIKE '%a%' THEN 8
                ELSE 10 END ),
 ordfld DESC,
( CASE WHEN ifnull(usr.cityid,0) = 234 THEN '0' ELSE '1' END ), usr.fullname 
LIMIT 20

and explain show me following

1, 'SIMPLE', 'usr', 'fulltext', 'PRIMARY,IX_usertable_fname_lname', 'IX_usertable_fname_lname', 0, NULL        , 1, 'Using where; Using filesort'
1, 'SIMPLE', 'ct' , 'eq_ref'  , 'PRIMARY'                         , 'PRIMARY'        ,          3, 'usr.cityid', 1, NULL

above query is taking too much time, it is responding between 800-900ms.

Any guess?

Answer 1

Usually FULLTEXT searches need more than a single letter, there is actually a setting that prevents them from being used if less than X number of characters are used.

ft_min_word_length: The minimum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable

My guess is that you are not using your index when you search for a single character like that.