Which index option should I choose?

I have a SQL table with these columns (id, A, B, C, D, E).

I usually query on (A and C) and (B 开发者_如何学运维and C) so I decide to have index on these 3. There are some options for this:

• Index for A,B,C separately
• Index for two pairs (A,C) and (B,C)
• Index for (A,B,C)

Which option should I choose? Database size may be an issue but let assume it's not a big deal right now.

---

Build an index on the two pairs. With most indexes (although this may vary between types and databases), an index on A,B is uses as well if you query only A, but A,B is not used if you query only B. So make sure the first columns in your indexes are the columns you query for. If you query A,B and A,C, but not B or C alone, you only need the indexes on those two pairs.

---

if storage/space utilization is important, than add two single column indexes, each on A and B.

if performance of these queries is more critical, then add two composite indexes, one on (A,C) and the other on (B,C).

as someone has already commented, these suggestions are educated guesses. it is a good idea to experiment with a variety of options to find the optimal indexing strategy.