diaspora/diaspora — pull request #3087 by christophe-de (2012-03-30 15:56:39+00:00)
Title: DB indexes

Hi,

I started adding some of the missing indexes. There's definitely room for more improvements.

In the posts table there should be an index on id, type and interacted_at. I didn't add that one right away, because I think we could combine it with the new index on id, type and created_at to an index on id, type, interacted_at and created_at and changing all queries using created_at to include interacted_at (and setting it to created_at, because interacted_at >= created_at).

http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html

Here's an example for such a query. I think they're used on the activity page

SELECT `posts`.* FROM `posts` INNER JOIN `participations` ON `participations`.`target_id` = `posts`.`id` AND `participations`.`target_type` = 'Post' WHERE `participations`.`author_id` = xx AND `posts`.`type` IN ('StatusMessage', 'Reshare', 'ActivityStreams::Photo') AND (posts.interacted_at < 'xx') AND (posts.author_id NOT IN (xx,xx,xx,...)) AND (posts.id NOT IN ('xx','xx','xx','...')) ORDER BY posts.interacted_at DESC, posts.interacted_at desc LIMIT 15\G

There seem to be quite a lot of duplicate indexes as well, have a look at the output from mk-duplicate-key-checker. If nobody protests, I will remove them.

http://pastebin.com/40L0nfTn

The last thing I noticed were these kind of queries:

SELECT `posts`.* FROM `posts` WHERE `posts`.`id` IN (xx, xx, xx, /*... omitted 26579 items ...*/) AND `posts`.`type` IN ('StatusMessage', 'Reshare', 'ActivityStreams::Photo') AND (posts.created_at < '2012-03-30 15:34:47') ORDER BY posts.created_at desc LIMIT 15\G

I don't really understand why index_posts_on_id_and_type_and_created_at is not used, but having 26579(!) clauses in your query can't be a good thing.