List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:August 14 2007 1:45pm
Subject:Re: index, unique index question
View as plain text  
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 <m.tonies@stripped> 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:kristian@stripped]
> 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 <anandkl@stripped>:
>
> > 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 <jtu@stripped> 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/mysql?unsub=1
> >>
> >>
> >
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

Thread
index, unique index questionJames Tu14 Aug
  • Re: index, unique index questionAnanda Kumar14 Aug
    • Re: index, unique index questionKristian Myllymäki14 Aug
      • RE: index, unique index questionNoah Dowd14 Aug
    • Re: index, unique index questionMartijn Tonies14 Aug
      • Re: index, unique index questionAnanda Kumar14 Aug