List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:August 14 2007 1:41pm
Subject:Re: index, unique index question
View as plain text  


>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



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