Manoj <[EMAIL PROTECTED]> wrote on 07/14/2005 06:09:24 AM: > 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 = cd and dt1 > $one_year_ago" over > 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 >
First, I must comment that you double-posted. Bad form. Second, if you RTFM, you will quickly discover that MySQL only uses ONE (1) index per table participating in any query. Which index will be used (if one is used at all) is based on the probability of retrieving less than roughly thirty percent (30%) of the rows from any table. The 30% statistic is a rough figure as the exact threshold is determined at run time using parameters like table size, index cardinality, other columns in the SELECT clause, and what other actions need to be done to this table (like GROUP BY or ORDER BY). It is entirely conceivable that an index (even though several may be available) will not be used to get data from a particular table in a particular query. Please READ THE FINE MANUAL for more information about optimizing MySQL queries: http://dev.mysql.com/doc/mysql/en/mysql-optimization.html If English is not your primary language, some translations are also online. http://dev.mysql.com/doc/mysql/fr/mysql-optimization.html http://dev.mysql.com/doc/mysql/pt/mysql-optimization.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
