Bug #322 Explain returns wrong row count if FORCE INDEX is used

• Submitted: 24 Apr 2003 6:51
• Modified: 27 Apr 2003 11:28
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.12
• OS: Any (all)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[24 Apr 2003 6:51] Peter Zaitsev

Description:
Number of rows in EXPLAIN output for some queries is different in case FORCE INDEX is used, exactly 2 times less to be specific:

How to repeat:

CREATE TABLE fibug (
  i int(11) NOT NULL default '0',
  c char(40) default NULL,
  KEY i (i)
) TYPE=InnoDB;

--
-- Dumping data for table 'fibug'
--

INSERT INTO fibug VALUES (1,NULL),(2,NULL),(3,NULL),(4,NULL),(5,'1'),(6,'1'),(7,'1'),(9,'1'),(10,'1'),(10,'1'),(10,'1'),(10,'1'),(11,'1');

mysql> explain select * from fibug use index(i)  where i>1 order by i limit 1;
+-------+-------+---------------+------+---------+------+------+-------------+
| table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+-------+-------+---------------+------+---------+------+------+-------------+
| fibug | range | i             | i    |       4 | NULL |   13 | Using where |
+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from fibug force index(i)  where i>1 order by i limit 1;
+-------+-------+---------------+------+---------+------+------+-------------+
| table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+-------+-------+---------------+------+---------+------+------+-------------+
| fibug | range | i             | i    |       4 | NULL |    6 | Using where |
+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

[27 Apr 2003 11:28] Michael Widenius

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html