0

I am using MySQL as my RDBMS.

But I think it must be applicable to other relational DBs.

I have a table Z, where I have 5 columns: a, b, c, d, e.

Columns a, b, c comprise a composite primary key.

Now, when it comes down to querying in the WHERE clause there will be times when I will be fetching data based on the values of columns a, b, c. But only one column out of 3 will be set.

Do I need to create 3 indices against these columns?

Follow-up question: what if I need to query my table knowing values for 2 columns out of 3? Will the creation of an additional 3 indices help to speed up my queries? (a, b) (a, c) (b, c)

Please advise.

Ihor M.
  • 2,728
  • 3
  • 44
  • 70
  • Provide precise query text. Indices are NOT created "in general". – Akina Nov 24 '20 at 20:01
  • You can search on A based on the composite primary index. B & C will possibly require additional indexes, but you would have to test to see if those indexes make a difference. B, C would use the B index. A, B or A, C would use the composite primary index. – Gilbert Le Blanc Nov 24 '20 at 20:05
  • 1
    A composite index can be used when searching/ordering any prefix of the key, so `(a, b, c)` will also act as an index on `(a, b)` and `(a)`. – Barmar Nov 24 '20 at 20:07

2 Answers2

3

...will be fetching data based on the values of columns a, b, c. But only one column out of 3 will be set.

If that's the case you'll need three indexes:

  • If a is set your primary key index (a, b, c) will suffice. You don't need to create an extra index for this case.
  • If b is set you'll need the index (b) for this query to be fast.
  • If c is set you'll need the index (c) for this query to be fast.

The index (a, b, c) is not useful when a is null. Remember, null is not a value.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

Short answer: yes.

INDEX (a, b, c) 
-- creates 1 index of unique combinations of a&b&c, not unlike CONCAT(a, b,c)

INDEX (a),
INDEX (b),
INDEX (c)
-- creates 3 indexes of unique values for all a, b, c separately

INDEX (a, b),
INDEX (c)
-- creates 2 indexes:
--  1st for a&b unique values
--  2nd for c unique values

Follow up: with WHERE a = '...' AND b = '...', searching thru INDEX(a, b) will be faster than searching thru INDEX(a), INDEX(b). However, if a or b values are (at least mostly) unique, performance increase will not be significant.

When debugging index performance, always start witch checking your indexes' cardinality and later your queries' index usage with EXPLAIN SELECT.

ΔO 'delta zero'
  • 3,506
  • 1
  • 19
  • 31