Overhead of Composite Indexes

Question

I have many tables where I have indexes on foreign keys, and clustered indexes which include those foreign keys. For example, I have a table like the following:

TABLE: Item
------------------------
id       PRIMARY KEY
owner    FOREIGN KEY
status

... many more columns

MySQL generates indexes for primary and foreign keys, but sometimes, I want to improve query performance so I'll create clustered or covering indexes. This leads to have indexes with overlapping columns.

INDEXES ON: Item
------------------------
idx_owner (owner)
idx_owner_status (owner, status)

If I dropped idx_owner, future queries that would normally use idx_owner would just use idx_owner_status since it has owner as the first column in the index.

Is it worth keeping idx_owner around? Is there an additional I/O overhead to use idx_owner_status even though MySQL only uses part of the index?

Edit: I am really only interested in the way InnoDB behaves regarding indexes.

Answer 1

Short Answer Drop the shorter index.

Long Anwser Things to consider:

Drop it:

• Each INDEX is a separate BTree that resides on disk, so it takes space.
• Each INDEX is updated (sooner or later) when you INSERT a new row or an UPDATE modifies an indexed column. This takes some CPU and I/O and buffer_pool space for the 'change buffer'.
• Any functional use (as opposed to performance) for the shorter index can be performed by the longer one.

Don't drop it:

• The longer index is bulkier than the shorter one. So it is less cacheable. So (in extreme situations) using the bulkier one in place of the shorter one could cause more I/O. A case that aggravates this: INDEX(int, varchar255).

It is very rare that the last item really overrides the other items.

Bonus

A "covering" index is one that contains all the columns mentioned in a SELECT. For example:

SELECT status FROM tbl WHERE owner = 123;

This will touch only the BTree for INDEX(owner, status), thereby being noticeably faster than

SELECT status, foo FROM tbl WHERE owner = 123;

If you really need that query to be faster, then replace both of your indexes with INDEX(owner, status, foo).

PK in Secondary key

One more tidbit... In InnoDB, the columns of the PRIMARY KEY are implicitly appended to every secondary key. So, the three examples are really

INDEX(owner, id)
INDEX(owner, status, id)
INDEX(owner, status, foo, id)

More discussion in my blogs on composite indexes and index cookbook.