CODEWITHSUNDEEP
mysql
ktm5124
ktm5124

Reputation: 12123

Is a multi-column index slower than a single-column index?

I have a primary key on (A, B) where A is an INT and B is an INT. Would queries searching on A run faster if I had an index on A instead?

I understand the leftmost prefix rule, but I'm curious if a multi-column key/index performs worse than a single-column key/index due to the key being longer.

Upvotes: 7

Views: 1461

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You have a situation where the composite key has two components. The first is 4 bytes and the second 4 bytes. The total key is 8 bytes.

A primary key index is clustered, meaning that the "leaf"s of the b-tree are the actual records themselves. A clustered index is going to be faster to access than other types of indexes.

One consideration in the performance of an index is the size of the key (as well as additional columns being kept in the index). An index with a 4-byte key is going to be smaller than an index with an 8-byte key. This means less disk usage and less storage in memory. However, the gains here might be pretty small. After all, a million rows in the table would correspond to at most a 10-20 million bytes (indexes have additional overheads in them).

Another consideration is the performance of data modification steps. In a clustered index, inserting/modifying a key value in the middle of a table requires re-writing the records themselves. However, you question does not seem to be address data modification.

If you have already defined the primary key index, then adding another index is additional overhead for the system. You might find that both indexes are occupying memory, so instead of saving space you are actually adding to it.

Ultimately, the answer to this type of rather arcane question is to do some timing tests. If the B column were much, much larger than the A component, I could possibly see some gains. For queries that only use A, I could possibly see some gains. However, my guess is that such gains would be quite minimal.

Upvotes: 1

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26709

In some cases it may perform worse - if the rest of the columns are large, for example: A: int, B: varchar(128), C: text index on A will perform better than index on A,B,C

In most cases it perform the same; in your case you have 4 vs 8 bytes, so overhead of having a second index is not worth doing it.

Keep in mind that primary key performs better than a secondary index, especially if the storage engine is InnoDB (the primary key is a clustering index) and it's not a covering query (it have to access the table to load data not stored in the index)

Actually in InnoDB all secondary indexes contain the primary key, so they are larger than the PK by default.

Upvotes: 1

Related Questions