Do mysql composite indexes make some other indexes completely redundant?

Question

If I have an a composite index on (a, b) I understand that queries only concerned with 'a' will still use the composite index (but not queries concerned with 'b')

My question is whether there is any valid reason to have a single-column index on 'a' if I have the (a, b) index? What I've read has seemed vague as to whether the (a,b) index was a complete substitute for a, or merely a "better than nothing" index.

This assumes that I do filtering by both a and a,b. I have a table with way too many indexes that is hurting write performance and want to double check before dropping indexes that I'm only fairly sure are not doing any good.

Also, does this answer change depending on whether I am using InnoDb or MyISAM? The table concerned is MyISAM, but most of our tables are InnoDb.

Answer 1

Your (a,b) index will also handle queries involving only 'a' and there is no need for an index on (a) alone.

From the documentation:

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows.

For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

Answer 2

My question is whether there is any valid reason to have a single-column index on 'a' if I have the (a, b) index?

No, there is no reason to have an index on (a) if you have one on (a,b)