MySQL Bugs: #45366: Mysql use wrong index

Bug #45366	Mysql use wrong index
Submitted:	8 Jun 2009 8:41	Modified:	12 Nov 2009 14:41
Reporter:	Andrew Sitnikov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.35, 6.0	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	index primary key

Description:
Mysql use wrong index

How to repeat:
mysql> show variables like 'version'; 
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| version       | 5.1.35-community-log | 
+---------------+----------------------+
1 row in set (0.00 sec)

CREATE TABLE `friends` (
 `user` mediumint(9) NOT NULL DEFAULT '0',
 `friend` mediumint(9) NOT NULL DEFAULT '0',
 `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `respond` tinyint(1) NOT NULL DEFAULT '0',
 `priority` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`user`,`friend`),
 KEY `user` (`user`),
 KEY `friend` (`friend`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> explain SELECT DISTINCT(user) FROM friends WHERE friend = '1766395';
+----+-------------+---------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | friends | index | friend        | PRIMARY | 6       | NULL | 22272635 | Using where; Using index | 
+----+-------------+---------+-------+---------------+---------+---------+------+----------+--------------------------+

mysql> explain SELECT DISTINCT(user) FROM friends USE INDEX(friend) WHERE friend = '1766395';
+----+-------------+---------+------+---------------+--------+---------+-------+------+------------------------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref   | rows | Extra                        |
+----+-------------+---------+------+---------------+--------+---------+-------+------+------------------------------+
|  1 | SIMPLE      | friends | ref  | friend        | friend | 3       | const |    6 | Using where; Using temporary | 
+----+-------------+---------+------+---------------+--------+---------+-------+------+------------------------------+
1 row in set (0.00 sec)

Thank you for the report.

Why do you complain about using PRIMARY while results which you provided shows better results when with using "right" index:

explain SELECT DISTINCT(user) FROM friends WHERE friend = '1766395';
id      1
...
key     PRIMARY
...
Extra   Using where; Using index

explain SELECT DISTINCT(user) FROM friends USE INDEX(friend) WHERE friend = '1766395';
id      1
...
key     friend
...
Extra   Using where; Using temporary

Please explain.

I think I got why you complain: 22272635 rows in case of PRIMARY vs 6 rows in case of index.

Optimizer chooses PRIMARY KEY to avoid filesort. So this case can be considered as a bug only if performance of statement which chooses PRIMARY KEY is slower than second one. Could you please provide execution times of both statements in your environment.

Please compare rows. 

mysql> SELECT DISTINCT(user) FROM friends WHERE friend = '1766395';                           
+---------+
| user    |
+---------+
|   48031 | 
|  375777 | 
|  412477 | 
|  604439 | 
| 1506919 | 
| 1679026 | 
+---------+
6 rows in set (18.86 sec)

# Time: 090608 12:23:11
# User@Host: root[root] @ localhost []
# Query_time: 18.862456  Lock_time: 7.176426 Rows_sent: 6  Rows_examined: 22274279
SET timestamp=1244452991;
SELECT DISTINCT(user) FROM friends WHERE friend = '1766395';

mysql> SELECT DISTINCT(user) FROM friends USE INDEX (friend) WHERE friend = '1766395';
+---------+
| user    |
+---------+
|  604439 | 
|  375777 | 
|   48031 | 
| 1679026 | 
| 1506919 | 
|  412477 | 
+---------+
6 rows in set (0.00 sec)

Thank you for the feedback.

Verified as described:

mysql> select count(*) FROM friends;
+----------+
| count(*) |
+----------+
|    10000 | 
+----------+
1 row in set (0.07 sec)

mysql> select count(distinct user) FROM friends  WHERE friend = '-8216584';
+----------------------+
| count(distinct user) |
+----------------------+
|                    1 | 
+----------------------+
1 row in set (0.15 sec)

mysql> SELECT DISTINCT(user) FROM friends WHERE friend = '-8216584';
+---------+
| user    |
+---------+
| 5610093 | 
+---------+
1 row in set (0.95 sec)

mysql> SELECT DISTINCT(user) FROM friends USE INDEX(friend) WHERE friend = '-8216584';
+---------+
| user    |
+---------+
| 5610093 | 
+---------+
1 row in set (0.12 sec)

dump to recreate the problem

Attachment: bug45366.sql (text/x-sql), 470.17 KiB.

we encounter same problem:
EXPLAIN SELECT DISTINCT annotation0.af AS col_0_0_
FROM  annotation0
WHERE (
(
annotation0_.id
IN ( 375446405, 375446406, 375446410, 375446420, 375446422, 375446425, 375446428, 375446429, 375446431, 375446432, 375446433, 375446434, 375446435, 375446436, 375446437, 375446438, 375446439, 375446446, 375446448, 375446449 )
)
)

id=primary index

on 5.1

wrong index was selected (applicable on annotation0.af) leading to
18 mio examined rows in 10 secs

on 5.0 

primary key index was used (20 rows) 0.01 secs

Actually I can not repeat this with Sveta's test case, neither on 5.1.42 from bzr:

77-52-7-73:5.1 openxs$ bin/mysql -uroot test < ~/Downloads/bug45366.sql 
77-52-7-73:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> explain SELECT DISTINCT(user) FROM friends WHERE friend = '-8216584';
+----+-------------+---------+------+---------------+--------+---------+-------+------+------------------------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref   | rows | Extra                        |
+----+-------------+---------+------+---------------+--------+---------+-------+------+------------------------------+
|  1 | SIMPLE      | friends | ref  | friend        | friend | 3       | const |    1 | Using where; Using temporary |
+----+-------------+---------+------+---------------+--------+---------+-------+------+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT(user) FROM friends WHERE friend = '-8216584';
+---------+
| user    |
+---------+
| 5610093 |
+---------+
1 row in set (0.00 sec)

not on 6.0.14 from bzr. So, looks like the problem is fixed by some other bugfix.

ok, we upgraded server from 5.1.35 to 5.1.40. 
the correct index is used now
the bug seems to be resolved in 5.1.40