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.
-Dan On Sat, Apr 11, 2009 at 4:40 PM, Dan McGee <[email protected]> wrote: > To put a long story short, when we do joins on these tables in our > pkg_search_page() function, we always join on both the user ID and package > ID columns. By creating multicolumn indices, we can always get the exact row > we are looking for in the table. > > The benefits of adding a unique index should also speak for themselves, as > we previously did not have this on either of these tables. > > This is part one of a two-part series to address the fact that this query > was often showing up in our slow query logs. > > Signed-off-by: Dan McGee <[email protected]> > --- > support/schema/aur-schema.sql | 3 ++- > 1 files changed, 2 insertions(+), 1 deletions(-) > > diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql > index 2a2a1a7..8d5c427 100644 > --- a/support/schema/aur-schema.sql > +++ b/support/schema/aur-schema.sql > @@ -168,7 +168,7 @@ CREATE TABLE PackageVotes ( > FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE, > FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE > ); > - > +CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, > PackageID); > > -- The individual files and their file system location. > -- > @@ -206,6 +206,7 @@ CREATE TABLE CommentNotify ( > FOREIGN KEY (PkgID) REFERENCES Packages(ID) ON DELETE CASCADE, > FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE > ); > +CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PkgID); > > -- Vote information > -- > -- > 1.6.2.2 > >
