Bug #42457 The Optimizer ignores file sort with order by datecolumn clause
Submitted: 29 Jan 2009 15:42 Modified: 29 Jan 2009 17:02
Reporter: Nikolai Ikhalainen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.1.30 OS:Any (precompiled sources from mysql.com)
Assigned to:

[29 Jan 2009 15:42] Nikolai Ikhalainen
Description:
The bug is appears after 5.1.24.
Tested on Solaris 10 & ubuntu server 8.10
mysql> explain extended select search_str, make_date FROM user_search_log WHERE user_id='398672';
+----+-------------+-----------------+------+-----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table           | type | possible_keys   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------------+------+-----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_search_log | ref  | PRIMARY,user_id | PRIMARY | 4       | const |   21 |   100.00 |       | 
+----+-------------+-----------------+------+-----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain extended select search_str, make_date FROM user_search_log WHERE user_id='398672' ORDER BY make_date;
+----+-------------+-----------------+-------+-----------------+-----------+---------+------+--------+----------+--------------------------+
| id | select_type | table           | type  | possible_keys   | key       | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-----------------+-------+-----------------+-----------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | user_search_log | index | PRIMARY,user_id | make_date | 4       | NULL | 623145 |     0.00 | Using where; Using index | 
+----+-------------+-----------------+-------+-----------------+-----------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

How to repeat:
create simple table
CREATE TABLE `user_search_log` (
  `user_id` int(5) unsigned NOT NULL,
  `search_str` varchar(255) NOT NULL,
  `make_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`search_str`),
  KEY `user_id` (`user_id`),
  KEY `make_date` (`make_date`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
fill the table with aprox 600k rows.
withh distribution like this:
              Cardinality
user_id     | 136175
search_str  | 544702
user_id     | 272351
make_date   | 544702
[29 Jan 2009 16:43] Valerii Kravchuk
Looks like a duplicate of bug #36259. Please, check.
[29 Jan 2009 16:55] Nikolai Ikhalainen
Sorry, for cross post. It's definetly related.
[29 Jan 2009 17:02] Valerii Kravchuk
Duplicate of bug #36259.