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

newer
[aur-dev] [patch] Advanced search...

Dan McGee

11 Apr 2009 11 Apr '09

11:40 p.m.

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

Show replies by date

Dan McGee

11 Apr 11 Apr

11:40 p.m.

New subject: [aur-dev] [PATCH 2/2] Specify explicit joins for package search

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

Dan McGee

16 Apr 16 Apr

1:34 a.m.

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

Loui Chang

6:30 p.m.

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

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.

Aaron Griffin

6:37 p.m.

On Thu, Apr 16, 2009 at 11:30 AM, Loui Chang 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 :)

Dan McGee

8:56 p.m.

On Thu, Apr 16, 2009 at 11:37 AM, Aaron Griffin wrote:

...

On Thu, Apr 16, 2009 at 11:30 AM, Loui Chang 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

Aaron Griffin

9:42 p.m.

On Thu, Apr 16, 2009 at 1:56 PM, Dan McGee wrote:

...

On Thu, Apr 16, 2009 at 11:37 AM, Aaron Griffin wrote:

...
On Thu, Apr 16, 2009 at 11:30 AM, Loui Chang 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.

Loui Chang

9:20 p.m.

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

On Thu, Apr 16, 2009 at 11:37:35AM -0500, Aaron Griffin wrote:

...

On Thu, Apr 16, 2009 at 11:30 AM, Loui Chang 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'

Dan McGee

17 Apr 17 Apr

4:08 a.m.

On Thu, Apr 16, 2009 at 2:20 PM, Loui Chang 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 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

Loui Chang

18 Jun 18 Jun

7:58 a.m.

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

On Thu 16 Apr 2009 21:08 -0500, Dan McGee wrote:

...

On Thu, Apr 16, 2009 at 2:20 PM, Loui Chang 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 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.

5432

Age (days ago)

5500

Last active (days ago)

List overview

Download

9 comments

4 participants

participants (4)

Aaron Griffin
Dan McGee
Dan McGee
Loui Chang