MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query not always using indexes
Posted by: Øystein Grøvlen
Date: January 09, 2017 02:30AM

Hi,

That the optimizer will pick block nested join (BNL) over indexed join for such large tables looks like a bug in the optimizer. I hope you can file a bug at bugs.mysql.com so that we can look into this. The best would be if you could attach a reproducible test case. If that is not possible, please, attach an optimizer trace for the query. (See http://oysteing.blogspot.co.uk/2016/01/how-to-get-optimizer-trace-for-query.html for details.)

As a work-around, you can try turning off BNL with the NO_BNL optimizer hint. (See https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-table-level for details.)

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2796
January 06, 2017 05:48AM
933
January 06, 2017 12:28PM
1043
January 09, 2017 05:18AM
Re: Query not always using indexes
905
January 09, 2017 02:30AM


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.