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 ="
(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
...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.
(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?