Comments - Compound (Composite) Indexes
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
Is it worth mentioning that indices have a length constraint?
I don't know the maximum off-hand (1024?), but as I recall, compound indices require a space equal to the sum of the maximum length of each column in the index, plus some bytes of overhead.
Unless I know it's a query I'm going to do often, I avoid "covering indexes" because complex ones can quickly eat up the max index length, in which case, some of the columns (in longest order?) get quietly truncated.
Needless to say, truncated columns in the compound index could easily blow away any advantage there was to a covering index! In a large database (say, the New York City phone book), it would have to do a table scan of all the truncated columns, right?