For userpoints_txn, need an index on expirydate and expired columns

Hi all,

We have a database with more than 400000 users, and cron was taking about 5 seconds to run. Analyzing the various hook implementations and looking at the queries executed by them, I have found that a bit more than 4 of those seconds was used by userpoints_cron(). The problem is the main query, that filters records by concepts that are not indexed. Adding an index the time to run the whole cron falled down to less than one second for all the stuff we have installed in this site.

The query that needs optimization is this one:

  $sql = "SELECT txn_id, uid, points, time_stamp, operation, description 
          FROM {userpoints_txn} 
          WHERE status = 0 AND expired = 0 
          AND (expirydate < %d AND expirydate != 0)"; 

There is already an index by 'status' in the {userpoints_txn} table, however we need to include 'expired' and 'expirydate' to this index, so I have removed the 'status' index, and added a new one called 'status_expire_date' that includes the 3 columns.

I did this by hand, but ideally this could be implemented using a new hook_update_N() implementation in userpoints.install.