Composite Index Overhead

Question

I have many tables where I have indexes for foreign keys and clustered indexes that include these 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 will create clustered or spanning indexes. This results in indexing with overlapping columns.

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

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

Is it worth keeping idx_owner around? Is there any additional I / O idx_owner_status for using idx_owner_status , although MySQL uses only part of the index?

Edit: I'm only interested in InnoDB's way of behaving with respect to indexes.

Answer 1

Short answer Remove the shorter index.

Long Anwser Things to consider:

Drop it:

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

Do not drop it:

• A longer index is more cumbersome than a shorter one. Thus, it is less cached. Thus (in extreme situations) the use of more cumbersome, instead of shorter, can lead to more I / O. A case that exacerbates this is: INDEX(int, varchar255) .

It is very rare that the last element really overrides the other elements.

Bonus

A "covering" index is one that contains all the columns specified in the SELECT . For instance:

 SELECT status FROM tbl WHERE owner = 123; 

This only affects BTree for INDEX(owner, status) , which is noticeably faster than

 SELECT status, foo FROM tbl WHERE owner = 123; 

If you really need this query faster, replace both indexes with INDEX(owner, status, foo) .

PC in an additional key

Another tidbit ... In InnoDB, PRIMARY KEY columns are implicitly added to each secondary key. So, three examples are really

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

More discussions on my blogs on composite indexes and index cookbook .