summaryrefslogtreecommitdiffstats
path: root/support/schema
diff options
context:
space:
mode:
authorDan McGee <dan@archlinux.org>2009-04-11 23:40:57 +0200
committerLoui Chang <louipc.ist@gmail.com>2009-06-18 07:47:17 +0200
commite2b3bd1c37d91a747e8f774bbb91aad3dc466c68 (patch)
tree3293311340de8753996a61c0ff21d9611a25e84e /support/schema
parent135d61095796442db6dc77ca87b5baf49b6d7029 (diff)
downloadaur-e2b3bd1c37d91a747e8f774bbb91aad3dc466c68.tar.gz
aur-e2b3bd1c37d91a747e8f774bbb91aad3dc466c68.tar.xz
Add multicolumn indexes on votes and notifications tables
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@archlinux.org> Signed-off-by: Loui Chang <louipc.ist@gmail.com>
Diffstat (limited to 'support/schema')
-rw-r--r--support/schema/aur-schema.sql3
1 files changed, 2 insertions, 1 deletions
diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql
index 2a2a1a7a..8d5c427e 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
--