使用LIKE运算符优化mysql查询以获取10k条记录 [英] optimise mysql query with LIKE operator for 10k records

查看：102
本文介绍了使用LIKE运算符优化mysql查询以获取10k条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

注意:

所以正如我所想，真正的问题是因为我正在使用Tag标记的IN子句.更改用于文本搜索的查询部分并没有太大帮助.知道如何改善查询吗?

在服务器上运行时，查询花费的时间太长.在此，Partha S是用户输入的搜索项目.表联系人包含个人信息，标签包含类别名称和ID；和contacts2tags表包含contactid和tagid，其值分别类似于contacts和tag中的id.

    SELECT *
    FROM
    (
    SELECT *,
     IF
    (
     first_name LIKE 'Partha S'
    OR last_name LIKE 'Partha S'
    OR phone_number LIKE 'Partha S'
    OR mobile_number LIKE 'Partha S'
    OR email_address LIKE 'Partha S'
    OR address LIKE 'Partha S'
    OR organization LIKE 'Partha S'
    OR other LIKE 'Partha S'
    OR sector LIKE 'Partha S'
    OR designation LIKE 'Partha S'
    OR concat ( first_name,  ' ',  last_name ) LIKE 'Partha S'
    OR concat ( last_name,  ' ',  first_name ) LIKE 'Partha S',
     1,
     0 )
     as exact,
     IF
    (
    (
     first_name LIKE '%Partha%'
    OR last_name LIKE '%Partha%'
    OR phone_number LIKE '%Partha%'
    OR mobile_number LIKE '%Partha%'
    OR email_address LIKE '%Partha%'
    OR address LIKE '%Partha%'
    OR organization LIKE '%Partha%'
    OR other LIKE '%Partha%'
    OR sector LIKE '%Partha%'
    OR designation LIKE '%Partha%' )
    AND
    (
     first_name LIKE '%S%'
    OR last_name LIKE '%S%'
    OR phone_number LIKE '%S%'
    OR mobile_number LIKE '%S%'
    OR email_address LIKE '%S%'
    OR address LIKE '%S%'
    OR organization LIKE '%S%'
    OR other LIKE '%S%'
    OR sector LIKE '%S%'
    OR designation LIKE '%S%' )
    ,
     1,
     0 )
     as normal
    FROM contacts
    WHERE id in
    (
    SELECT DISTINCT contacts.id
    from contacts INNER
    JOIN contacts2tags ON contacts.id = contacts2tags.contactid
    WHERE ( tagid in ( 178 ) ) )
     )
     d
    WHERE exact = 1
    OR normal = 1
    ORDER BY exact desc,
     last_name asc LIMIT 0,
     20

更新: 根据建议，我删除了LIKE运算符以进行精确搜索，并在后一种情况下使用MATCH(..)AGAINST(..)代替了LIKE.尽管第一个更改确实改善了性能，但是使用MATCH()AGAINST()并不会意外地改变执行时间.这是更新的查询. PS我尝试将MATCH(所有列)AGAINST(搜索项)和MATCH(单列)AGAINST(搜索项)与OR结合使用.请提出建议.谢谢

     SELECT *
    FROM
    (
    SELECT *,
     IF
    (
         first_name ='Partha S'
       OR last_name ='Partha S'
       OR phone_number ='Partha S'
       OR mobile_number ='Partha S'
       OR email_address = 'Partha S'
       OR address ='Partha S'
       OR organization ='Partha S'
       OR other ='Partha S'
       OR sector ='Partha S'
       OR designation ='Partha S'
       OR concat ( first_name,  ' ',  last_name ) ='Partha S'
       OR concat ( last_name,  ' ',  first_name ) ='Partha S',
       1,
       0 )
      as exact,
       IF
      ( match(first_name,last_name,phone_number,mobile_number,email_address,  address,organization,other,sector,designation) against( 'Partha')                 
    OR  match(first_name,last_name,phone_number,mobile_number,email_address,address,organization,other,sector,designation) against( 'S')


    ,
    1,
    0 )
     as normal
    FROM contacts
    WHERE id in
    (
    SELECT DISTINCT contacts.id
    from contacts INNER
    JOIN contacts2tags ON contacts.id = contacts2tags.contactid
    WHERE ( tagid in ( 178 ) ) )
     )
     d
    WHERE exact = 1
    OR normal = 1
    ORDER BY exact desc,
     last_name asc LIMIT 0,
      20

解决方案

一个优化是在exact情况下，您不需要使用LIKE(您应该仅将其与通配符-％一起使用)./p>

可以使事情变得更快的另一件事是将INDEX添加到要搜索的文件中.

此外，仅当您使用MyISSAM作为该表的存储引擎时，您才能使用全文搜索，

选择*从normal匹配(标题，正文)反对(查询字符串")

first_name LIKE '%S%'
OR last_name LIKE '%S%'
OR phone_number LIKE '%S%'
OR mobile_number LIKE '%S%'
OR email_address LIKE '%S%'
OR address LIKE '%S%'
OR organization LIKE '%S%'
OR other LIKE '%S%'
OR sector LIKE '%S%'
OR designation LIKE '%S%' )

似乎给整个过程带来的价值很小.

希望这会有所帮助.

Note:

So as I figured, the real problem is because of the IN clause I'm using for tagids. Changing the portion of query for text search didn't help much. Any idea how to improve the query?

The query takes too long while running on the server. Here Partha S is a search item entered by user. The table contacts contains personal information , tags contains category name and id; and contacts2tags table contains contactid and tagid with values similar to id in contacts and tags respectively.

    SELECT *
    FROM
    (
    SELECT *,
     IF
    (
     first_name LIKE 'Partha S'
    OR last_name LIKE 'Partha S'
    OR phone_number LIKE 'Partha S'
    OR mobile_number LIKE 'Partha S'
    OR email_address LIKE 'Partha S'
    OR address LIKE 'Partha S'
    OR organization LIKE 'Partha S'
    OR other LIKE 'Partha S'
    OR sector LIKE 'Partha S'
    OR designation LIKE 'Partha S'
    OR concat ( first_name,  ' ',  last_name ) LIKE 'Partha S'
    OR concat ( last_name,  ' ',  first_name ) LIKE 'Partha S',
     1,
     0 )
     as exact,
     IF
    (
    (
     first_name LIKE '%Partha%'
    OR last_name LIKE '%Partha%'
    OR phone_number LIKE '%Partha%'
    OR mobile_number LIKE '%Partha%'
    OR email_address LIKE '%Partha%'
    OR address LIKE '%Partha%'
    OR organization LIKE '%Partha%'
    OR other LIKE '%Partha%'
    OR sector LIKE '%Partha%'
    OR designation LIKE '%Partha%' )
    AND
    (
     first_name LIKE '%S%'
    OR last_name LIKE '%S%'
    OR phone_number LIKE '%S%'
    OR mobile_number LIKE '%S%'
    OR email_address LIKE '%S%'
    OR address LIKE '%S%'
    OR organization LIKE '%S%'
    OR other LIKE '%S%'
    OR sector LIKE '%S%'
    OR designation LIKE '%S%' )
    ,
     1,
     0 )
     as normal
    FROM contacts
    WHERE id in
    (
    SELECT DISTINCT contacts.id
    from contacts INNER
    JOIN contacts2tags ON contacts.id = contacts2tags.contactid
    WHERE ( tagid in ( 178 ) ) )
     )
     d
    WHERE exact = 1
    OR normal = 1
    ORDER BY exact desc,
     last_name asc LIMIT 0,
     20

UPDATE: As per the suggestions, I removed the LIKE operator for exact search, and used MATCH(..) AGAINST(..) instead of LIKE in the latter case. While the first change did improve the performance a little, but using MATCH() AGAINST() didn't change the execution time surprisingly. Here's the updated query. PS I tried using both MATCH(all cols) AGAINST(search item) and MATCH(single cols) AGAINST (search item) combined with OR. Please suggest. thanks

     SELECT *
    FROM
    (
    SELECT *,
     IF
    (
         first_name ='Partha S'
       OR last_name ='Partha S'
       OR phone_number ='Partha S'
       OR mobile_number ='Partha S'
       OR email_address = 'Partha S'
       OR address ='Partha S'
       OR organization ='Partha S'
       OR other ='Partha S'
       OR sector ='Partha S'
       OR designation ='Partha S'
       OR concat ( first_name,  ' ',  last_name ) ='Partha S'
       OR concat ( last_name,  ' ',  first_name ) ='Partha S',
       1,
       0 )
      as exact,
       IF
      ( match(first_name,last_name,phone_number,mobile_number,email_address,  address,organization,other,sector,designation) against( 'Partha')                 
    OR  match(first_name,last_name,phone_number,mobile_number,email_address,address,organization,other,sector,designation) against( 'S')


    ,
    1,
    0 )
     as normal
    FROM contacts
    WHERE id in
    (
    SELECT DISTINCT contacts.id
    from contacts INNER
    JOIN contacts2tags ON contacts.id = contacts2tags.contactid
    WHERE ( tagid in ( 178 ) ) )
     )
     d
    WHERE exact = 1
    OR normal = 1
    ORDER BY exact desc,
     last_name asc LIMIT 0,
      20

解决方案

One optimization is that in the exact case, you don't need to use LIKE (you should only use it with the wildcard - %).

Another thing that you can do to make the things faster is adding an INDEX to the fileds you're going to be searching in.

Also, only if you're using MyISSAM as your storage engine (for that table) you can use full text search like this

SELECT * FROM normal WHERE MATCH(title, body) AGAINST ('Queried_string')

first_name LIKE '%S%'
OR last_name LIKE '%S%'
OR phone_number LIKE '%S%'
OR mobile_number LIKE '%S%'
OR email_address LIKE '%S%'
OR address LIKE '%S%'
OR organization LIKE '%S%'
OR other LIKE '%S%'
OR sector LIKE '%S%'
OR designation LIKE '%S%' )

seems to be bringing very little value to the whole process.

Hope this helps.

这篇关于使用LIKE运算符优化mysql查询以获取10k条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆