very true, to have unique constraint on both columns, he needs to create a composite index using both columns.
regards anandkl On 8/14/07, Martijn Tonies <[EMAIL PROTECTED]> wrote: > > > > > >Of course, since James said he will never search for a record matching > receiver_ID AND sender_ID, it would be more efficient >to simply create > one > index for each of the columns. > > Then again, his question isn't really about indices (to speed up > searches and what not), but about constraints, which, from what > I read, he needs. > > A compound "unique constraint" is what he needs. > > Martijn Tonies > Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle > & > MS SQL Server > Upscene Productions > http://www.upscene.com > My thoughts: > http://blog.upscene.com/martijn/ > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > > -Noah > > > -----Original Message----- > From: Kristian Myllymäki [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 14, 2007 3:50 AM > To: Ananda Kumar > Cc: James Tu; MySQL List > Subject: Re: index, unique index question > > > A composite index on both columns may be used by queries involving > either both columns, or the first column in the index. > > http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html > > So, an index on (receiver_id, sender_id) may be used by predicates on > both columns or receiver_id alone, but never sender_id alone. (Or I > should never say never, since mysql could in the future implement an > index scan on the secondary column if the first has very few distinct > values). > > Since James only uses the predicates alone and never combined, I would > also suggest a secondary index on (sender_id). > > unique index (receiver_id, sender_id) > index (sender_id) > > /Kristian > > Quoting Ananda Kumar <[EMAIL PROTECTED]>: > > > Hi James, > > Since your queries have both receiver_id and sender_id in the where > > condition and u want this to be unique, just create one combined unique > > index on both these columns. > > > > Do this at db level will give you much better options, performance, > rather > > than doing at code level, which might involve quite bit of coding and > will > > slow down the performance of the app. > > > > If you create individual index and combined index, there will be huge > > performance degradation as there would be unnecessary index over heads. > > > > regards > > anandkl > > > > > > On 8/14/07, James Tu <[EMAIL PROTECTED]> wrote: > >> > >> I have a table that has a Primary key using the 'id' column. > >> The table also has a 'receiver_id' and a 'sender_id'. > >> > >> I have queries that will use > >> (1) "WHERE receiver_id =" > >> or > >> (2) "WHERE sender_id=" > >> but never "WHERE receiver_id='###' AND sender_id='###'" > >> > >> Also, I want the receiver_id/sender_id pair to be unique. The reason > >> I want this unique key is so that I can issue a > >> 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. > >> > >> > >> What's the best approach to create indices in this case? > >> (A) Create an index on 'receiver_id' and also create an index on > >> 'sender_id' > >> ...and enforce the uniqueness of receiver_id and sender_id in > >> code...first do a query to see if it's there then either do an UPDATE > >> or and INSERT. > >> or > >> (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? > >> > >> When I create both (A) and (B), phpmyadmin gives me a warning > >> indicating that more than one index is created on 'receiver_id.' > >> > >> > >> Any suggestions on how to handle this situation? > >> -James > >> > >> > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >> > >> > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
