mysql - 通过 OR 优化左连接？

Question

这是我的查询(已编辑)

SELECT
  en.name AS name,
  en.entity_id,
  COUNT(o.order_id) AS orders_qty,
  ROUND(SUM(o.total)) AS orders_sum,
  ROUND((SUM(o.total) / COUNT(o.order_id))) AS average_purchase,
  MIN(o.date_added) AS first_purchase,
  MAX(o.date_added) AS last_purchase,
  (SELECT COUNT(*) FROM oc_order WHERE order_status_id <> 0 AND customer_id = c.customer_id AND date_added <= NOW() - INTERVAL 3 MONTH) as periodicity
FROM oc_xile_entity en
  LEFT JOIN oc_xile_customer_to_entity c2en ON (c2en.entity_id = en.entity_id)
  LEFT JOIN oc_customer c ON (c2en.customer_id = c.customer_id)
LEFT JOIN oc_order o FORCE INDEX FOR JOIN (`unreg_customer_id`) ON ((o.customer_id = c2en.customer_id OR o.unreg_customer_id = c2en.customer_id) AND order_status_id <> 0)
  WHERE en.entity_id IS NOT NULL
GROUP BY en.entity_id
ORDER BY name ASC
LIMIT 0,700

...以及表格的相关部分...

CREATE TABLE oc_order 
( order_id int(11) NOT NULL PRIMARY KEY 
, customer_id int(11) NOT NULL DEFAULT '0'
, unreg_customer_id (unreg_customer_id)
, unreg_customer_id int(11) NOT NULL
, order_status_id int(11) NOT NULL DEFAULT '0'
, INDEX (customer_id)
);

CREATE TABLE oc_customer 
(customer_id int(11) NOT NULL PRIMARY KEY);

CREATE TABLE oc_xile_customer_to_entity 
(entity_id int(11) NOT NULL
,customer_id int(11) NOT NULL
,PRIMARY KEY (entity_id,customer_id)
,INDEX entity_id
);

当我查询表顺序 35506 行时，它花费了超过 15 秒

最繁重的查询是

LEFT JOIN oc_order o ON ((o.customer_id = c2en.customer_id OR o.unreg_customer_id = c2en.customer_id) AND order_status_id  0)

最好的优化方式是添加FORCE INDEX FOR JOIN (unreg_customer_id)像这样LEFT JOIN oc_order o FORCE INDEX FOR JOIN (unreg_customer_id) ON ((o.customer_id = c2en.customer_id OR o.unreg_customer_id = c2en.customer_id) AND order_status_id <> 0)其速度提升，查询时间变为3秒。但我认为它可以优化得更好。有谁能够帮助我？查询的解释

Answer 1

呃？该查询对 LEFT JOIN 不执行任何操作。然而，优化器可能太笨了，无法丢弃它们并获得等效的查询:

SELECT  en.name AS name , en.entity_id
    FROM  oc_xile_entity en
    WHERE  en.entity_id IS NOT NULL
    GROUP BY  en.entity_id
    ORDER BY  name ASC
    LIMIT  0 , 700

那么这可能是“不正确的”，因为您选择了名称，但没有按它进行分组。如果有多个名称与一个entity_id关联，那么应该显示哪个名称？

请描述您想要实现的目标，并提供SHOW CREATE TABLE oc_xile_entity。