With just about 7.000 users, the following query in og_user_roles_getgid() is causing huge delays for us (about 4-500 msec):

$result = db_query("SELECT na.gid from {node_access} na INNER JOIN {og_uid} ogu ON na.gid = ogu.nid WHERE na.realm = 'og_subscriber' AND ogu.uid = %d AND (na.nid = %d OR na.gid = %d)", $uid, $nid, $nid); // modified to check for either the node or group ID in node_access

EXPLAIN tells us why (note the size of the result set - approx. 75.000 rows):

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	na 	index 	PRIMARY 	PRIMARY 	775 	NULL 	74475 	Using where; Using index
1 	SIMPLE 	ogu 	eq_ref 	PRIMARY 	PRIMARY 	8 	iofc_drupal_protobuild.na.gid,const 	1 	Using where; Using index

We then added a new index to the {node_access} table in an attempt to speed up data access:

ALTER TABLE node_access ADD INDEX og_subscriber (realm(30), nid, gid)

However, since there are too much matching rows, there is almost no visible effect (still 30.184 rows to examine):

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	na 	ref 	PRIMARY,og_subscriber 	og_subscriber 	92 	const 	30184 	Using where; Using index
1 	SIMPLE 	ogu 	eq_ref 	PRIMARY 	PRIMARY 	8 	iofc_drupal_protobuild.na.gid,const 	1 	Using where; Using index

I then tried to rewrite the query in order to split it into two simple queries vs. one complex:

$result = db_query("SELECT gid FROM {node_access} WHERE realm = 'og_subscriber' AND nid = %d AND (gid IN (SELECT nid FROM {og_uid} WHERE uid = %d) OR gid = %n)", $nid, $uid, $nid);

By avoiding a direct INNER JOIN, the resulting queries make proper use of indexes:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	node_access 	ref 	PRIMARY 	PRIMARY 	4 	const 	1 	Using where; Using index
2 	DEPENDENT SUBQUERY 	og_uid 	unique_subquery 	PRIMARY 	PRIMARY 	8 	func,const 	1 	Using index; Using where

However, the question remains whether the new query is 100% the same as the old one.

CommentFileSizeAuthor
#2 og_user_roles-getgid.patch1.86 KBsmk-ka

Comments

somebodysysop’s picture

somebodysysop commented

How about two separate queries this way:

  if ($gid == 0) {
    $result = db_query("SELECT na.gid from {node_access} na INNER JOIN {og_uid} ogu ON na.gid = ogu.nid WHERE na.realm = 'og_subscriber' AND ogu.uid = %d AND (na.nid = %d)", $uid, $nid, $nid); // modified to check for either the node ID in node_access
    while ($t = db_fetch_object($result)) {
      $gid = $t->gid;
    }
  }

and

  if ($gid == 0) {
    $result = db_query("SELECT na.gid from {node_access} na INNER JOIN {og_uid} ogu ON na.gid = ogu.nid WHERE na.realm = 'og_subscriber' AND ogu.uid = %d AND (na.gid = %d)", $uid, $nid, $nid); // modified to check for group ID in node_access
    while ($t = db_fetch_object($result)) {
      $gid = $t->gid;
    }
  }
smk-ka’s picture

smk-ka commented
Version: 6.x-1.0 » 6.x-1.x-dev
Status: Active » Needs review
StatusFileSize
new og_user_roles-getgid.patch1.86 KB

That seems to work equally well. Turned #1 into a patch.

somebodysysop’s picture

somebodysysop commented
Status: Needs review » Fixed

OK. Code has been modified. Will be included in next release. Thanks for your assistance on this.

sun’s picture

sun
Primary language German
Location Karlsruhe
commented

Code has been modified.

Where? I do not see a commit on http://drupal.org/project/cvs/149373

somebodysysop’s picture

somebodysysop commented

Will be included in next release

I still don't know how to update the dev release. I only know how to issue new ones.

sun’s picture

sun
Primary language German
Location Karlsruhe
commented
  1. CVS checkout -A -r DRUPAL-6--1 contributions/modules/og_user_roles
  2. Apply above patch to working copy
  3. CVS commit -m "#347277 by smk-ka: Fixed slow query causing delays."
somebodysysop’s picture

somebodysysop commented

CVS checkout -A -r DRUPAL-6--1 contributions/modules/og_user_roles

Here's where I get tripped up by cvs voodoo. In my local directories, the 5.x code for ogur is in "contributions/modules/og_user_roles". The 6.x version is in "contributions/og_user_roles".

So, when I issue this command:

CVS checkout -A -r DRUPAL-6--1 contributions/modules/og_user_roles

Should the directory reflect where my 6.x files are locally, or where they are under Drupal cvs?

I am so sorry, but this confuses the living daylights out of me.

sun’s picture

sun
Primary language German
Location Karlsruhe
commented

contributions/modules/og_user_roles defines the "module folder" in CVS, not your local directory. Above command performs a checkout of contributions/modules/og_user_roles into the current directory.

somebodysysop’s picture

somebodysysop commented

OK, thanks. Followed the instructions:

1. CVS checkout -A -r DRUPAL-6--1 contributions/modules/og_user_roles
2. Apply above patch to working copy
3. CVS commit -m "#347277 by smk-ka: Fixed slow query causing delays."

These are the results:

[root@db og_user_roles]# cvs commit -m "#347277 by smk-ka: Fixed slow query causing delays."
cvs commit: Examining .
cvs commit: Examining contributions
cvs commit: Examining contributions/modules
cvs commit: Examining contributions/modules/og_user_roles
/cvs/drupal-contrib/contributions/modules/og_user_roles/README.txt,v  <--  README.txt
new revision: 1.1.2.13.2.3; previous revision: 1.1.2.13.2.2
/cvs/drupal-contrib/contributions/modules/og_user_roles/og_user_roles.module,v  <--  og_user_roles.module
new revision: 1.1.2.15.2.3; previous revision: 1.1.2.15.2.2
cvs commit: Using deprecated info format strings.  Convert your scripts to use
the new argument format and remove '1's from your info file format strings.

Is there anything else I need to do to get it updated on the project page?

BTW, it's updated here: http://cvs.drupal.org/viewvc.py/drupal/contributions/modules/og_user_rol...

sun’s picture

sun
Primary language German
Location Karlsruhe
commented

Yes, that's all you have to do to commit a changeset. (Isn't it simple? ;)

Your commit is now displayed on
http://drupal.org/project/cvs/149373
http://cvs.drupal.org/viewvc.py/drupal/contributions/modules/og_user_rol...
...and will be in the development snapshot for 6.x once the packaging scripts ran (every 12 hours).

It is recommended to commit each bug or feature in a separate commit, also linking to the project issue and providing credit. Ideally, you'll also add a CHANGELOG.txt, so users of a development snapshot know what is contained in the snapshot (and what not).

I highly recommend you to read about this in detail here: http://drupal.org/node/52287

somebodysysop’s picture

somebodysysop commented

@sun:

That *was* pretty simple. I really, really appreciate you taking the time to help me with this. I've just had the worse time trying to figure out CVS with respect to creating and maintaining a 6.x branch. This has helped me tremendously. I will read the documentation.

Again, thanks.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.