Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 5.5.39, 10.0.11
-
Fix Version/s: 10.1.1
-
Component/s: None
-
Labels:
-
Environment: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..
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
There are issues with the above approach. If we solve it the simple way, we will need to allocate arrays to store potential ref(const) costs for each index in each used table
We could do some pre-processing to make the set of potentially usable indexes smaller. That would require touching a lot of optimizer code.
How about considering an alternative: re-caculate the costs in test_if_cheaper_ordering(), as requested.
In order to re-calculate the costs, we need to tell whether we're caclulating
ref(const) costs or range access costs.
table->quick_rows/quick_costs can provide range access costs and #rows.
As for ref access, we need to be able to detect whether ref(const) was
applicable and what its cost was.
Note that potential range access over one interval is not the same as
ref(const) access. consider
t.key IN (10, 20) AND t.key > 15
this is a 1-interval range access but ref(const) is not considered for this
query.
Q: Could we reuse best_access_path()?
A: Hardly. We need to consider ref(const) over one specific index. For range access, we
need to consider only one specific index (and not just the best possible range access
like it is done in best_access_path.
Committed a patch: http://lists.askmonty.org/pipermail/commits/2014-July/006282.html
The patch is going through the QA.
See updates at MDEV-6454. After some input from the QA, the patch was modified. It is now being tested again.
One problem with this fix is that it is getting too big for 10.0. It is ok to include in 10.1, but pushing this change into 10.0 may cause an unwanted regression for somebody.
The report doesn't specify the dataset (types of columns not known, and the query that fills the tables uses information_schema.columns whose contents are different on every database). Seunguck Lee, if you still have tb_test1 and tb_test2 tables, could you please attach a mysqldump of these to make sure we're looking at the same problem?