[aur-dev] [PATCH 1/2] 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> --- 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
Refactor the query to use explicit LEFT JOINs, which appears to be handled by MySQL in a saner fashion than the previous implicit join syntax. This is part two in a slight fixup for observed slow queries in the production environment. With the new indexes and this fixup, a particular iteration of this query will examine only 13346 rows instead of 272060. Signed-off-by: Dan McGee <dan@archlinux.org> --- web/lib/pkgfuncs.inc | 12 ++++++------ 1 files changed, 6 insertions(+), 6 deletions(-) diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index 6ce9615..1c467ba 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -425,19 +425,19 @@ function pkg_search_page($SID="") { Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate - FROM PackageCategories, PackageLocations, Packages + FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) "; - if ($SID) { $q .= "LEFT JOIN PackageVotes ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = $myuid) LEFT JOIN CommentNotify ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = $myuid) "; } - $q .= "WHERE - Packages.CategoryID = PackageCategories.ID - AND Packages.LocationID = PackageLocations.ID - AND Packages.DummyPkg = 0 "; + $q .= "LEFT JOIN PackageCategories + ON (Packages.CategoryID = PackageCategories.ID) + LEFT JOIN PackageLocations + ON (Packages.LocationID = PackageLocations.ID) + WHERE Packages.DummyPkg = 0 "; // TODO: possibly do string matching on category and // location to make request variable values more sensible -- 1.6.2.2
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@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@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
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.
On Thu, Apr 16, 2009 at 11:30 AM, Loui Chang <louipc.ist@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 :)
On Thu, Apr 16, 2009 at 11:37 AM, Aaron Griffin <aaronmgriffin@gmail.com> wrote:
On Thu, Apr 16, 2009 at 11:30 AM, Loui Chang <louipc.ist@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 :)
Yep, just run the CREATE statement. As I told you over IM, I added these indexes on the production DB already so that isn't an issue, but you can of course create them locally. If an index with that name already exists, it is just a "DROP INDEX <name>;" away from not being a problem. -Dan
On Thu, Apr 16, 2009 at 1:56 PM, Dan McGee <dpmcgee@gmail.com> wrote:
On Thu, Apr 16, 2009 at 11:37 AM, Aaron Griffin <aaronmgriffin@gmail.com> wrote:
On Thu, Apr 16, 2009 at 11:30 AM, Loui Chang <louipc.ist@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 :)
Yep, just run the CREATE statement. As I told you over IM, I added these indexes on the production DB already so that isn't an issue, but you can of course create them locally.
If an index with that name already exists, it is just a "DROP INDEX <name>;" away from not being a problem.
Way off topic... some DBs support "CREATE OR REPLACE ...", which is nice for cases like this.
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@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'
On Thu, Apr 16, 2009 at 2:20 PM, Loui Chang <louipc.ist@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@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
On Thu 16 Apr 2009 21:08 -0500, Dan McGee wrote:
On Thu, Apr 16, 2009 at 2:20 PM, Loui Chang <louipc.ist@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@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.
Hah, well I pushed this finally.
participants (4)
-
Aaron Griffin
-
Dan McGee
-
Dan McGee
-
Loui Chang