In the last episode (Jan 26), Artem Koutchine said:
> The manual says that if two seprate single-column indexes exist on
> two different colums (col1, col2) and someone issues a query:
> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2
> optimizer will choose the index which matches the least records and
> use it.
> This is it. Manual does not say what happens nexts. Assuming exactly
> what has been said abouve we have a terrible situation. For example
> if tbl_name has 100,000 records in col1 and for each of the values we
> have 100,000 values in col2. In this case MySQL would have too look
> through thousands of records to get the results if it does not apply
> another index after using the first one.
> So, my question is: Are things so bad? MySQL cannot apply more than
> one index per query per table?
Most database products only allow one B-tree index per table in a
query. Using two indexes is a waste of disk I/O. Why look up the
value in the second index when you can simply look it up directly in
The best solution is to create a compound index on (col1, col2).