Add an index on the uid column of og_uid table [#793588]

Comment	File	Size	Author
#4	add_uid_index_to_og_uid-793588-4-D6.patch	628 bytes	pdrake
#3	793588-og_uid-add-index-3.patch	846 bytes	msonnabaum

Comment #1

amitaibu

Hebrew

Israel

commented 13 May 2010 at 20:09

As mentioned by Moshe, we'd like to see some benchmarks to support this change.

Log in or register to post comments

table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users_roles	ref	PRIMARY,rid	rid	4	const	4377	Using index; Using temporary; Using filesort
1	SIMPLE	users	eq_ref	PRIMARY	PRIMARY	4	databasename.users_roles.uid	1
1	SIMPLE	users_roles2	eq_ref	PRIMARY,rid	PRIMARY	8	databasename.users.uid,const	1	Using where; Using index; Not exists
1	SIMPLE	node_users	ref	node_type,uid	uid	4	databasename.users.uid	1
1	SIMPLE	og_uid	index	NULL	PRIMARY	8	NULL	8363	Using index
1	SIMPLE	node_og_uid	eq_ref	PRIMARY	PRIMARY	4	databasename.og_uid.nid	1	Using index
1	SIMPLE	node_og_uid__og_subgroups	ref	PRIMARY	PRIMARY	4	databasename.node_og_uid.nid	1	Using where; Using index
1	SIMPLE	node_users_node_data_field_profile_picture	eq_ref	PRIMARY	PRIMARY	4	databasename.node_users.vid	1
1	SIMPLE	node_users_node_data_field_lastname	eq_ref	PRIMARY	PRIMARY	4	databasename.node_users.vid	1

table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users_roles	ref	PRIMARY,rid	rid	4	const	4377	Using index; Using temporary; Using filesort
1	SIMPLE	users	eq_ref	PRIMARY	PRIMARY	4	databasename.users_roles.uid	1
1	SIMPLE	users_roles2	eq_ref	PRIMARY,rid	PRIMARY	8	databasename.users.uid,const	1	Using where; Using index; Not exists
1	SIMPLE	node_users	ref	node_type,uid	uid	4	databasename.users.uid	1
1	SIMPLE	og_uid	ref	uid	uid	4	databasename.users.uid	41	Using index
1	SIMPLE	node_og_uid	eq_ref	PRIMARY	PRIMARY	4	databasename.og_uid.nid	1	Using index
1	SIMPLE	node_og_uid__og_subgroups	ref	PRIMARY	PRIMARY	4	databasename.node_og_uid.nid	1	Using where; Using index
1	SIMPLE	node_users_node_data_field_profile_picture	eq_ref	PRIMARY	PRIMARY	4	databasename.node_users.vid	1
1	SIMPLE	node_users_node_data_field_lastname	eq_ref	PRIMARY	PRIMARY	4	databasename.node_users.vid	1

Comment #3

msonnabaum commented 17 June 2011 at 17:26

Status:

Active

» Needs review

Status	File	Size
new	793588-og_uid-add-index-3.patch	846 bytes

And here's a patch.

Log in or register to post comments

Comment #4

pdrake commented 29 November 2011 at 20:02

Status:

Needs review

» Reviewed & tested by the community

Status	File	Size
new	add_uid_index_to_og_uid-793588-4-D6.patch	628 bytes

I also ran into a basic view which needs this index. This query is from a view of groups in which a user is active. Below are profiles of the query before and after.

| 1 | 0.34071700 | SELECT sql_no_cache n.title, n.TYPE, n.status FROM og_uid ou INNER JOIN node n ON ou.nid = n.nid WHERE ou.uid = ? AND ou.is_active >= 1 AND n.TYPE in('group') ORDER BY n.title
| 2 | 0.03641400 | SELECT sql_no_cache n.title, n.TYPE, n.status FROM og_uid ou INNER JOIN node n ON ou.nid = n.nid WHERE ou.uid = ? AND ou.is_active >= 1 AND n.TYPE in('group') ORDER BY n.title

This is the same as the above patch, but re-rolled against the latest 6.x-2.x and using db_add_index instead of SQL. Given that I changed this patch slightly, feel free to reset my RTBC back to needs review if necessary. Not sure what the proper status would be here.