Add an index for message timestamp

Patch is welcome, and a benchmark even more ;)

I agree. Well, then at least a patch ;)

In the interest of only adding indexes that we know we're running into issues with, here's a patch that adds one to the timestamp column.

I ran into this in Commons, where we have a query like this:

  SELECT message.mid AS mid, message.timestamp AS message_timestamp
  FROM message message
  WHERE (((message.type IN ('commons_activity_streams_comment_created', 'commons_activity_streams_node_created', 'commons_follow_user_user_followed', 'commons_wikis_wiki_updated', 'commons_q_a_question_asked')) ))
  ORDER BY message_timestamp DESC
  LIMIT 5 OFFSET 0

Before this patch:

+------+-------------+---------+------+---------------+------+---------+------+--------+-----------------------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+------+-------------+---------+------+---------------+------+---------+------+--------+-----------------------------+
|    1 | SIMPLE      | message | ALL  | NULL          | NULL | NULL    | NULL | 168463 | Using where; Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+--------+-----------------------------+

Filesort
+- Filter with WHERE
   +- Table scan
      rows           168463
      +- Table
         table          message

After:

+------+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| id   | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+------+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | message | index | NULL          | timestamp | 4       | NULL |    5 | Using where |
+------+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+


Filter with WHERE
+- Bookmark lookup
   +- Table
   |  table          message
   +- Index scan
      key            message->timestamp
      key_len        4
      rows           5

Query on my local machine goes from about 250ms to 0.4ms.

Re-titling and tagging.

After applying patch and using a query that has WHERE on the Message-type (a typical use case), mysql still doesn't use the index key

EXPLAIN SELECT message.mid AS mid, message.timestamp AS message_timestamp FROM message message WHERE message.type = "example_create_node" ORDER BY message_timestamp DESC LIMIT 5 OFFSET 0;

Should we add an index for that as-well?

Interesting. That query uses the timestamp index for me, but that's probably just a difference between envs (I'm running MariaDB 5.5 locally).

And yeah, we probably should add an index to type as well.

Add index to "type" as-well and committed, thanks.