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?
EDIT :
does ft_min_word_len matter? when I changed it in my localhost and rebuild index again, same query returns within 500ms. If I would like to change this on Amazon RDS, How Do I do this?
