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 table?
The best solution is to create a compound index on (col1, col2).
--
Dan Nelson
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
