以下是对耗时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
您拥有的LEFT
联接是多余的,因为您没有为结果中的任何内容使用正确的表。并且由联接产生的任何重复行都被DISTINCT
移除。
因此,您的查询等效于从表中获取所有行的查询(只要wp_pods_bars
中有主键或唯一键):
SELECT t.* FROM wp_pods_bars AS t ;
没有DISTINCT
、JOIN
、OR
。
MariaDB中有一个功能,名为表消除(另请参阅Sergey Petrunia的博客中的解释),它可能会按照您的意愿工作并消除加入,但当然,从MySQL切换到MariaDB可能不是您的可用选项。