mysql LIKE query takes too long

Question

SQL :

        SELECT
            COUNT(usr.id) as `total_results`
        FROM
            users as usr
            LEFT JOIN profile as prof
                ON prof.uid = usr.uid
        WHERE
            usr.username LIKE '%a%'
            OR
            prof.name LIKE '%a%' 

Indexes on users:

uid - uid
username - username

Indexes on profile

index1 - uid
index2 - uid,name
index3 - name

EXPLAIN :

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     usr     ALL     NULL    NULL    NULL    NULL    18387   
1   PRIMARY     prof    ref     index2,index1   index2  8   site.usr.uid    1   Using where
2   DEPENDENT SUBQUERY  sub     ref     i3,index1,index2    i3  16  site.usr.uid,const  1   Using index

the above query takes about 0.1221

how can i make it run faster?

Solution 1

I removed this query so it wont show the total result count when a search is done.

Seems a temporary solution or even permanent.

Solution 2

The % at the beginning of the string to match makes it so that the indexes cannot be used. A wildcard at the beginning nullifies the index and MySQL has to search within that column in every row. It can't skip down. If you know that the item you are searching for will be at the beginning of the beginning of the field, you can remove the beginning '%'.

However, if you are searching for 'steve', my answer will return 'steve', 'steven', 'steve-boss', but not 'boss-steve' or 'realsteve'.

Solution 3

The initial % in your LIKE clauses means that the indexes for these columns cannot be used. I believe that the MySQL full text index may do what you want.

Solution 4

It uses indexes as much as it can (both primary keys for your left join). The main problem is LIKE '%a%', because in that case it cannot use index (if it was 'a%', it would be able to use index on username and name). What you can try (not sure if that will speed up things) is to use concat(usr.username, prof.name) like '%a%', but you will probably not notice any difference.

Full text index will not work, because full text index is useful when you search for a whole word.

Anyway, for the query you have your indexes on name and username are useless and just taking up space, so I would delete them. If LIKE 'a%' satisfies your need, then those indexes make sense.

If 'a%' doesn't satisfy your needs, you might take a look at other options, for example mysql query cache (if you expect frequent repetition of queries).

Solution 5

1) Buy a faster database server.

2) Redesign your database so that you don't have to look in two places for username or only allow users to search on one or the other

View more solutions