title: Partial multi-field index usage in MySQL
body: <p>I have a MyISAM table with almost 1 billion records, with say, three fields: a, b and c.</p>
  
  <p>The table has a btree multi-field index on columns a, b and c in that order. Analyzing the index shows that the cardinalities for the fields in that index are:</p>
  
  <p>a: 112 (int)</p>
  
  <p>b: 2694 (int)</p>
  
  <p>c: 936426795 (datetime)</p>
  
  <p>Which means that there are around 100 different values for a, around 20 different values for b, and for each combination of a and b, a whole lot of values of c.</p>
  
  <p>I want to perform a query over a specific value of a, and a range over c. Something like</p>
  
  <pre><code>select a, b, c from mytable where a=4 and c &gt;= "2011-01-01 00:00:00" and c &lt; "2011-01-02 00:00:00"
  </code></pre>
  
  <p>Getting the query explained shows me that it will indeed use the index, but I don't know if it will use only the first field of the index and then scan over the rest of the table, or if it will be smart enough to apply the third field index, for each value of b, which would be the same as executing 20 different queries, one for each different value of b.</p>
  
  <p>Anybody who knows the internal working of mysql indices can answer this question?</p>
  
  <p>Edit: I'm not asking whether or not I can have mysql to use the index over only a and c. I know how btrees work, and I know that you can only use it over a, a and b, or a and b and c. I would like to know if the mysql optimizer is smart enough to apply the index over all the values in b so it can use the a+b+c index, considering that the cardinality of b is extremely small.</p>
  
  <p>Consider an even simpler example. A table with two columns: a and b, and the index has cardinality 1 over a and 10000000 over b. Mysql should be smart enough to know that there's only one value of a, therefore this index is equivalent to an index only over b, and should use this index when performing queries only over b.</p>