Description:
MySQL produces wrong result when select is run on the first column (index order) in a table with composite key and indexes created automatically on composite key.
The same select query works fine when run as a select * query
Running EXPLAIN <query> produces the following under EXTRA column:
Using where; Using index for skip scan; Using filesort
Please check the query details attached to get the complete details of the issue.
How to repeat:
1.Consider the following table:
CREATE TABLE `group_info` (
`UpdateTime` datetime NOT NULL,
`GroupName` varchar(128) NOT NULL,
`username` varchar(128) NOT NULL,
`DisplayName` varchar(256) NOT NULL,
KEY `date_user` (`UpdateTime`, `GroupName`, `username`),
KEY `date_group` (`UpdateTime`, `GroupName`)
);
ENGINE=InnoDB DEFAULT CHARSET=latin1
2. Index Created is of the form:
mysql> SHOW INDEXES FROM group_info;
+----------------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| group_info| 1 | date_user | 1 | UpdateTime | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| group_info| 1 | date_user | 2 | GroupName| A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| group_info| 1 | date_user | 3 | username | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| group_info| 1 | date_group | 1 | UpdateTime | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| group_info| 1 | date_group | 2 | GroupName| A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
+----------------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3. When select is run on this table, the result is missing many records.
Sample Query run:
select UpdateTime from group_info where username='test' and GroupName = 'testgroup1' order by UpdateTime desc limit 1;
EXPLAIN on this select Query:
id: 1
select_type: SIMPLE
table: group_info
partitions: NULL
type: RANGE
possible_keys: date_user
key: date_user
key_len: 135
ref: NULL
rows: 7075102
filtered: 10.00
Extra: Using where; Using index for skip scan; Using filesort
4. Alternate select * query which produces the correct result:
select * from group_info where username='test' and GroupName = 'testgroup1' order by UpdateTime desc limit 1;
