3 messages in com.mysql.lists.mysqlRe: question with rows count
FromSent OnAttachments
Pooly28 Aug 2005 12:43 
Michael Stassen28 Aug 2005 15:03 
Pooly28 Aug 2005 16:12 
Subject:Re: question with rows count
From:Pooly (poo@gmail.com)
Date:08/28/2005 04:12:02 PM
List:com.mysql.lists.mysql

2005/8/28, Michael Stassen <Mich@verizon.net>:

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;

I ran OPTIMIZE TABLE forums_data juste before performing those queries. So, the "rows" returned int the EXPLAIN command juste indicate a rough count of how many rows MySQL think it will have to examine for running the query ? Ok, it's a bit clearer.

-- Pooly Webzine Rock : http://www.w-fenec.org/