Add index on uid and module columns to authmap

Problem/Motivation

We manage a site for a client that uses an external identity store for user authentication. At any given time, we have approximately 1 million users, and a corresponding 1 million rows in our authmap table. We also maintain rolling deletes of stale users.

Review with New Relic showed that we were getting slower than expected performance with some selects and delete queries on the authmap table. It traced back to the following queries:

From user_delete_multiple():

db_delete('authmap')
        ->condition('uid', $uids, 'IN')
        ->execute();

From user_set_authmaps:

if ($value) {
  db_merge('authmap')
    ->key(array(
      'uid' => $account->uid,
      'module' => $module[1],
    ))
    ->fields(array('authname' => $value))
    ->execute();
}
else {
  db_delete('authmap')
    ->condition('uid', $account->uid)
    ->condition('module', $module[1])
    ->execute();
}

There is no index on either the uid or module columns, yielding to slower performance for these queries.

Proposed resolution

Add an index to authmap to handle these queries:

CREATE INDEX uid_module ON authmap (uid, module)

Because uid is the first column in the index, the delete query will use the multi-column index as well.

Remaining tasks

None

User interface changes

None

API changes

None

Data model changes

As above, a new index on authmap (uid, module).