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
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