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) Email Updates:
Status: Closed Impact on me:
None 
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.