MYSQL查询，优化LIKE和NOT IN [英] MYSQL Query, Optimize LIKE and NOT IN

问题描述

我有一个查询需要运行，目前运行速度非常慢。我正在使用的表有近100万条记录。

我需要做的是搜索具有LIKE名称的项目：示例SELECT name，other，stuff FROM my_table WHERE name LIKE'％some_name％'

另外，它必须能够排除某些条款搜索结果中的通配符，所以它变成这样的：

  SELECT name，other，stuff 
 FROM my_table 
 WHERE name LIKE'％some_name％'
 AND name NOT IN（SELECT name FROM my_table WHERE name LIKE'％term1％'AND name LIKE'％term2％'AND name LIKE'％term3％'）
  

最重要的是，我有两个INNER JOIN，我必须在PHP中执行它。我在表中列出了相关列的索引。它在服务器上的速度非常快，几乎可以在其中查询任何其他查询。

问题是，有没有其他方法可以用来执行这种类型的查询很快？

更新

这是我在FULLTEXT索引中添加后的实际查询化学表和尝试匹配功能。注：我无法控制表/列名称。

  CREATE FULLTEXT INDEX name_index ON chems（名称）
 
 SELECT f .name fname，f.state，f.city，f.id fid，
 cl.alt_facilid_ida，cl.alt_facili_idb，
 c.ave，c.name，c.id chem_id，
 cc.first_name，cc.last_name，cc.id cid，cc.phone_work 
 FROM facilities f 
 INNER JOIN facilitlt_chemicals_c cl ON（f.id = cl.alt_facilid485ilities_ida）
 INNER JOIN chems c ON （cl.alt_facili3998emicals_idb = c.id）
 LEFT OUTER JOIN facilities_contacts_c con ON（f.id = con.alt_facili_ida）
 LEFT OUTER JOIN联系人cc ON（con.alt_facili_idb = cc.id）
 WHERE match（c.name）against（'+ lead -gas'BOOLEAN MODE）
  

仅仅是一个简单查询的例子。实际的术语可能很多。

解决方案

您希望在您正在搜索的列上实现MySQL的全文功能。请阅读此处。

此外，你可能想做一个布尔搜索：

  select 
 t1.name，
 t1.stuff 
 from 
 my_table t1 
其中
匹配（t1.name）针对（'+ some_name -term1 -term2 -term3'in布尔模式）
  

I have a query I need to run which is currently running extremely slow. The table I am working with has nearly 1 million records.

What I need to do is search for items with a LIKE name : Example "SELECT name, other, stuff FROM my_table WHERE name LIKE '%some_name%'"

In addition it has to be able to exclude certain terms with wildcards from the search results so it turns into something like this :

SELECT name, other, stuff 
FROM my_table 
WHERE name LIKE '%some_name%' 
AND name NOT IN (SELECT name FROM my_table WHERE name LIKE '%term1%' AND name LIKE '%term2%' AND name LIKE '%term3%')

To top things off, I have two INNER JOINs and I have to execute it in PHP. I have indexes on the table for relevant columns. It is on a server that is fast for pretty much every other query I can throw at it.

The question is, is there a different method I could be using to do this type of query thats is quick?

UPDATES

This is my actual query after adding in FULLTEXT index to the chem table and trying the match function instead. NOTE : I don't have control over table/column names. I know they dont look nice.

CREATE FULLTEXT INDEX name_index ON chems (name)

SELECT f.name fname, f.state, f.city, f.id fid,
cl.alt_facilid_ida, cl.alt_facili_idb,
c.ave, c.name, c.id chem_id,
cc.first_name, cc.last_name, cc.id cid, cc.phone_work
FROM facilities f
INNER JOIN facilitlt_chemicals_c cl ON (f.id = cl.alt_facilid485ilities_ida)
INNER JOIN chems c ON (cl.alt_facili3998emicals_idb = c.id)
LEFT OUTER JOIN facilities_contacts_c con ON (f.id = con.alt_facili_ida)
LEFT OUTER JOIN contacts cc ON (con.alt_facili_idb = cc.id)
WHERE match(c.name) against ('+lead -gas' IN BOOLEAN MODE)

That is just an example of a simple query. The actual terms could be numerous.

解决方案

You want to implement MySQL's full text capabilities on the columns you're searching. Read more about it here.

Moreover, you probably want to do a boolean search:

select
    t1.name,
    t1.stuff
from
    my_table t1
where
    match(t1.name) against ('+some_name -term1 -term2 -term3' in boolean mode)

这篇关于MYSQL查询，优化LIKE和NOT IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！