Bug #16590 Optimized does not do right "const" table pre-read
Submitted: 18 Jan 2006 0:16 Modified: 13 Feb 2007 19:07
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1 OS:Any (all)
Assigned to: Georgi Kodinov CPU Architecture:Any

[18 Jan 2006 0:16] Peter Zaitsev
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');
[11 Jan 2007 14:23] Bugs System
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.
[22 Jan 2007 10:52] Bugs System
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.
[30 Jan 2007 22:12] Igor Babaev
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
[13 Feb 2007 19:07] Paul DuBois
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.