Maria DB - Is there a point in indexing each uniq index cols separately?

We are running 10.2.44-MariaDB-log MariaDB Server. Right now I am creating tables with this structure

Primary('id')
UniqueIndex('colA', 'colB', 'colc');
Index('cola')
Index('colb')
Index('colc')

I was wondering, does it make sense to have those indexes for each column? Those columns can be legitimately queried on their own like Select cola or JOIN ON cola but does the server know to use the index on those situations or does it use the unique index since those cols are already in there?

I'll assume you are using InnoDB, the default storage engine.

The indexes on individual columns colb and colc may be used in a query searching on those columns. The three-column index cannot be used for such queries:

SELECT ... WHERE colb = ?

SELECT ... WHERE colc = ?

The index on cola is almost redundant with the unique index on three columns, but not quite redundant. The reason is that InnoDB secondary indexes implicitly have the primary key appended to the columns you name. So you really have indexes as if you had defined them as follows:

Primary(`id`)
UniqueIndex(`colA`, `colB`, `colc`) with extra `id`
Index(`cola`, `id`)
Index(`colb`, `id`)
Index(`colc`, `id`)

The id in the unique index isn't used for uniqueness, but it is present in the secondary index.

Now suppose you have a query like this:

SELECT ... WHERE cola = ? ORDER BY id

It would be able to use the index on cola, id and be guaranteed the sort order is a no-op because of the order of id in that index. So it avoids the need for a filesort.

But the unique index on cola, colb, colc is not in the same sort order, so the ORDER BY id would require a filesort.

StackExchange Database Administrators Q#317009, answer score: 5