While load testing a client site, I noted that pm_block_user currently has no indexes defined. There are only two columns in this table and they are almost always used together in the module, i.e. SELECT * FROM pm_block_user where author = blank and recipient IN (blank, nobody);
I think this module would benefit quite a bit from a covering index. For example, here is an explain with a query from the module and the table with a covering index as the PK:
mysql> explain SELECT COUNT(recipient) FROM pm_block_user WHERE author = 11 AND recipient IN (12);
+----+-------------+---------------+-------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+-------------+------+-------------+
| 1 | SIMPLE | pm_block_user | const | PRIMARY | PRIMARY | 8 | const,const | 1 | Using index |
+----+-------------+---------------+-------+---------------+---------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
However, I'm not sure if this index can be the PK. Judging by pm_index, there maybe a chance for duplicate records in this table as well. Is this true? I have two patches in the wings, one a regular index and one a PK.
| Comment | File | Size | Author |
|---|---|---|---|
| #6 | privatemsg_pmblockuser.patch | 811 bytes | nnewton |
| #3 | privatemsg_pmblockuser.patch | 634 bytes | nnewton |
| #2 | pm_block_user.diff | 746 bytes | nnewton |
Comments
Comment #1
berdirIn the worst case, we might have to add some logic to avoid duplicates, however, the entries should be unique, you can't block a person more than once and you can't block yourself (in contrast to pm_index) unless you're schizophrenic and we don't support that :)
Comment #2
nnewton commentedExcellent, then I have a patch for you :)
Extremely simple, just adding the PK and an update routine.
Comment #3
nnewton commentedRe-roll this to be somewhat less old-school :)
Comment #4
litwol commentedComment #6
nnewton commentedRe-rolled this to be a standard CVS diff and not a bzr diff
-N
Comment #7
berdir#6: privatemsg_pmblockuser.patch queued for re-testing.
Comment #8
berdir#6: privatemsg_pmblockuser.patch queued for re-testing.
Comment #9
berdirCommited to all branches, thanks for reporting and providing a patch :)
Note that Privatemsg 7.x-1.x does not contain the update function.