Very big Handler_read_rnd_next when using PunBB ? (Page 1 of 2)

explain SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) LEFT JOIN reputation as r ON (r.user_id=u.id) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2000,25;

describe reputation

show indexes from reputation

create index rep_user_id_idx on reputation(user_id)

LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0)

# Query_time: 19  Lock_time: 0  Rows_sent: 15  Rows_examined: 158486
use pun_klasa3h;
SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) LEFT JOIN reputation as r ON (r.user_id=u.id) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2100,25;

mysql> explain SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) LEFT JOIN reputation as r ON (r.user_id=u.id) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2100,25;
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys                      | key                | key_len | ref                    | rows | Extra                           |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
|  1 | SIMPLE      | u     | ALL  | PRIMARY                            | NULL               | NULL    | NULL                   |   26 | Using temporary; Using filesort |
|  1 | SIMPLE      | o     | ref  | online_user_id_idx                 | online_user_id_idx | 4       | pun_klasa3h.u.id       |    2 |                                 |
|  1 | SIMPLE      | g     | ALL  | PRIMARY                            | NULL               | NULL    | NULL                   |    3 | Using where                     |
|  1 | SIMPLE      | p     | ref  | posts_topic_id_idx,posts_multi_idx | posts_multi_idx    | 8       | pun_klasa3h.u.id,const |    6 |                                 |
|  1 | SIMPLE      | r     | ref  | rep_user_id_idx                    | rep_user_id_idx    | 4       | pun_klasa3h.u.id       |   43 |                                 |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
5 rows in set (0.00 sec)

mysql> describe reputation;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| user_id      | int(10) unsigned    | NO   | MUL | 0       |                |
| from_user_id | int(10) unsigned    | NO   |     | 0       |                |
| time         | int(10) unsigned    | NO   |     | 0       |                |
| post_id      | int(10) unsigned    | NO   | MUL | 0       |                |
| reason       | text                | NO   |     |         |                |
| rep_plus     | tinyint(1) unsigned | NO   |     | 0       |                |
| rep_minus    | tinyint(1) unsigned | NO   |     | 0       |                |
| topics_id    | int(10) unsigned    | NO   | MUL | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

mysql> show indexes from reputation;
+------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| reputation |          0 | PRIMARY               |            1 | id           | A         |         735 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_post_id_idx       |            1 | post_id      | A         |         735 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_multi_user_id_idx |            1 | topics_id    | A         |          81 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_multi_user_id_idx |            2 | from_user_id | A         |         183 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_user_id_idx       |            1 | user_id      | A         |          17 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> show indexes from posts;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| posts |          0 | PRIMARY            |            1 | id          | A         |        4555 |     NULL | NULL   |      | BTREE      |         |
| posts |          1 | posts_topic_id_idx |            1 | topic_id    | A         |         198 |     NULL | NULL   |      | BTREE      |         |
| posts |          1 | posts_multi_idx    |            1 | poster_id   | A         |          18 |     NULL | NULL   |      | BTREE      |         |
| posts |          1 | posts_multi_idx    |            2 | topic_id    | A         |         759 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.11 sec)

mysql> show indexes from users;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          0 | PRIMARY              |            1 | id          | A         |          26 |     NULL | NULL   |      | BTREE      |         |
| users |          1 | users_registered_idx |            1 | registered  | A         |          26 |     NULL | NULL   |      | BTREE      |         |
| users |          1 | users_username_idx   |            1 | username    | A         |          26 |        8 | NULL   |      | BTREE      |         |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.01 sec)

mysql> show indexes from groups;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| groups |          0 | PRIMARY  |            1 | g_id        | A         |           4 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.06 sec)

mysql> explain SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2100,25;
+----+-------------+-------+--------+------------------------------------+-----------------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys                      | key             | key_len | ref                    | rows | Extra                           |
+----+-------------+-------+--------+------------------------------------+-----------------+---------+------------------------+------+---------------------------------+
|  1 | SIMPLE      | o     | system | online_user_id_idx                 | NULL            | NULL    | NULL                   |    0 | const row not found             |
|  1 | SIMPLE      | u     | ALL    | PRIMARY                            | NULL            | NULL    | NULL                   |   26 | Using temporary; Using filesort |
|  1 | SIMPLE      | g     | ALL    | PRIMARY                            | NULL            | NULL    | NULL                   |    3 | Using where                     |
|  1 | SIMPLE      | p     | ref    | posts_topic_id_idx,posts_multi_idx | posts_multi_idx | 8       | pun_klasa3h.u.id,const |    6 |                                 |
+----+-------------+-------+--------+------------------------------------+-----------------+---------+------------------------+------+---------------------------------+
4 rows in set (0.47 sec)

analyze table users

analyze table posts

analyze table groups