The documentation is a bit unclear about indexes on multiple columns in a derived table. The Derived Tables page says:
add indexes on customer_id and first_order_date, like this:…
indexes: [customer_id, first_order_date]
On the other hand, the
indexes page says:
with an index on customer_id and date:…
indexes: [customer_id, date]
Note the difference! According to the Derived Tables page, there are indexes on the two columns, one on each. According to the
indexes page, there’s a single index on the two columns. This is a significant difference (in MySQL at least); specifically, a single multicolumn index serves as an index not only on the combination of columns but also on any initial subset of that combination, but not on any other subset. Therefore, if this is a multicolumn index, then the order columns are specified in matters. (Again, this is true for MySQL. I don’t know what other dialects it might be true in.)
So my questions are:
- Which documentation page is correct: does the
indexes: [foo, bar]syntax create a single multicolumn index or two indexes?
- Can the incorrect documentation page be emended?
indexes: [foo, bar]creates a multicolumn index, then is there a way to create separate indexes on various columns?
- If there isn’t a way, then can there be, please? (This is an important feature request, because a multicolumn index, as I mentioned, is useful only when using an initial subset of its columns. Again, this is true of MySQL.)
indexes: [foo, bar]creates separate indexes, then is there a way to create a multicolumn index?
- If there isn’t a way, then can there be, please?
I propose a fairly mnemonic syntax for specifying a multicolumn index on
bar in that order, a multicolumn index on
quux in that order, and separate indexes on
mumble, as follows:
indexes: [foo, bar], [baz, quux], xyzzy, mumble