php - 使用 LIKE 运算符优化 10k 记录的 mysql 查询

Question

注意:

正如我所想，真正的问题是因为我用于标记的 IN 子句。更改文本搜索的查询部分并没有多大帮助。知道如何改进查询吗？

查询在服务器上运行时花费的时间太长。这里Partha S是用户输入的搜索项。表contacts包含个人信息，tags包含类别名称和id； contact2tags表包含contactid和tagid，其值分别类似于联系人和标签中的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(all cols) AGAINST(search item) 和 MATCH(single cols) AGAINST (search item) 与 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

Answer 1

一项优化是，在精确情况下，您不需要使用 LIKE(您应该仅将其与通配符 - % 一起使用)。

为了加快速度，您可以做的另一件事是向您要搜索的文件添加索引。

此外，仅当您使用 MyISSAM 作为存储引擎(针对该表)时，您才可以像这样使用全文搜索

SELECT * FROM 正常 WHERE MATCH(标题, 正文) 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%' )

似乎给整个过程带来的值(value)很小。

希望这有帮助。