List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 26 2001 6:10pm
Subject:Re: Not a lame question about index usage of MySQL
View as plain text  
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
	dnelson@stripped
Thread
How to use a database from Sun solaris to LinuxLegeard Luc26 Jan
  • Re: How to use a database from Sun solaris to LinuxRemco van den Berg26 Jan
  • Re: How to use a database from Sun solaris to LinuxArtem Koutchine26 Jan
  • Re: How to use a database from Sun solaris to LinuxMikel King26 Jan
  • Who is quicker: CHAR(*), VARCHAR(*) or TINYTEXT.Nick Kostirya26 Jan
  • Not a lame question about index usage of MySQLArtem Koutchine26 Jan
    • Re: Not a lame question about index usage of MySQLDan Nelson26 Jan
Re: Who is quicker: CHAR(*), VARCHAR(*) or TINYTEXT.Matthew Tedder26 Jan
  • RE: Who is quicker: CHAR(*), VARCHAR(*) or TINYTEXT.Carsten H. Pedersen27 Jan