Adding an index on the userpoints.points field

I need opinions from others whether this use case is generic enough to include the patch in the base module.

@colin49: #857712: How can add a user ranking based on the module Userpoints? is a feature request for adding a ranking, probably similiar to what you have done for your site. Are you interested in sharing your code? I guess if it is a feature of userpoints.module, it does make sense to add an index. If there is no query in userpoints.module that does actually use that index, I'd say it shouldn't be added.

Actually, I'd like to have that index as well, plus a bunch more :-)

I run two queries in the userpoints_evaporate module that have the userpoints.points field in a WHERE clause and without an index performance is totally woeful. I've added the index by hand here to resolve the issue, but it would be good to have it added upstream.

The other fields that would probably benefit from an index for people that might want to query them directly or join on them are:

userpoints.uid
userpoints_txn.uid
userpoints_txn.approver_uid
userpoints_txn.points

The attached patch updates hook_schema and adds an update() hook to create these indexes.

No idea why the patch doesn't apply...

+++ userpoints.install	9 Nov 2010 00:01:04 -0000
@@ -230,3 +235,12 @@ function userpoints_update_8() {
+function userpoints_update_9() {

- Comment is missing here.
- I know that the existing update functions are named wrong, but this should be 600X nevertheless :)

Note: I usually only commit patches for D7, so if you can provide a patch for Drupal 7 too, I'll commit that one.

Powered by Dreditor.

Ok, so that patch was all wrong; I'd made it against HEAD and not 6.x-1.x. First off, here is the proper 6.x patch.

6.x-1.x already had a compound index on userpoints.uid and userpoints.tid and that is used in a WHERE clause on uid as well, so there is no need for an extra uid index on that table.

.. and here is the same patch, rolled for 7.x-1.x.

Thanks, commited to 7.x-1.x.

Back to 6.x-1.x...

subscribing

subscribe

A reason for patch in #8 wasn't commited ?

Fixing the issue title. Sorry, it bugs me.

Commited, back to 7.x, the update function 7.x needs to be made conditional now so that it doesn't try to add the indexes twice.

Attached is a patch that turns the db_add_index() calls in 7.x into conditionals, so that if a user has added indexes in D6 they won't get an error when upgrading to D7.

Great, thanks! Commited.