[barrkel]

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]

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]

> 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.