Does MySQL use existing indexes on creating new indexes?

Question

I have a large table with millions of records.

Table `price`
------------
id 
product 
site 
value

The table is brand new, and there are no indexes created.

I then issued a request for new index creation with the following query:

CREATE INDEX ix_price_site_product_value_id ON price (site, product, value, id);

This took long long time, last time I was checking ran for 5000+ seconds, because of the machine.

I am wondering if I issue another index creation, will it use the existing index in the process calculation? If so in what form?

Next to run query 1:

CREATE INDEX ix_price_product_value_id ON price (product, value, id);

Next to run query 2:

CREATE INDEX ix_price_value_id ON price (value, id);

Answer 1

Having an existing index won't improve the speed of creating new indexes because creating an index requires a full scan.

To speed things up, instead of creating three indexes:

• (site, product, value, id)
• (product, value, id)
• (value, id)

you can reverse the column order and have just one index that can serve all three queries:

• (id, value, product, site)

This is because when MySQL looks for a suitable index it will also be able to use any prefix of an index.

Answer 2

If you're using MySQL version 5.1, and the InnoDB storage engine, you may want to use the InnoDB Plugin 1.0, which supports a new feature called Fast Index Creation. This allows the storage engine to create indexes without copying the contents of the entire table.

Overview of the InnoDB Plugin:

Starting with version 5.1, MySQL AB has promoted the idea of a “pluggable” storage engine architecture, which permits multiple storage engines to be added to MySQL. Currently, however, most users have accessed only those storage engines that are distributed by MySQL AB, and are linked into the binary (executable) releases.

Since 2001, MySQL AB has distributed the InnoDB transactional storage engine with its releases (both source and binary). Beginning with MySQL version 5.1, it is possible for users to swap out one version of InnoDB and use another.

Source: Introduction to the InnoDB Plugin

Overview of Fast Index Creation:

In MySQL versions up to 5.0, adding or dropping an index on a table with existing data can be very slow if the table has many rows. The CREATE INDEX and DROP INDEX commands work by creating a new, empty table defined with the requested set of indexes. It then copies the existing rows to the new table one-by-one, updating the indexes as it goes. Inserting entries into the indexes in this fashion, where the key values are not sorted, requires random access to the index nodes, and is far from optimal. After all rows from the original table are copied, the old table is dropped and the copy is renamed with the name of the original table.

Beginning with version 5.1, MySQL allows a storage engine to create or drop indexes without copying the contents of the entire table. The standard built-in InnoDB in MySQL version 5.1, however, does not take advantage of this capability. With the InnoDB Plugin, however, users can in most cases add and drop indexes much more efficiently than with prior releases.

...

Changing the clustered index requires copying the data, even with the InnoDB Plugin. However, adding or dropping a secondary index with the InnoDB Plugin is much faster, since it does not involve copying the data.

Source: Overview of Fast Index Creation

Answer 3

I am wondering if I issue another index creation, will it use the existing index in the process calculation? If so in what form?

No, it won't.

Theoretically, an index on (site, product, value, id) has everything required to build an index on any subset of these fields (including the indices on (product, value, id) and (value, id)).

However, building an index from a secondary index is not supported.

First, MySQL does not support fast full index scan (that is scanning an index in physical order rather than logical), thus making an index access path more expensive than the table read. This is not a problem for InnoDB, since the table itself is always clustered.

Second, the record orders in these indexes are completely different so the records need to be sorted anyway.

However, the main problem with the index creation speed in MySQL is that it generates the order on site (just inserting the records one by one into a B-Tree) instead of using a presorted source. As @Daniel mentioned, fast index creation solves this problem. It is available as a plugin for 5.1 and comes preinstalled in 5.5.