如何从Doctrine QueryBuilder优化这个MySQL查询?

Question

此查询由 doctrine2 QueryBuilder 生成(concat 函数仅需要 2 个参数(，需要 4 秒。

SELECT  COUNT(*) AS dctrn_count
FROM  
(
SELECT  DISTINCT id_4
FROM  
(
SELECT  1 / LOCATE( ?, CONCAT( CONCAT( CONCAT(w0_.firstname, ' '),
CONCAT(w0_.lastname, ' ') ), w1_.fullname ) 
) AS sclr_0,
1 / LOCATE( ?, CONCAT( CONCAT( CONCAT(w0_.firstname, ' '),
CONCAT(w0_.lastname, ' ') ), w1_.shortname ) 
) AS sclr_1,
1 / LOCATE( ?, CONCAT( CONCAT( CONCAT(w0_.nickname, ' '),
CONCAT(w0_.lastname, ' ') ), w1_.fullname ) 
) AS sclr_2,
1 / LOCATE( ?, CONCAT( CONCAT( CONCAT(w0_.nickname, ' '),
CONCAT(w0_.lastname, ' ') ), w1_.shortname ) 
) AS sclr_3,
w0_.id AS id_4, w0_.slug AS slug_5, w0_.firstname AS firstname_6,
w0_.lastname AS lastname_7, w0_.nickname AS nickname_8,
w0_.gender AS gender_9, w0_.email AS email_10, w0_.email_checked AS email_checked_11,
w0_.title_en AS title_en_12, w0_.short_title AS short_title_13,
-- lots of stuff removed (see edit) --
w5_.biography_en AS biography_en_55, w5_.created AS created_56, w5_.updated AS updated_57, w6_.id AS id_58, w6_.web_text AS web_text_59, w6_.created AS created_60
FROM  wmn_executive w0_
INNER JOIN  wmn_company w1_  ON w0_.company_id = w1_.id
INNER JOIN  wmn_industry w7_  ON w1_.industry_id = w7_.id
INNER JOIN  wmn_location w2_  ON w1_.location_id = w2_.id
INNER JOIN  wmn_country w3_  ON w2_.country_id = w3_.id
INNER JOIN  wmn_city w4_  ON w2_.city_id = w4_.id
LEFT JOIN  wmn_executive_link w5_  ON w0_.link_id = w5_.id
LEFT JOIN  wmn_web_executive w6_  ON w0_.id = w6_.executive_id
WHERE  w0_.original_id IS NULL
AND  w0_.user_id IS NOT NULL
AND  ( w0_.firstname LIKE ?
OR  w0_.lastname LIKE ?
OR  w0_.nickname LIKE ?
OR  w1_.fullname LIKE ?
OR  w1_.shortname LIKE ?
OR  w0_.title_en LIKE ?
OR  w0_.short_title LIKE ?
OR  w7_.industry_name_en LIKE ?
OR  w7_.industry_name_fr LIKE ?
OR  w3_.country_name_en LIKE ?
OR  w3_.country_name_fr LIKE ?
OR  w4_.city_name LIKE ? 
)
ORDER BY  sclr_0 DESC, sclr_1 DESC, sclr_2 DESC, sclr_3 DESC ) dctrn_result 
) dctrn_table

Answer 1

**ORDER BY对最终结果没有任何好处;请将其删除。

**

SELECT  COUNT(*) AS dctrn_count
FROM  
(
SELECT  DISTINCT id_4

可以简化为

SELECT COUNT(DISTINCT(id_4))

** SELECT 子句中的所有项目都无法使用，除了id_4;删除它们。

这 3 个优化可能会将运行时间从 4.0s 缩短到 3.9s。

然后你会说这不是真正的查询，而只是一个计数？

如果您要进行这样的凌乱文本扫描，则需要将所有这些字符串放在一个表中。 更好的是，所有字符串都连接在一个表中的一列中。 这只是为了搜索，而不是为了显示。 然后在该列上创建一个FULLTEXT索引。 这将解决OR和LIKE '%...'问题。 但是如何让它回到教义2，我不知道。