3 ms·

STRAIGHT_JOIN is probably my favourite feature of MySQL in terms of planner hints; but there's actually a deeper inconsistency behind the philosophy. Usually,

STRAIGHT_JOIN is probably my favourite feature of MySQL in terms of planner hints; but there's actually a deeper inconsistency behind the philosophy. Usually, when you get a bad query plan, it's because the join order isn't right. Outside the start table and hash joins, indexes need to match up with both predicates and the join keys. Get the wrong join order and then your indexes aren't used. Since you need to specify which indexes to build and maintain, and such indexes are generally predicated on the query plan, why not ensure that the query is using the expected indexes? If one really wants to go down the route of no optimizer hints, then the planner should start making decisions about what indexes to build and update. Go all in.

SigmundA 6y ago
Right db developers decide what indexes to add but aren't allowed decide if and how they are used. Join order / type and which indexes to use would go a long way, thats pretty much all I need to do on MSSQL server if the planner is not cooperating.
- magicalhippo 6y ago
  > Join order Had to fight this a few times, planner thought it was smart to scan an index for a few million rows, then throw almost all of them away in a join further up, ending up with a few hundred rows. Caused the query to take almost a minute. Once the join order was inverted (think I ended up with nesting queries) the thing took a second or two.
jeltz 6y ago
> why not ensure that the query is using the expected indexes? How should your database know this unless you explicitly named the indexes in your query? Just because there exists an index on a particular predicate does not mean that using the index would result in a faster query.