[aur-dev] [PATCH 1/2] Add multicolumn indexes on votes and notifications tables
dan at archlinux.org
Wed Apr 15 19:34:28 EDT 2009
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.
On Sat, Apr 11, 2009 at 4:40 PM, Dan McGee <dan at archlinux.org> 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 <dan at archlinux.org>
> 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
More information about the aur-dev