我有一个 wordpress 插件,它本质上创建一个 mysql 查询并将结果返回到 wordpress。
它是用户驱动的,因此最终可能会出现带有多个 NOT LIKE 的大型查询,从而导致查询非常慢。
我可以用来改进的任何建议:
SELECT field1,field2,field3,field4
from datatable
WHERE (title NOT LIKE '%word%' AND title NOT LIKE '%word2%'
AND title NOT LIKE '%word3%' AND title NOT LIKE '%word4%'
AND title NOT LIKE '%word5%' AND title NOT LIKE '%word6%'
AND title NOT LIKE '%word7%' AND title NOT LIKE '%word8%'
AND title NOT LIKE '%word9%')
AND MATCH (title) AGAINST ("\"brandname\" " IN BOOLEAN MODE)
ORDER BY total ASC LIMIT 0,60
客户向 WordPress 插件添加了大量否定关键字,这导致查询量比上面的查询量更大。
这最容易完成REGEXP http://dev.mysql.com/doc/refman/5.1/en/regexp.html。对于多个单词,请使用类似的组(one|two|three)
SELECT
field1,
field2,
field3,
field4
from datatable
WHERE
title NOT REGEXP '(word1|word2|word3|word4|word5...|word9)'
AND MATCH (title) AGAINST ("\"brandname\" " IN BOOLEAN MODE)
ORDER BY total ASC
LIMIT 0,60
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)