4 ms·

Agree. I'd much prefer they worked on the way MySQL determines what indexes to use on a multi-table join, so there could be less emphasis on the need to ANALYZE

Agree. I'd much prefer they worked on the way MySQL determines what indexes to use on a multi-table join, so there could be less emphasis on the need to ANALYZE TABLE and FORCE INDEX when the DB is seemingly being dumb, but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up. Troubleshooting full table scans on large sets of data is a nightmare.

Tostino 5y ago
Slightly different group of people working on MySQL vs this proposal.
dspillett 5y ago
> but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up Unfortunately I don't think providing that information is generally possible. In some specific cases there are useful details it could provide, but there would usually be a myriad of other similar details that are irrelevant and if it included all those you'd not see the wood for the trees. EXPLAIN and query plan outputs in other DBs are “this is what I did” not really “why I did what I did”. To make the query planner bright enough to know what details would be useful to you, would probably pretty much require making it bright enough to do the optimisation job without you¹. [1] picking better indexes without hints, even creating those that are often needed, etc. MS's SQL Server tries to do this a bit with index suggestions. These are sometimes handy, but often at best for guidance². I've seen people blindly follow these suggestions to get a %-or-few gain from a small set of queries that could see orders of magnitude improvement with just a little tweaking elsewhere³, slowly amassing collections of indexes for very specific cases, sometimes multiple on the same key columns but each INCLUDEing a different mix of other data, that balloon their storage requirements⁴. [2] a nudge in the direction of “Mr Dev/DBA, you might want to think about how I'd avoid scanning this large object or performing many many thousands of seeks on this other one” [3] refactoring non-sargable predicates, index changes on other tables being referred to, getting rid of “SELECT *” particularly when referring to hideous views, ... [4] and having the knock-on effects of slowing insert/update activity & important admin functions (particularly backups)

Tostino 5y ago

Slightly different group of people working on MySQL vs this proposal.

dspillett 5y ago

> but there's not enough information in EXPLAIN to tell you where it's actually getting tripped up Unfortunately I don't think providing that information is generally possible. In some specific cases there are useful details it could provide, but there would usually be a myriad of other similar details that are irrelevant and if it included all those you'd not see the wood for the trees. EXPLAIN and query plan outputs in other DBs are “this is what I did” not really “why I did what I did”. To make the query planner bright enough to know what details would be useful to you, would probably pretty much require making it bright enough to do the optimisation job without you¹. [1] picking better indexes without hints, even creating those that are often needed, etc. MS's SQL Server tries to do this a bit with index suggestions. These are sometimes handy, but often at best for guidance². I've seen people blindly follow these suggestions to get a %-or-few gain from a small set of queries that could see orders of magnitude improvement with just a little tweaking elsewhere³, slowly amassing collections of indexes for very specific cases, sometimes multiple on the same key columns but each INCLUDEing a different mix of other data, that balloon their storage requirements⁴. [2] a nudge in the direction of “Mr Dev/DBA, you might want to think about how I'd avoid scanning this large object or performing many many thousands of seeks on this other one” [3] refactoring non-sargable predicates, index changes on other tables being referred to, getting rid of “SELECT *” particularly when referring to hideous views, ... [4] and having the knock-on effects of slowing insert/update activity & important admin functions (particularly backups)