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

[Dan McGee]

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 <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
>  --
> --
> 1.6.2.2
>
>