MySQL: Composite Index vs Multiple Indices (Leftmost Index Prefixes)

Question

We have a table that is currently using a composite (i.e. multi-column) index.

Let's say

PRIMARY KEY(A, B)

Of course we can rapidly search based on A alone (Leftmost Index Prefix) and if we want to efficiently search based on B alone, we need to create a separate index for B.

My question is that if I am doing:

PRIMARY KEY (B)

is there any value in retaining

PRIMARY KEY (A,B)

In other words will there be any advantage retaining

PRIMARY KEY (A,B)

if I have

PRIMARY KEY (A)

and

PRIMARY KEY (B)

You can't have more than one primary key constraint in a table. The purpose of the primary key is to define the columns you can use to select any row individually. — Bill Karwin, Jun 12 '18 at 00:18
Possible duplicate of [Two single-column indexes vs one two-column index in MySQL?](https://stackoverflow.com/questions/2349817/two-single-column-indexes-vs-one-two-column-index-in-mysql) — revo, Jun 12 '18 at 00:18

score 1 · Answer 1 · answered Jun 12 '18 at 03:14

You are missing a key point about PRIMARY KEY -- it is by definition (at least in MySQL), UNIQUE. And do not have more columns than are needed to make the PK unique.

If B, aloneis unique, then havePRIMARY KEY(B)` without any other columns in the PK definition.

If A is also unique, then do

PRIMARY KEY(B),
UNIQUE(A)

or swap them.

For a longer discussion of creating indexes, see my cookbook.

If it takes both columns to be "unique", then you may need

PRIMARY KEY(A, B),
INDEX(B)

or

PRIMARY KEY(B, A),
INDEX(A)

Until you have the SELECTs, it is hard to know what indexes to create.

score 0 · Answer 2 · answered Jun 12 '18 at 00:44

You can't have multiple primary keys, so I'm going to assume you're really asking about having an ordinary index.

If you have an index on (A, B), it will be used for queries that use both columns, like:

WHERE A = 1 AND B = 2

as well as queries that just use A:

WHERE A = 3

But if you have a query that just uses B, e.g.

WHERE B = 4

it will not be able to use the index at all. If you need to optimize these queries, you should also have an index on B. So you might have:

UNIQUE KEY (A, B)
INDEX (B)

MySQL: Composite Index vs Multiple Indices (Leftmost Index Prefixes)

2 Answers2