Query parsing 16k records out of 34k records

Question

Table User has around 34k records.

I am firing this query-

SELECT user_pid, user_fname, user_lname, user_uname, user_mobno, user_email, user_date,
  user_userid, user_type, user_status
FROM User
WHERE ((lower(user_fname) like'%all%'
   OR lower(user_fname) like'%that%') 
   OR (lower(user_lname) like'%all%'
   OR lower(user_fname) like'%that%'))
   AND user_status!=3
   AND user_type != 1
LIMIT 20;

Explain result is-

+----+-------------+---------+-------+---------------+-------+---------+------+-------+-------------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows  | Extra       |
+----+-------------+---------+-------+---------------+-------+---------+------+-------+-------------+
|  1 | SIMPLE      | User | range | users         | users | 4       | NULL | 16967 | Using where |
+----+-------------+---------+-------+---------------+-------+---------+------+-------+-------------+

Index on table-

users(user_type, user_status)

Where should I add indexes to make this query fast ?

possible duplicate :http://stackoverflow.com/questions/2042269/how-to-speed-up-select-like-queries-in-mysql-on-multiple-columns — Suresh Kamrushi, Jan 20 '14 at 13:30
Note that LIMIT without ORDER BY is pretty much meaningless, and most implementations of MySQL are case insensitive. — Strawberry, Jan 20 '14 at 13:32
Perhaps if you explain what this query is trying to accomplish, we will have other suggestions. — Marcus Adams, Jan 20 '14 at 13:48
I want to search all the possible combinations. Suppose I typed 'all that' then I'll search all in firstname as well as in lastname and viceversa — Sidd, Jan 20 '14 at 13:54

score 2 · Answer 1 · answered Jan 20 '14 at 13:39

This is your query

SELECT user_pid, user_fname, user_lname, user_uname, user_mobno, 
       user_email, user_date, user_userid, user_type, user_status
FROM User
WHERE ((lower(user_fname) like'%all%' or lower(user_fname) like '%that%') or
       (lower(user_lname) like'%all%' or lower(user_fname) like '%that%' )
      ) and user_status!=3 and user_type != 1
limit 20;

First, just based on the size of the matching results, 16k out of 34k, indexes probably cannot help you.

Let me further elaborate. Your like clauses are full wildcard searches. A regular index can be used for like, but only when the pattern starts with non-wildcards. All your patterns start with %, so regular indexes cannot work.

Your comparisons are non-equals comparisons. Once again, an index would rarely be used for these. Indexes are best applied to equality predicates.

The only indexing scheme that I could foresee would be full text indexes on the name fields, user_fname and user_lname. I'm not sure if that would help performance, but you could try.

...but you could try. (but possibly not if you're returning more than 50% of the data set) — Strawberry, Jan 20 '14 at 13:41
@Strawberry . . . Actually, only 20 records are being returned. I'm not quite sure how the `limit` would interact with the other factors on the full text search. — Gordon Linoff, Jan 20 '14 at 14:49

Query parsing 16k records out of 34k records

1 Answers1