Can index creation use existing indexes?

Question

I have individual indexes on, say, columns A, B and C. I'd like to create a composite index on the three columns A+B+C.

What impact will my existing indexes have on composite index creation? Will the database take advantage of them, are they irrelevant, or will they slow down the creation of my new composite index?

I'm using MySql 5.1.

EDIT: BTW the table has several million rows.

EDIT 2: thanks to tster for the suggestion: I tried this out on a much smaller table (admittedly just 20,000 rows) but even so the creation of a new composite index took noticeably longer when the individual indexes were already present.

Answer 1

Your existing indexes are not relevant to the creation of the new index. An index is a physical thing on disk and you're asking the database to create a brand new one which has an entirely different structure from the existing three.

(Caveat: I have no specific knowledge of MySQL.)

Answer 2

MySQL usually rebuilds the whole table when you add an index, so all the existing ones get rebuilt as well. This can be slow.

The only exception is adding an index using the InnoDB plugin, which does not.

As far as I know, it always does a full table scan when building an index, however it COULD do an index scan if you were adding an index which had the same (or a subset) of columns as another index. Such indexes are normally only useful if the columns are in a different order.

Using stock mysql, the more indexes you have, the slower it will be to make a new one, as it rebuilds existing indexes too.

With the plugin, I think it makes no difference.

Either way, if you're planning to add several indexes, you should do them all-at-once not one at a time.