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?

Answer 1

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

Answer 2

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.