Hi,
Generally, queries with OR in where clause is not very efficient. Is it possible to avoid it? Could SAM.admin_user_id='24577' be moved to the corresponding ON clause? (I guess that depends on whether admin_user_id can actually be NULL in the table or just as a result of the LEFT JOIN).
Note also that the first LEFT JOIN has no effect since the ON condition of the next INNER JOIN requires that SFII.order_item_id is non-null.
Ways to get another plan:
- You can try to set optimizer_switch='block_nested_loop=off' and see if that
gives a better join ordering.
- You can force a specific table order by rewriting the FROM clause so that
tables come in the order you want and put STRAIGHT_JOIN after SELECT.
- Instead of rewriting the entire query, you could replace INNER JOIN with
STRAIGHT_JOIN where tables come in the order that fits with your wanted plan
(or rearrange two tables to get right order). For example, if you replace
INNER JOIN with STRAIGHT_JOIN between SFSI and SFO, you should get a different
join order from the original (but not necessarily the staging order)
- You may force an index that is only used in the wanted plan. For example,
if you put FORCE INDEX(IDX_order_item_id) after SFII in FROM clause, you should
probably get a join order where SFOI comes before SFII.
- You can create index on columns with conditions that do not currently have it.
Do you have index on SFOI.created_at?
- You can upgrade to 5.7 which has improvements wrt how the size of intermediate
join results are estimated.
That said, I find your plan on the production system a bit strange, and wonder whether it could be caused by a bug in the optimizer. Particularly, why the optimizer choose to not use an index when accessing the SFST table. Please, file a bug report at bugs.mysql.com so we can investigate this further. In order to work on the bug, we would also need to have the optimizer trace for
the query (see
http://oysteing.blogspot.ro/2016/01/how-to-get-optimizer-trace-for-query.html for how to get a trace. Please, note the part about how to make sure the full trace is captured.) A reproducible test case would be even better, but I understand that this could be difficult.
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway