Shall I define index (A) and index (B), or index (A, B), or both?

Question

In my table I have two closely related columns A and B. What possible considerations shall I made to decide whether to create:

• index(A) and index(B),
• index(A, B),
• both of the above?

How does this change if I:

1. use only queries like where A = 5 and B = 10 (and never like where A = 5),
2. also use queries like where A > 3 and A < 10 and B > 12 and B < 20,
3. often use order by (A, B),
4. often use group by (A, B)?

Note: I intentionally haven't provided more details about my particular case as I want a general answer that will also serve to others. I use mysql, but if you give more general answer that covers SQL in general that would be great.

Answer 1

Ok, when you have an index(A,B), MySQL can also use it as an index(A). Declaring AB and A has no sense. Declaring AB and B does, however

So you have these choices:

1. Index(A,B)
2. Index(A,B) and Index(B)
3. Index(A) and Index(B)

Answer 2

If you always use both columns (or the first one of the index) in your WHERE / GROUP BY / ORDER BY you should use index(A,B).

The second one in the index (in this case B) cannot be used separately, but the first one can (only the first one in any case).

So if you never use B on its own, then index(A,B) should be sufficient. If you never use A on its own, but you do use B, then do index(B,A). If you do use both separately, but mostly together, then add the other index separately.