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

I have a primary key on (A, B) , where A is INT and B is INT . Would query search in A be faster if index A were instead?

I understand the leftmost prefix rule, but I'm curious if a multi-column key / index is worse than a single-column key / index because the key is larger.

+6
source share
2 answers

In some cases, this may get worse - if the remaining columns are large, for example: A: int, B: varchar (128), C: text index on A will work better than index on A, B, C

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

Keep in mind that the primary key works better than the secondary index, especially if the storage engine is InnoDB (the primary key is the clustering index) and it is not a closing query (it needs to access the table to load data that has not been stored in index)

In fact, in InnoDB, all secondary indexes contain a primary key, so by default they are larger than PK.

+1
source

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

The primary key index is clustered, which means that the "sheets" of the b-tree are the actual records themselves. A clustered index will be faster to access than other types of indexes.

One measure of index performance is the key size (as well as additional columns stored in the index). An index with a 4-byte key will be less than an index with an 8-byte key. This means less disk usage and less memory. However, the benefits here can be quite small. In the end, a million rows in the table will correspond to no more than 10-20 million bytes (indexes have additional overhead in them).

Another consideration is the implementation of data modification steps. In a clustered index, inserting / changing a key value in the middle of a table requires re-writing the records themselves. However, you doubt that this is not a change in address data.

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

Ultimately, the answer to this rather cryptic question is to do some temporary tests. If column B was much larger than component A , I could see some winnings. For queries that use only A , I might see some winnings. However, I assume that such benefits will be minimal.

+1
source

Source: https://habr.com/ru/post/951395/


All Articles