11左加入S使查询非常缓慢。

Question

我有一个大查询，它包含11个左联接。当我运行它的时候。它得到12s返回结果。是有人能帮我把这个查询速度弄得那么大。是sql代码的新手。我代理解释结果的图像。

SELECT `idclients`, `clientsNameFirst`, `clientsNameLast`, `clientsEmail`, `clientsTelNoPersonalMobile`
FROM `clients`
LEFT JOIN `experienced` ON `experienced`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `designation` ON `designation`.`iddesignation` = `experienced`.`designation_iddesignation`
LEFT JOIN `industry` ON `industry`.`idindustry` = `experienced`.`industry_idindustry`
LEFT JOIN `company` ON `company`.`idcompany` = `experienced`.`company_idcompany`
LEFT JOIN `job_category` ON `job_category`.`idjobCategory`=`experienced`.`job_category_idjobCategory`
LEFT JOIN `academic_qualifications` ON `academic_qualifications`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `qulification` ON `qulification`.`idqulification` = `academic_qualifications`.`qulification_idqulification`
LEFT JOIN `institute` ON `institute`.`idinstitute` = `academic_qualifications`.`institute_idinstitute`
LEFT JOIN `client_tag` ON `client_tag`.`clients_idclients` = `clients`.`idclients`
LEFT JOIN `tag` ON `tag`.`idtag` = `client_tag`.`tag_idtag`
LEFT JOIN `cv` ON `cv`.`clients_idclients` = `clients`.`idclients`
WHERE (   `clients`.`fullName` LIKE '%Anti money laundering executive%' 
       OR `clients`.`clientsEmail` LIKE '%Anti money laundering executive%' 
       OR `clients`.`clientsTelNoPersonalMobile` LIKE '%Anti money laundering executive%' 
       OR `designation`.`designationName` LIKE '%Anti money laundering executive%' 
       OR `cv`.`cvName` LIKE '%Anti money laundering executive%' 
       OR `industry`.`industryName` LIKE '%Anti money laundering executive%' 
       OR `company`.`companyName` LIKE '%Anti money laundering executive%' 
       OR `qulification`.`qulificationName` LIKE '%Anti money laundering executive%' 
       OR `institute`.`instituteName` LIKE '%Anti money laundering executive%' 
       OR `job_category`.`jobCategoryName` LIKE '%Anti money laundering executive%' 
       OR `tag`.`tagName` LIKE '%Anti money laundering executive%' )
AND `clients`.`status_idstatus` != '10'
AND `clients`.`clientsAge` >= '0'
AND `clients`.`clientsAge` <= '100'
AND `clients`.`clientsOtherQualifications` = '1'
GROUP BY `clients`.`idclients`
ORDER BY `clients`.`idclients` DESC
 LIMIT 30

在使用EXPLAIN运行这段代码之后，我得到了这个结果。

这是我的分贝

这是我的客户桌担架

Answer 1

重新开始。

在一个TEXT列中构建一个包含所有字符串的表。在上面使用FULLTEXT索引。让它成为搜索表。在从该表中找到id之后，只查找那些ids，然后在没有LIKEs的情况下执行JOINs。

CREATE TABLE Search (
    idclient INT NOT NULL,      -- for JOINing to clients.idclient
    stuff MEDIUMTEXT NOT NULL,  -- all the strings collected together
    PRIMARY KEY(idclient),
    FULLTEXT(stuff)
) ENGINE=InnoDB;

SELECT ...
    FROM Search AS s
    JOIN client AS c USING(idclient)
    LEFT JOIN ... -- without the LIKE
    WHERE MATCH(s.stuff) AGAINST('+Anti +money +laundering +executive' IN BOOLEAN MODE)
      AND c.status_idstatus...
      AND ...

MATCH将负责并将行缩减到极少数，从而使查询的其余部分运行得很快。

有三件事会让你的查询变得缓慢：

• 或者.使索引毫无用处
• 引导LIKE中的通配符--使索引毫无用处
• 把所有的字符串分散在多个表上

也就是说，LEFT JOIN (与标题相反)并不是主要的反派。

如果年龄测试(等)不适用，也可能会更好。