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.