笔记：

因此，正如我所想到的，真正的问题是因为我使用了用于tagid的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