0

Assume we have table in MySql database that contains two columns A and B. And now I need to create a secondary index on them.

Question: is there any difference on how I choose order of columns in index(A|B or B|A) taking into consideration following statements:

  1. I will always perform selects filtering by both columns: WHERE A=sth AND B=sth so there is no difference on how to choose leftmost part of index.
  2. Values in column B are much more unique than values in column A.
Shadow
  • 33,525
  • 10
  • 51
  • 64
steavy
  • 1,483
  • 6
  • 19
  • 42
  • If the `WHERE` is using on `=` and one 'range', see https://stackoverflow.com/questions/50239658/higher-cardinality-column-first-in-an-index-when-involving-a-range – Rick James Jul 11 '18 at 17:24
  • A more important question when choosing `INDEX(A,B)` over `(B,A)` is whether you also have `WHERE A=sth` without `B` or `B` is used in a range. – Rick James Jul 11 '18 at 17:27
  • Be careful in reading the "dup" Q&A, there are some confusing and contradictory tips. The important thing to note: Cardinality is _not_ important. – Rick James Jul 11 '18 at 17:51
  • @Rick James If I'm going to do only `=` searches, does the cardinality matter like in "dup" q&a described? – steavy Jul 12 '18 at 21:36
  • Add the index, then show us `SHOW CREATE TABLE` and any queries using either or both of the columns. – Rick James Jul 12 '18 at 22:02

0 Answers0