Re: Very big Handler_read_rnd_next when using PunBB ?
OK, now run the explain again 
Re: Very big Handler_read_rnd_next when using PunBB ?
With reputation mod:
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)Without reputation mod:
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 | 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 | |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
4 rows in set (0.00 sec)Darmowe forum - Polish free forum hosting
Re: Very big Handler_read_rnd_next when using PunBB ?
MySQL is still not optimizing the query properly: I'm not sure why 
Are you sure you didn't mess with the MySQL setup between when you ran the first explain and when you encountered this odd behavior?
Re: Very big Handler_read_rnd_next when using PunBB ?
No, I'm only restarted MySQL.
Darmowe forum - Polish free forum hosting
Re: Very big Handler_read_rnd_next when using PunBB ?
Well, I'm not sure what to tell you then
Re: Very big Handler_read_rnd_next when using PunBB ?
heh, where is my server status from phpmyadmin. Memember that MySQL server has been only running for 1 hour and 48 minutes.
I'm wondering about missing index (Select_full_join = 1,066) and Opened_tables (229 k / 2,048 k).
Darmowe forum - Polish free forum hosting
Re: Very big Handler_read_rnd_next when using PunBB ?
The higher number in select_full_join is caused by the poorly optimized query: again, until you can figure out why your setup messes up the way it does, there isn't really an easy fix
Re: Very big Handler_read_rnd_next when using PunBB ?
Smartys I find out that sql question in viewtopic.php
SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, ..... LEFT JOIN '.$db->prefix.'reputation as r ON (r.user_id=u.id) ..... GROUP BY p.idis very slow With this mod my topic with >4000 posts is generated in 132 seconds but without this mod* only 0.63 seconds 
Have you any ideas what is wrong with this question ?
* - I delete only reputation queries at line 186
Darmowe forum - Polish free forum hosting
Re: Very big Handler_read_rnd_next when using PunBB ?
I think the topic having thousands of posts is more of an issue. 
Once you have thousands of rows being returned by a more complex query, you're going to run into slowdown issues: for every pageview, the joins etc have to be done for thousands of posts. You might be able to rewrite the query a little to speed it up, but I'm not sure how great it would be.