1

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?

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.

  • thanks @dgig, I have changed ft_min_word_length and rebuilt index again. but when I restart mysqld service and then run query first time it is taking to much time (may be worm up time), but still this is taking too much time. do you any guess – Manish Sapkal Jul 19 '14 at 8:16
  • My guess is full text searches are not meant for single letters like that. I would suggest having just a standard index on there as well. – dgig Jul 21 '14 at 13:17
  • Fulltext is meant generally for phrases and whole words. I would suggest having just a standard index on there as well. I also think that ORDER statement is slowing you down and I'm not sure what its purpose is. – dgig Jul 21 '14 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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