Improve indexes on pm_* tables

Uh oh, catch is looking at my queries :)

Yeah, you're right, confirmed this on my setup. Will write a patch soon...

Ok, I took the opportunity and looked through most of our queries.

Turned out that most of our indexes aren't used at all, so I tried to optimize the indexes and this is what I came up with.

For the reference, these are the queries which I used for testing (first the index and then the queries which are improved by that index):

----
list: uid, deleted, is_new (pm_index)
SELECT pmi.thread_id, MIN(pm.subject) as subject, MAX(pm.timestamp) as last_updated, SUM(pmi.is_new) as is_new, COUNT(distinct pmi.mid) as count, (SELECT GROUP_CONCAT(DISTINCT pmia.uid SEPARATOR ",") FROM pm_index pmia WHERE pmia.thread_id = pmi.thread_id) AS participants FROM pm_message pm INNER JOIN pm_index pmi ON pm.mid = pmi.mid INNER JOIN pm_tags_index pmti0 ON (pmti0.thread_id = pmi.thread_id AND pmti0.uid = pmi.uid) WHERE (pmi.uid = 1) AND (pmi.deleted = 0) AND (pmti0.tag_id = 830) GROUP BY pmi.thread_id ORDER BY last_updated DESC LIMIT 0, 25

SELECT COUNT(DISTINCT thread_id) as unread_count FROM pm_index pmi WHERE (pmi.deleted = 0) AND (pmi.is_new = 1) AND (pmi.uid = 1)
----

messages: mid, uid (pm_index)
SELECT SQL_NO_CACHE pm.mid, pm.author, pm.subject, pm.body, pm.timestamp, pm.format, pmi.is_new, pmi.thread_id, pmbu.recipient AS is_blocked FROM pm_message pm INNER JOIN pm_index pmi ON pm.mid = pmi.mid LEFT JOIN pm_block_user pmbu ON (pm.author = pmbu.author AND pmi.uid = pmbu.recipient) WHERE (pmi.mid IN (3271,3272,3273,3274,3275,3276,3277,3278,3279,3280,3281,3282,3283,3284,3285,3286,3287,3288,3289,3303)) AND (pmi.uid = 1) ORDER BY pm.timestamp ASC, pm.mid ASC
----

participants: thread_id, uid, deleted (pm_index)
SELECT DISTINCT(pmi.uid) AS uid, u.name AS name FROM pm_index pmi INNER JOIN users u ON (u.uid = pmi.uid) WHERE (pmi.thread_id = 3267)

SELECT pmi.mid FROM pm_index pmi INNER JOIN pm_message pm ON (pm.mid = pmi.mid) WHERE (pmi.thread_id IN (382)) AND (pmi.uid = 1) AND (pmi.deleted = 0) GROUP BY pmi.mid, pm.timestamp ORDER BY pm.timestamp ASC, pmi.mid ASC LIMIT 32, 50
----

thread_tags: thread_id, uid (pm_tags_index)
SELECT t.tag, t.tag_id, t.public, ti.thread_id FROM pm_tags t INNER JOIN pm_tags_index ti on ti.tag_id = t.tag_id WHERE (ti.thread_id IN (382)) AND (ti.uid = 1) ORDER BY t.tag ASC
----

tag_list: tag_id, tag, public (pm_tags)
SELECT SQL_NO_CACHE t.tag, t.tag_id, t.public, COUNT(ti.thread_id) as count FROM pm_tags t LEFT JOIN pm_tags_index ti ON t.tag_id = ti.tag_id WHERE (ti.uid = 1) GROUP BY t.tag_id, t.tag, t.public ORDER BY t.tag ASC

The patches updates the hook_schema() definition for those 4 tables and also provides an update function.

Ahh, now I got it ;)

Simpletest does not display notices for D6 too... ;)

The attached patch should now pass.

Tried the upgrade path on a clean install of privatemsg 6.x-1.x-dev and it worked fine. So I've decided to commit this. As said before, this needs to be done for 6.x-2.x to before it gets stable but it doesn't make much sense now.

I've recently updated the tables for 7.x-1.x, see #968384: Error when upgrading to 2.x-dev version for 6.x-2.x.