本文介绍了mysql LIKE查询花费太长时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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查询花费太长时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!