[aur-dev] [PATCH 1/2] Add multicolumn indexes on votes and notifications tables

[Dan McGee]

On Thu, Apr 16, 2009 at 2:20 PM, Loui Chang <louipc.ist at gmail.com> wrote:
> On Thu, Apr 16, 2009 at 11:37:35AM -0500, Aaron Griffin wrote:
>> On Thu, Apr 16, 2009 at 11:30 AM, Loui Chang <louipc.ist at gmail.com> wrote:
>> > On Wed, Apr 15, 2009 at 06:34:28PM -0500, Dan McGee wrote:
>> >> Loui- I noticed you applied the patch I sent a day or two after this
>> >> one but didn't do anything with this one and its pair (explicit join
>> >> usage in query). Just wanted to make sure it didn't get lost.
>> >
>> > Yeah. I definitely still have my eye on this.
>> > Actually I was wondering how to add these indexes to an existing DB,
>> > but I haven't had the chance to look into it.
>>
>> Just running the CREATE statements should work fine, as Dan said they
>> do not exist already.
>>
>> If they did exist, that's another story :)
>
> Hmm. I ran into a problem where there are some duplicate entries.
> There may be a bug in the notification.
>
> mysql> CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PkgID);
> ERROR 1062 (23000): Duplicate entry '631-1323' for key 'NotifyUserIDPkgID'

Yeah, bad data is getting in there somehow. Here is how I took care of that:
alter ignore table CommentNotify add unique index NotifyUserIDPkgID
(UserID, PkgID);

The "ignore" clause is the key.

-Dan