CODEWITHSUNDEEP
mysqlsqlindexing
Martin AJ
Martin AJ

Reputation: 6707

Why doesn't my query use any index?

Here is my query:

select u.*,
       concat(user_fname, ' ', user_lname) like concat(?, '%') `both`,
       user_lname like ? last_name
from users u 
where concat(user_fname, ' ', user_lname) like concat(?, '%')
   or user_lname like concat(?, '%')
order by `both`*2 DESC, last_name

Also I've two indexes: users(user_fname,user_lname), users(user_lname).

And here is the result of EXPLAIN:

id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra
1  | SIMPLE      | u     | ALL  | user_lname    | NULL | NULL    | NULL | 9    |  Using where

See? It doesn't use any index. Why? and how can I make the query optimal?

Upvotes: 0

Views: 59

Answers (1)

Rick James
Rick James

Reputation: 142518

  • Don't hide an indexed column inside a function call (CONCAT),
  • OR is deadly for optimization,
  • A leading wildcard (%) makes the index unusable in LIKE,
  • Since you are fetching all the columns, it is impractical to have a "covering" index.
  • In some situations, an index for the ORDER BY can be used instead of for WHERE. But you have a mixture of ASC and DESC, so this is not possible (until version 8.0).

Plan A: Use a FULLTEXT and MATCH on the the fields. (There are limitations to FULLTEXT that may make this unusable.)

Plan B: Have an extra column with the concatenation of the columns and do a single LIKE against it. (This does not fix all the problems.)

Bottom line: Indexes are very efficient if you live within their constraints. Your query violates so many of the limitations, that I don't think there is any hope for the query; it must perform a table scan.

Caveat: There may be more issues.

Upvotes: 2

Related Questions