CODEWITHSUNDEEP
mysqlsql
nastaseion
nastaseion

Reputation: 173

Why MySQL isn't using my FULLTEXT indexes?

I have the following query:

EXPLAIN SELECT *
FROM glean2_saves
WHERE username = '1d85d5aed8b02b3d6b0c155a563293ef'
AND ses_id = 'e4fa3ae216f5033fbd16d6c66370954c'
AND save_status =1
ORDER BY id DESC

And the result is this:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  glean2_saves    ref     save_status,username,ses_id     save_status     2   const   286315  Using where; Using filesort

Here are my indexes:

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  id  331837  A       
Edit Edit   Drop Drop   save_status BTREE   No  No  save_status 3   A   YES 
Edit Edit   Drop Drop   nickname    FULLTEXT    No  No  nickname    7374        YES 
Edit Edit   Drop Drop   username    FULLTEXT    No  No  username    7717        YES 
Edit Edit   Drop Drop   ses_id  FULLTEXT    No  No  ses_id  11442       YES

I do have indexes on the right columns, but why are they not used? How come they are in the "possible keys" (save_status, username, ses_id) but only one "key" is actually used: save_status

Without the indexes, the query takes way too long (sometimes over 15 seconds), it should take less than one second. The database is over 330k entries.

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

For this query, you want one index that covers the where and order by clauses. That index would be:

glean2_saves(username, ses_id, save_status, id)

Note that because you are doing equality comparisons, the first three columns can be in any order. But the last column needs to be id for the order by.

As for why MySQL is not using the individual indexes. Let's just say that merging indexes for where clauses is a lot of work, and might result in more work than scanning one index and doing the tests for the remaining columns.

Upvotes: 2

Related Questions