title: db2 10.5 multi-column index explanation
body: <p>My first time working with indexes in database and so far I've learn that if you have a multi-column index such as index('col1', 'col2', 'col3'), and if you do a query that uses where col2='col2' and col3='col3', that index would not be use.</p>
  
  <p>I also learn that if a column is very low selectivity column. Indexing is useless.
  However, from my test, it seems none of the above is true at all. Can someone explain more on this?</p>
  
  <p>I have a table with more than 16 million records. Let's say claimID is the primary key, then there're a historynumber column that only have 3 distinct values (1,2,3), and a last column with storeNumber that has about 1 million distinct values.</p>
  
  <p>I have an index for claimID alone, another index(historynumber, claimID), and other index with index(historynumber, storeNumber), and finally index(storeNumber, historynumber). </p>
  
  <p>My guess was that if I do:</p>
  
  <pre><code>select * from my_table where claimId='123456' and historynumber = 1 
  </code></pre>
  
  <p>would be much faster than</p>
  
  <pre><code>select * from my_table where historynumber = 1 and claimId = '123456'
  </code></pre>
  
  <p>However, the 2 have exactly the same performance (instant). So I thought the primary key index can work on any column order. Therefore, I tried the same thing but on historynumber and storeNumber instead. The result is exactly the same. Then I start trying out on columns that has no indexes and of course the result is the same also.</p>
  
  <p>Finally, I do a </p>
  
  <pre><code>select * from my_table where historynumber = 1 
  </code></pre>
  
  <p>and the query takes so long I had to cancel it.</p>
  
  <p>So my conclusion is that the column order in where clause is completely useless, and so is the column order in the index definition since it seems like the database is smart enough to tell which column is the highest selectivity column.</p>
  
  <p>Could someone give me an example that could prove otherwise?</p>