Poor index usage with some activity queries

A client site has over 8 million rows in the {activity} table and node_save() is called very often on that site (especially updating user profiles).

The query in hook_nodeapi('update') nearly took the site down.

SELECT a.aid, u.status as user_status, a.status as activity_status FROM activity a INNER JOIN users u ON u.uid = a.uid WHERE a.uid <> 0 AND a.nid = 20132509;

This is what the explain looked like:

mysql> EXPLAIN SELECT a.aid, u.status as user_status, a.status as activity_status FROM activity a INNER JOIN users u ON u.uid = a.uid WHERE a.uid <> 0 AND a.nid = 20132509;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows    | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+---------+-------------+
|  1 | SIMPLE      | a     | ref    | nid,uid,uid_2 | nid     | 5       | const               | 1734224 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | redacted.a.uid |       1 |             |

There are two issues with that query:

1. There is no index that spans both the uid and nid columns.

2. Having the uid first in the query means that MySQL needs to first find every row in the table where uid <> 0, then find the rows matching nid = 20132509

After running ALTER TABLE activity ADD INDEX nid_uid (nid, uid); and swapping the query clauses, I got it down to this:

mysql> EXPLAIN SELECT a.aid, u.status as user_status, a.status as activity_status FROM activity a INNER JOIN users u ON u.uid = a.uid WHERE a.nid = 20132509 AND a.uid <> 0;
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | a     | range  | nid,uid,nid_uid | nid_uid | 9       | NULL                |    3 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY         | PRIMARY | 4       | www_zinch_com.a.uid |    1 |             |
+----+-------------+-------+--------+-----------------+---------+---------+---------------------+------+-------------+
2 rows in set (0.00 sec)

Patch forthcoming.