CODEWITHSUNDEEP
mysqlsqlindexing
stergosz
stergosz

Reputation: 5860

mysql LIKE query takes too long

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?

Upvotes: 6

Views: 14984

Answers (6)

Huckleberry
Huckleberry

Reputation: 41

In my situation, an index on a 'Value' field was slowing down selection when using the 'like' operator with a leading '%':

select distinct My_Value
from My_Table
where My_Value like '%a%';

If I removed the index, the same query ran quickly. But I needed the index for other situations.

In researching discovered the 'Ignore Index' command and rewrote the query and problem solved:

select distinct My_Value
from My_Table Ignore Index (My_Index)
where My_Value like '%a%';

More info on using 'Ignore Index': https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

Upvotes: 2

stergosz
stergosz

Reputation: 5860

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

Seems a temporary solution or even permanent.

Upvotes: 0

Ben English
Ben English

Reputation: 3918

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

Upvotes: 0

Frank Luke
Frank Luke

Reputation: 1400

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'.

Upvotes: 18

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

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).

Upvotes: 2

Jim
Jim

Reputation: 22656

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.

Upvotes: 7

Related Questions