mysql LIKE查询花费太长时间 [英] 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%' 

用户索引:

uid - uid
username - username

个人资料索引

index1 - uid
index2 - uid,name
index3 - name

说明:

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

上述查询大约需要0.1221

the above query takes about 0.1221

我如何使其运行更快?

推荐答案

我删除了此查询，因此搜索完成后不会显示总结果数.

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

似乎是临时解决方案，甚至是永久解决方案.

Seems a temporary solution or even permanent.

这篇关于mysql LIKE查询花费太长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！