MySQL Forums
Forum List  »  Optimizer & Parser

Re: Why not use index
Posted by: Øystein Grøvlen
Date: November 12, 2015 01:48AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1685
November 08, 2015 11:14PM
854
November 09, 2015 11:42AM
864
November 11, 2015 12:17AM
896
November 10, 2015 10:35AM
1049
November 11, 2015 12:15AM
938
November 11, 2015 03:40AM
1190
November 11, 2015 07:29PM
832
November 11, 2015 11:00PM
Re: Why not use index
927
November 12, 2015 01:48AM
895
November 16, 2015 12:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.