Bug #5311 "const" row read breaks order by optimization
Submitted: 31 Aug 2004 3:19 Modified: 8 Sep 2004 6:01
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.4 OS:Any (all)
Assigned to: Timour Katchaounov CPU Architecture:Any

[31 Aug 2004 3:19] Peter Zaitsev
Description:
The "const" table reference described as  row being pre-read and  its values treated as constants, this however does not seems to be the case:

mysql> explain select * from t2 where j=1 order by t2.c;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t2    | ref  | j             | j    |       5 | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t1,t2 where t1.c="a" and i=j order by t2.c;
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t1    | const | PRIMARY,c     | c    |      11 | const |    1 | Using filesort           |
|  1 | SIMPLE      | t2    | ref   | j             | j    |       5 | const |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)

As you can see "filesort" is used in second case while it is not if we substitute const value manually.

How to repeat:
--

CREATE TABLE t1 (
  i int(11) NOT NULL default '0',
  c char(10) NOT NULL default '',
  PRIMARY KEY  (i),
  UNIQUE KEY c (c)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t1`
--

INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');

--
-- Table structure for table `t2`
--

CREATE TABLE t2 (
  j int(11) default NULL,
  c char(1) default NULL,
  KEY j (j,c)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t2`
--

INSERT INTO t2 VALUES (1,'d'),(1,'g'),(2,'e');
[31 Aug 2004 4:10] MySQL Verification Team
Thank you for the bug report I was able to repeat with latest Windows
server.
[8 Sep 2004 6:01] Timour Katchaounov
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Currently MySQL does not support such an optimization. More precisely, the optimizer does
indeed detect constant tables based on schema information, however this information is not
propagated to the later optimization phase where it is decided if ORDER BY can be computed
without sorting by index access only, that is, the optimizer does not consider t2.j to be a
constant, thus there is no constant prefix to search in index j.

Such an optimization will be added as a new feature in an upcoming version of MySQL.