MySql count with like query 响应很慢 [英] MySql count with like query is very slow response

问题描述

下面是我的查询，查询速度很慢，我尝试设置一些索引但索引不起作用.表有近 150000 行.请帮帮我.

Below is my query, query is very slow and I tried to set some index but indexes are not working. Table has almost 150000 rows. Please help me out.

SELECT COUNT(*) AS COUNT
FROM attachment
    LEFT JOIN candidate ON attachment.data_item_id = candidate.candidate_id 
        AND attachment.data_item_type = 100 
        AND attachment.site_id = candidate.site_id
    LEFT JOIN USER AS owner_user ON candidate.owner = owner_user.user_id
WHERE attachment.RESUME = 1 
AND attachment.data_item_id != 0 
AND( (attachment.text LIKE '%java%') ) 
AND( ISNULL(candidate.is_admin_hidden) OR(candidate.is_admin_hidden = 0) ) 
AND( ISNULL(candidate.is_active) OR(candidate.is_active = 1) ) 
AND attachment.site_id = 1

附件表索引

候选表索引

推荐答案

LEFT 是否有必要?AND a.data_item_type = 100 在 ON 中的意图是什么?听起来像是一个过滤器"，应该在 WHERE 中.

Is LEFT necessary? What is the intent of AND a.data_item_type = 100 in an ON? It sounds like a "filter", which should be in the WHERE.

通过允许(并测试)NULL 和 0 来阻碍索引.选择一个，然后相应地编码.如果你保持NULL而不是0，这样表达:candidate.is_active IS NULL.

The indexes are thwarted by allowing (and testing for) both NULL and 0. Pick one, then code accordingly. If you keep NULL instead of 0, express it this way: candidate.is_active IS NULL.

或者...这是ISNULL(candidate.is_admin_hidden)测试LEFT是否未能找到行而不是检查列本身?

Or... Is this ISNULL(candidate.is_admin_hidden) testing whether the LEFT failed to find a row instead of checking the column itself?

删除加入owner_user，它似乎没有做任何事情(除了减慢查询速度).

Remove the Join to owner_user, it seems to be doing nothing (other than slowing down the query).

很少有前缀"索引(例如，skills(255))很有用.

Rarely is a "prefix" index (eg, skills(255)) useful.

INDEX(..., email1, email2, ...) 的唯一实际用途是如果您要测试两个 电子邮件的[不同] 值.

The only practical use for INDEX(..., email1, email2, ...) is if you will be testing both emails for [different] values.

更多

综合索引:

attachment: INDEX(site_id, RESUME)  -- in either order
candidate:  INDEX(site_id, candidate_id, is_active, is_admin_hidden, owner)
owner_user: INDEX(user_id)  -- unless it is the PRIMARY KEY

这篇关于MySql count with like query 响应很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！