Kernel always adds partition and dataareaid in front of any fields defined in an index.
For sure we know those should get included … and we can't "guess" the best place to include them meaning that having them near the beginning makes sense … but from a performance perspective is pretty bad.
Partition is deprecated and will always have only 1 possible value … as leading column is pretty bad.
DataareadID might have multiple value if the customer has multiple companies or might have 1. It's always going to be poorly selective.
While we can't know if those fields should come in 2nd, 3rd spot or later without analyzing the code, for sure we can safely say that the 1st column defined in the index will always be used otherwise we wouldn't be creating an index in the first place … (and if you really want to have an index with just partition and dataareaid just create that …).
Making the default be 1st column of the index > partition > dataareaid > all other columns.
While still not "perfect" it would still be a LOT better than what is happening today in most cases (which some fringe exceptions which will only revert to "just as bad as today" if that 1st field is say a Boolean). These index will generally perform the same or better, provide better stats and eliminate a lot of cases of "hidden scans"
Category: General