Paul, What about the case where column A is a foreign key? In that case would you be forced to keep the index on column A or could you use the A,B index since A is the left most prefix?
Tripp --- Paul DuBois <[EMAIL PROTECTED]> wrote: > At 19:26 -0700 7/7/04, John Mistler wrote: > >I am not certain from the documentation whether it > is advisable to create a > >unique multi-column index on two columns that are > already individually > >indexed. The individual indexes I assume I need > for when I do a SELECT on > >those particular columns. The multi-column one I > need for the reasons > >discussed below. Any one know? > > If you have indexes on column A and column B, then > if you create > a multiple-column unique index on (A,B), you could > remove the index > on A. The reason for this is that MySQL can use a > leftmost prefix > of a multiple-column index as if you had an index on > just the leftmost > columns. > > In other words, an index on (A, B) can be use when > you search for combinations > of A and B, or when you search for just A. > > You cannot remove the index that you have on just B, > because B is not > a leftmost index of (A, B). > > http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html > > > > >Thanks, > > > >John > > > >on 7/7/04 2:21 PM, Joshua J. Kugler at > [EMAIL PROTECTED] wrote: > > > >> Certainly, it's called making a unique index on > the field(s) you > >>want to keep > >> unique. > >> > >> Hope that helps. > >> > >> j----- k----- > >> > >> On Wednesday 07 July 2004 12:48 pm, John Mistler > said something like: > >>> Is there a way to do an INSERT on a table only > if no row already exists > >>> with the same info for one or more of the > columns as the row to be > >>> inserted? That is, without using a method > outside SQL? > >>> > >>> Thanks, > >>> > >>> John > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > Paul DuBois, MySQL Documentation Team > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
