Pooly wrote:
Hi,
I ran those two queries :
mysql> select count(id) from forums_data WHERE forums_data.group_id=1
AND forums_data.state=1;
+-----------+
| count(id) |
+-----------+
| 2385 |
+-----------+
1 row in set (0.11 sec)
mysql> explain select count(id) from forums_data WHERE
forums_data.group_id=1 AND forums_data.state=1;
+----+-------------+-------------+------+---------------+-----------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+-----------+---------+-------------+------+-------+
| 1 | SIMPLE | forums_data | ref | forum_id3 | forum_id3 |
2 | const,const | 2265 | |
+----+-------------+-------------+------+---------------+-----------+---------+-------------+------+-------+
1 row in set (0.00 sec)
CREATE TABLE `forums_data` (
`id` int(10) NOT NULL auto_increment,
`group_id` tinyint(3) unsigned NOT NULL default '1',
`subject` varchar(255) NOT NULL default '',
`body` text,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`state` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`id`),
KEY `forum_id3` (`state`,`group_id`)
);
I don't understand why the number rows analyzed returned by EXPLAIN
does not match the count(*) of the query. I can understand when it's
higher, but lower ?
I'm not sure why you think higher vs. lower makes a difference. To quote
from the manual, "The rows column indicates the number of rows MySQL
believes it must examine to execute the query"
<http://dev.mysql.com/doc/mysql/en/explain.html>. Note the word "believes".
Your table stats may be out of sync. If this is a MyISAM table, you
probably just need to run
ANALYZE TABLE forums_data;
then try again. See the manual for details
<http://dev.mysql.com/doc/mysql/en/analyze-table.html>.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
