Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.39, 10.0.11
-
None
-
Linux matt001 2.6.18-308.el5 #1 SMP Tue Feb 21 20:06:06 EST 2012 x86_64 x86_64 x86_64 GNU/Linux
Description
I created two test table. The only difference between them is the order of secondary index.
And I explained same query on two table after insert same test data in two tables.
But query execution plan is different.
<< First see the below test case >>
Here's the problem.
If "ix_fd1_fd2" index is UNIQUE, optimizer never use "ix_fd_fdpk" index in this case. Becuase UNIQUE index will be positioned before NORMAL secondary index like below table, even though it is added later.
CREATE TABLE `tb_test1` ( |
...
|
PRIMARY KEY (`fd_pk`), |
UNIQUE KEY `ux_fd1_fd2` (`fd1`,`fd2`), |
KEY `ix_fd_fdpk` (`fd1`,`fd_pk`), |
KEY `ix_fd1_fd2` (`fd1`,`fd2`), |
);
|
How can I make optimizer use ix_fd1_fdpk to avoid filesort operation (Without index hint ^^) ?
And is this expected ?
-- TEST CASE ------------------------
|
// Prepare some test data and table |
MariaDB [test]> INSERT INTO tb_test1 SELECT NULL, ORDINAL_POSITION, IFNULL(CHARACTER_MAXIMUM_LENGTH, ROUND(RAND()*10000)), NOW(), 'dummy' FROM information_schema.COLUMNS; |
Query OK, 1886 rows affected (0.26 sec) |
Records: 1886 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> INSERT INTO tb_test2 SELECT NULL, ORDINAL_POSITION, IFNULL(CHARACTER_MAXIMUM_LENGTH, ROUND(RAND()*10000)), NOW(), 'dummy' FROM information_schema.COLUMNS; |
Query OK, 1886 rows affected (0.23 sec) |
Records: 1886 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> alter table tb_test1 engine=innodb; |
Query OK, 0 rows affected (0.79 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> alter table tb_test2 engine=innodb; |
Query OK, 0 rows affected (0.45 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
MariaDB [test]> show table status like 'tb_test1'\G |
*************************** 1. row ***************************
|
Name: tb_test1 |
Engine: InnoDB
|
Version: 10
|
Row_format: Compact
|
Rows: 1886 |
Avg_row_length: 78
|
Data_length: 147456
|
Max_data_length: 0
|
Index_length: 147456
|
Data_free: 0
|
Auto_increment: 2048
|
Create_time: 2014-06-25 08:29:11
|
Update_time: NULL |
Check_time: NULL |
Collation: utf8_general_ci
|
Checksum: NULL |
Create_options:
|
Comment:
|
1 row in set (0.00 sec) |
MariaDB [test]> show table status like 'tb_test2'\G |
*************************** 1. row ***************************
|
Name: tb_test2 |
Engine: InnoDB
|
Version: 10
|
Row_format: Compact
|
Rows: 1886 |
Avg_row_length: 78
|
Data_length: 147456
|
Max_data_length: 0
|
Index_length: 147456
|
Data_free: 0
|
Auto_increment: 2048
|
Create_time: 2014-06-25 08:29:13
|
Update_time: NULL |
Check_time: NULL |
Collation: utf8_general_ci
|
Checksum: NULL |
Create_options:
|
Comment:
|
1 row in set (0.00 sec) |
MariaDB [test]> show index from tb_test1; |
+----------+------------+------------+--------------+-------------+-----------+-------------+.. |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |.. |
+----------+------------+------------+--------------+-------------+-----------+-------------+.. |
| tb_test1 | 0 | PRIMARY | 1 | fd_pk | A | 1886 |.. |
| tb_test1 | 1 | ix_fd_fdpk | 1 | fd1 | A | 157 |..
|
| tb_test1 | 1 | ix_fd_fdpk | 2 | fd_pk | A | 1886 |..
|
| tb_test1 | 1 | ix_fd1_fd2 | 1 | fd1 | A | 157 |..
|
| tb_test1 | 1 | ix_fd1_fd2 | 2 | fd2 | A | 1886 |..
|
+----------+------------+------------+--------------+-------------+-----------+-------------+.. |
5 rows in set (0.00 sec) |
MariaDB [test]> show index from tb_test2; |
+----------+------------+------------+--------------+-------------+-----------+-------------+.. |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |.. |
+----------+------------+------------+--------------+-------------+-----------+-------------+.. |
| tb_test2 | 0 | PRIMARY | 1 | fd_pk | A | 1886 |.. |
| tb_test2 | 1 | ix_fd1_fd2 | 1 | fd1 | A | 157 |..
|
| tb_test2 | 1 | ix_fd1_fd2 | 2 | fd2 | A | 1886 |..
|
| tb_test2 | 1 | ix_fd_fdpk | 1 | fd1 | A | 157 |..
|
| tb_test2 | 1 | ix_fd_fdpk | 2 | fd_pk | A | 1886 |..
|
+----------+------------+------------+--------------+-------------+-----------+-------------+.. |
5 rows in set (0.00 sec) |
MariaDB [test]> explain select * from tb_test1 where fd1=1 order by fd_pk limit 1000; |
+------+-------------+----------+------+-----------------------+------------+---------+-------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+----------+------+-----------------------+------------+---------+-------+------+-------------+ |
| 1 | SIMPLE | tb_test1 | ref | ix_fd_fdpk,ix_fd1_fd2 | ix_fd_fdpk | 8 | const | 172 | Using where | |
+------+-------------+----------+------+-----------------------+------------+---------+-------+------+-------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> explain select * from tb_test2 where fd1=1 order by fd_pk limit 1000; |
+------+-------------+----------+------+-----------------------+------------+---------+-------+------+-----------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+----------+------+-----------------------+------------+---------+-------+------+-----------------------------+ |
| 1 | SIMPLE | tb_test2 | ref | ix_fd1_fd2,ix_fd_fdpk | ix_fd1_fd2 | 8 | const | 172 | Using where; Using filesort | |
+------+-------------+----------+------+-----------------------+------------+---------+-------+------+-----------------------------+ |
1 row in set (0.00 sec) |
==> The last query doesn't need filesort operation when they use ix_fd1_fdpk index. But not..
