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.

Comments

berdir’s picture

In 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 :)

nnewton’s picture

StatusFileSize
new746 bytes

Excellent, then I have a patch for you :)

Extremely simple, just adding the PK and an update routine.

nnewton’s picture

StatusFileSize
new634 bytes

Re-roll this to be somewhat less old-school :)

litwol’s picture

Status: Active » Needs review

Status: Needs review » Needs work

The last submitted patch, privatemsg_pmblockuser.patch, failed testing.

nnewton’s picture

Status: Needs work » Needs review
StatusFileSize
new811 bytes

Re-rolled this to be a standard CVS diff and not a bzr diff

-N

berdir’s picture

#6: privatemsg_pmblockuser.patch queued for re-testing.

berdir’s picture

#6: privatemsg_pmblockuser.patch queued for re-testing.

berdir’s picture

Status: Needs review » Fixed

Commited to all branches, thanks for reporting and providing a patch :)

Note that Privatemsg 7.x-1.x does not contain the update function.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.