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

Question

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.

Answer 1

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.