Mysql 查询在 OR 条件下运行时间过长

Question

这里是查询什么需要12秒

SELECT SQL_CALC_FOUND_ROWS DISTINCT `t`.* FROM `wp_pods_bars` AS `t` 
LEFT JOIN `wp_podsrel` AS `rel_city` ON 
    ( `rel_city`.`field_id` = 13918 AND `rel_city`.`item_id` = `t`.`id` ) OR 
    ( `rel_city`.`related_field_id` = 13918 AND `rel_city`.`related_item_id` = `t`.`id` ) 

如果我这样做

SELECT SQL_CALC_FOUND_ROWS DISTINCT `t`.* FROM `wp_pods_bars` AS `t` 
LEFT JOIN `wp_podsrel` AS `rel_city` ON 
    ( `rel_city`.`field_id` = 13918 AND `rel_city`.`item_id` = `t`.`id` )

或

SELECT SQL_CALC_FOUND_ROWS DISTINCT `t`.* FROM `wp_pods_bars` AS `t` 
LEFT JOIN `wp_podsrel` AS `rel_city` ON 
    ( `rel_city`.`related_field_id` = 13918 AND `rel_city`.`related_item_id` = `t`.`id` ) 

每个查询运行 0.09 秒。为什么会这样以及如何解决这个问题？

编辑:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT `t`.* FROM `wp_pods_bars` AS `t` 
LEFT JOIN `wp_podsrel` AS `rel_city` ON 
    ( `rel_city`.`related_field_id` = 13918 AND `rel_city`.`related_item_id` = `t`.`id` ) 

结果

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,t,ALL,NULL,NULL,NULL,NULL,1340,Using temporary
1,SIMPLE,rel_city,ref,rel_field_rel_item_idx,rel_field_item_idx,rel_field_rel_item_idx,14,const,barchick.t.id,1,Using index; Distinct

第二个

EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT `t`.* FROM `wp_pods_bars` AS `t` 
LEFT JOIN `wp_podsrel` AS `rel_city` ON 
( `rel_city`.`related_field_id` = 13918 AND `rel_city`.`related_item_id` = `t`.`id` ) 

结果

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,t,ALL,NULL,NULL,NULL,NULL,1340,Using temporary
1,SIMPLE,rel_city,ref,rel_field_rel_item_idx,rel_field_item_idx,rel_field_rel_item_idx,14,const,barchick.t.id,1,Using index; Distinct 

Answer 1

您拥有的 LEFT 联接是多余的，因为您没有对结果中的任何内容使用正确的表。 DISTINCT 会删除连接产生的任何重复行。

因此您的查询等同于从表中获取所有行的查询(只要 wp_pods_bars 中有主键或唯一键):

SELECT t.* FROM wp_pods_bars AS t ;

没有DISTINCT，没有JOIN，没有OR。

---

MariaDB 中有一个特性，叫做 Table Elimination (另请参见 Sergey Petrunia's blog 中的解释)可能会按照您的意愿工作并消除连接，但当然从 MySQL 切换到 MariaDB 可能不是您可用的选项。