Re: Why not use index

[Øystein Grøvlen]

Hi,

So according to the plan, it will evaluate the use of ix_crm_order_t_source_order_id for every look-up. (That it says ALL for crm is a bit misleading.)

If you have issues with performance, you could try rewriting your query to use UNION to see if that performs better:

SELECT * FROM crm_order_manual_detail_t AS d
JOIN crm_order_manual_t AS m ON d.wp_order_id = m.wp_order_id
LEFT JOIN crm_order_t crm ON m.wp_order_id = crm.source_order_id
UNION ALL
SELECT * FROM crm_order_manual_detail_t AS d
JOIN crm_order_manual_t AS m ON d.wp_order_id = m.wp_order_id
LEFT JOIN crm_order_t crm ON m.source_order_id = crm.source_order_id

If it is the case that there will always be a match in crm for either m.wp_order_id or m.source_order_id, you can then use JOIN instead of LEFT JOIN, which may open up for even better join orders. (And with MySQL 5.7, evaluation of UNION ALL will no longer use a temporary table.)

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway