Topic: Problem with queries, chatbox

Hi,

I have 2 questions about queries from ChatBox mod.

1) Could you tell me where should I have INDEX for?

2) Why rows=100 when limit=50?

SELECT u.id, u.group_id, u.num_posts_chatbox, m.id AS m_id, m.poster_id, m.poster, m.poster_ip, m.poster_email, m.message, m.posted, g.g_id, g.g_title_chatbox FROM chatbox_msg AS m INNER JOIN users AS u ON u.id=m.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id WHERE m.posted > 0 ORDER BY m.posted ASC LIMIT 0,50;
mysql> explain SELECT u.id, u.group_id, u.num_posts_chatbox, m.id AS m_id, m.poster_id, m.poster, m.poster_ip, m.poster_email, m.message, m.posted, g.g_id, g.g_title_chatbox FROM chatbox_msg AS m INNER JOIN users AS u ON u.id=m.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id WHERE m.posted > 0 ORDER BY m.posted ASC LIMIT 0,50;
+----+-------------+-------+--------+------------------+---------+---------+----------------------------+------+-----------------------------+
| id | select_type | table | type   | possible_keys    | key     | key_len | ref                        | rows | Extra                       |
+----+-------------+-------+--------+------------------+---------+---------+----------------------------+------+-----------------------------+
|  1 | SIMPLE      | m     | ALL    | NULL             | NULL    | NULL    | NULL                       |  100 | Using where; Using filesort |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY,group_id | PRIMARY | 4       | forum.m.poster_id          |    1 | Using where                 |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY          | PRIMARY | 4       | forum.u.group_id           |    1 |                             |
+----+-------------+-------+--------+------------------+---------+---------+----------------------------+------+-----------------------------+
3 rows in set (0.00 sec

Thank you for a help.

Darmowe forum - Polish free forum hosting

Re: Problem with queries, chatbox

Query on m.posted, although it doesn't look like that would really help much here.
And the number of rows in the column is how many it looked at, I believe.