List:General Discussion« Previous MessageNext Message »
From:James Tu Date:August 13 2007 10:33pm
Subject:index, unique index question
View as plain text  
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


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