Hello.
> it be "optimal" to use primary key + non-unique key combination to narrow > down the search? According to: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html "Suppose that you issue the following SELECT statement: mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows." The same, I think, could be applied to '>' expressions. That means optimize could use only one index (or several leftmost prefixes from one composite index). So, probably, having one composite index is better choice. Manoj <[EMAIL PROTECTED]> wrote: > Greetings, > I am trying to get a feel of how MySQL would handle certain types of > situation, mainly concerning the usage of indices. > > Say I have two exactly identical table structures namely table A & B. > > For table A, I just have one composite primary key on (Code, Date1 & > Date2). > > For table B, I just have composite primary key on (Code, Date1 & Date2) > and two non-unique keys on Date1 & Date2. > > Question is, Will I find any performance improvement with table B, If > all my queries start with Code field ? > > In other words : Will there be any noticiable difference in speed for > query "Select * from tableB where code =3D cd and dt1 > $one_year_ago" ove= > r > the same query on tableA ? > > Kindly note that we might have 1000 or more records for each code. When > I tried to do an explain, it suggested in both the cases (on tableA & on > tableB) that It will use composite primary key (mainly because left most > field in the primary index is avaliable)....suggesting that the non-unique > indices are pretty much a waste of space...Is that really the case? Woudln'= > t > it be "optimal" to use primary key + non-unique key combination to narrow > down the search? > > I would greatly appreciate your insight in this issue. > > TIA > > Manoj > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
