MySQL Bugs: #16590: Optimized does not do right "const" table pre-read

Bug #16590	Optimized does not do right "const" table pre-read
Submitted:	18 Jan 2006 1:16	Modified:	13 Feb 2007 20:07
Reporter:	Peter Zaitsev (Basic Quality Contributor)
Status:	Closed
Category:	Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Any (all)
Assigned to:	Georgi Kodinov	Target Version:

Description:
See above: 

MySQL will force "filesort" in first explain plan, while it is not needed as user_id is
constant and so ordering can be done by index. 

If you "help"  MySQL a bit by adding  user_id=<const>  to where plan, it is able to
select correct one.    It should nowever be able to do that without this help as
according to our manual "const" table are pre-read and values from their rows  treated
just as constants in query.

mysql> explain select message from usr,messages where login='bbb' and id=user_id order by
ts desc limit 5;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+----------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows
| Extra          |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+----------------+
|  1 | SIMPLE      | usr      | const | PRIMARY,login | login   |     192 | const |    1
| Using filesort |
|  1 | SIMPLE      | messages | ref   | user_id       | user_id |       4 | const |    3
| Using where    |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+----------------+
2 rows in set (0.00 sec)

mysql> explain select message from usr,messages where login='bbb' and id=user_id and
user_id=2 order by ts desc limit 5;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows
| Extra       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | usr      | const | PRIMARY,login | PRIMARY |       4 | const |    1
|             |
|  1 | SIMPLE      | messages | ref   | user_id       | user_id |       4 | const |   19
| Using where |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

How to repeat:
Table structures 

CREATE TABLE `messages` (
  `user_id` int(10) unsigned NOT NULL default '0',
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `message` varchar(255) default NULL,
  KEY `user_id` (`user_id`,`ts`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `messages` VALUES (2,'2006-01-17 16:08:35','aaa'),(2,'2006-01-17
16:08:35','aaa'),(2,'2006-01-17 16:08:38','aaa'),(2,'2006-01-17
16:08:39','aaa'),(2,'2006-01-17 16:08:40','aaa'),(2,'2006-01-17
16:08:40','aaa'),(2,'2006-01-17 16:08:41','aaa'),(2,'2006-01-17
16:08:43','aaa'),(2,'2006-01-17 16:08:43','aaa'),(2,'2006-01-17
16:08:44','aaa'),(2,'2006-01-17 16:08:46','aaa'),(1,'2006-01-17
16:08:49','aaa'),(1,'2006-01-17 16:08:50','aaa'),(1,'2006-01-17
16:08:50','aaa'),(1,'2006-01-17 16:08:50','aaa'),(1,'2006-01-17
16:08:51','aaa'),(1,'2006-01-17 16:08:52','aaa'),(1,'2006-01-17
16:08:52','aaa'),(3,'2006-01-17 16:08:55','aaa'),(3,'2006-01-17
16:08:55','aaa'),(2,'2006-01-17 16:08:58','aaa'),(2,'2006-01-17
16:08:59','aaa'),(2,'2006-01-17 16:08:59','aaa'),(2,'2006-01-17
16:08:59','aaa'),(2,'2006-01-17 16:08:59','aaa'),(2,'2006-01-17
16:09:00','aaa'),(2,'2006-01-17 16:09:00','aaa'),(2,'2006-01-17
16:09:01','aaa'),(2,'2006-01-17 16:09:02','aaa');

CREATE TABLE `usr` (
  `id` int(10) unsigned NOT NULL default '0',
  `login` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `login` (`login`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `usr` VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17942

ChangeSet@1.2384, 2007-01-11 16:23:18+02:00, gkodinov@macbook.gmz +3 -0
  BUG#16590: Optimized does not do right "const" table pre-read
   st_table::const_key_parts member is used in determining if
   certain key has a prefix that is compared to constant(s) in
   the query predicates.
   If there's such prefix the index can be used to get the data
   from the remaining suffix columns in sorted order.
   However if a field is compared to another field from a "const"
   table the const_key_parts is not amended.
   This makes the optimizer unable to detect that the key can be 
   used and add an extra filesort.
   Fixed by updating the const_key_parts after reading in the "const"
   table.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18525

ChangeSet@1.2384, 2007-01-22 12:51:21+02:00, gkodinov@macbook.gmz +3 -0
  BUG#16590: Optimized does not do right "const" table pre-read
   st_table::const_key_parts member is used in determining if
   certain key has a prefix that is compared to constant(s) in
   the query predicates.
   If there's such prefix the index can be used to get the data
   from the remaining suffix columns in sorted order.
   However if a field is compared to another field from a "const"
   table the const_key_parts is not amended.
   This makes the optimizer unable to detect that the key can be 
   used for sorting and adds an extra filesort.
   Fixed by updating const_key_parts after reading in the "const"
   table.

The fix has been pushed to 5.1.16-beta main tree only.

As this is a purely performance problem it we do not plan to fix it in 4.1/5.0

Noted in 5.1.16 changelog.

The optimizer used a filesort rather than a const table read in some
cases when the latter was possible.