Performance Tweaks

Neil Drumm just went through some of the queries run by update.inc via chatroomread.php. Here are the initial raw results. I'll be going through this and writing a patch later.

Missing indexes:
ALTER TABLE blogs_chatroom_online_list ADD INDEX (session_id);
ALTER TABLE blogs_chatroom_online_list ADD INDEX (uid);

ALTER TABLE blogs_chatroom_online_list ADD INDEX (modified);

ALTER TABLE blogs_chatroom_chat ENGINE innodb;
ALTER TABLE blogs_chatroom_ ENGINE innodb;
ALTER TABLE blogs_chatroom_msg ENGINE innodb;
ALTER TABLE blogs_chatroom_ban_list ENGINE innodb;
ALTER TABLE blogs_chatroom_msg_archive ENGINE innodb;
ALTER TABLE blogs_chatroom_online_list ENGINE innodb;

from chatroom_chat_is_registered_user($chat_id):
  $archived = db_result(db_query("SELECT when_archived FROM {chatroom_chat} WHERE ccid = %d", $chat_id));
  if (!isset($archived)) {
    $sql = "SELECT guest_id FROM {chatroom_online_list} WHERE ccid = %d AND session_id = '%s'";
    $guest_id = db_result(db_query_range($sql, $chat_id, session_id(), 0, 1));
  }

from function chatroom_chat_update_online_time($chat_id):
  db_query("
    UPDATE {chatroom_online_list} SET modified = %d
    WHERE ccid = %d AND uid = %d AND session_id = '%s'
  ", time(), $chat_id, $user->uid, session_id());

from chatroom_chat_get_latest_msgs($chat_id, $uid, $last_msg_id = 0, $timezone = 0, $smileys = FALSE):
db_query_range("
      SELECT cm.*, u.name, col.guest_id FROM {chatroom_msg} cm
      INNER JOIN {chatroom_online_list} col ON col.session_id = cm.session_id AND col.ccid = cm.ccid
      LEFT JOIN {users} u ON u.uid = cm.uid
      WHERE cm.ccid = %d AND (
        cm.recipient = '' OR cm.session_id = '%s' OR cm.recipient IN (
          SELECT guest_id FROM {chatroom_online_list}
          WHERE session_id = '%s' OR (uid = %d AND uid > 0)
        ) OR (cm.uid = %d AND cm.uid > 0)
      )
      ORDER BY cm.cmid DESC
    ", array(
      $chat_id,
      session_id(),
      session_id(),
      $user->uid,
      $user->uid,
    ), 0, $limit);

sample: SELECT cm.*, u.name, col.guest_id FROM blogs_chatroom_msg cm INNER JOIN blogs_chatroom_online_list col ON col.session_id = cm.session_id AND col.ccid = cm.ccid LEFT JOIN shared_users u ON u.uid = cm.uid WHERE cm.ccid = 2 AND (cm.recipient = '' OR cm.session_id = 'd67ad287bd5747c1f05c73f52e7e13b8' OR cm.recipient IN (SELECT guest_id FROM blogs_chatroom_online_list WHERE session_id = 'd67ad287bd5747c1f05c73f52e7e13b8' OR (uid = 1 AND uid > 0)) OR (cm.uid = 1 AND cm.uid > 0)) ORDER BY cm.cmid DESC;

explain:
+----+--------------------+----------------------------+-------------+---------------------------+----------------+---------+---------------------------+------+------------------------------------------+
| id | select_type        | table                      | type        | possible_keys             | key            | key_len | ref                       | rows | Extra                                    |
+----+--------------------+----------------------------+-------------+---------------------------+----------------+---------+---------------------------+------+------------------------------------------+
|  1 | PRIMARY            | cm                         | ref         | ccid,session_id,recipient | ccid           | 4       | const                     |   19 | Using where; Using filesort              |
|  1 | PRIMARY            | col                        | ref         | update_time,session_id    | session_id     | 767     | airamerica2.cm.session_id |    5 | Using where                              |
|  1 | PRIMARY            | u                          | eq_ref      | PRIMARY                   | PRIMARY        | 4       | airamerica2.cm.uid        |    1 |                                          |
|  2 | DEPENDENT SUBQUERY | blogs_chatroom_online_list | index_merge | session_id,uid            | session_id,uid | 767,4   | NULL                      |    2 | Using union(session_id,uid); Using where |
+----+--------------------+----------------------------+-------------+---------------------------+----------------+---------+---------------------------+------+------------------------------------------+

from chatroom_chat_get_online_list($chat_id, $cache_timestamp, $update_count = 7):
db_query("
      SELECT
        col.uid,
        col.session_id,
        col.guest_id,
        col.away,
        cc.chatname,
        du.name
      FROM {chatroom_online_list} col
      INNER JOIN {chatroom_chat} cc ON cc.ccid = col.ccid
      LEFT JOIN {users} du ON du.uid = col.uid
      WHERE col.ccid = %d AND col.modified > cc.modified
    ", $chat_id);

sample: SELECT col.uid, col.session_id, col.guest_id, col.away, cc.chatname, du.name FROM blogs_chatroom_online_list col INNER JOIN blogs_chatroom_chat cc ON cc.ccid = col.ccid LEFT JOIN shared_users du ON du.uid = col.uid WHERE col.ccid = 2 AND col.modified > cc.modified;

explain:
+----+-------------+-------+--------+---------------------------+----------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys             | key      | key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+---------------------------+----------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | cc    | const  | PRIMARY,modified          | PRIMARY  | 4       | const               |    1 |             |
|  1 | SIMPLE      | col   | range  | update_time,ccid,modified | modified | 4       | NULL                |    1 | Using where |
|  1 | SIMPLE      | du    | eq_ref | PRIMARY                   | PRIMARY  | 4       | airamerica2.col.uid |    1 |             |
+----+-------------+-------+--------+---------------------------+----------+---------+---------------------+------+-------------+