Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6
-
Related to performance
-
temporary tables with potentially long generated keys may be faster
-
Q3/2025 Maintenance
Description
Hit this when looking at MDEV-39648.
create table t1 ( |
`CTC_DATABASE` varchar(128) NOT NULL, |
`CTC_TABLE` varchar(256) DEFAULT NULL, |
`CTC_PARTITION` varchar(767) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; |
insert into t1 |
select
|
concat('db-', seq), |
concat('tbl-', seq), |
concat('part-', seq) |
from |
seq_1_to_1000;
|
I use LIMIT to prevent merging, but this can be other construct that prevents merging, too.
explain
|
select * from |
(select * from t1 limit 1000) A, |
(select * from t1 limit 1000) B |
where |
A.ctc_database=B.ctc_database and A.ctc_table=B.ctc_table and A.ctc_partition=B.ctc_partition; |
This doesn't use derived_with keys:
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1000 | |
|
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 1000 | Using where; Using join buffer (flat, BNL join) |
|
| 3 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 9934 | |
|
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 9934 | |
|
+------+-------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
|
while this would use it:
explain
|
select * from |
(select * from t1 limit 1000) A, |
(select * from t1 limit 1000) B |
where |
A.ctc_database=B.ctc_database and A.ctc_table=B.ctc_table; |
+------+-------------+------------+------+---------------+------+---------+----------------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+----------------------------+------+-------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 389 | A.CTC_DATABASE,A.CTC_TABLE | 10 | |
|
| 3 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 9934 | |
|
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 9934 | |
|
+------+-------------+------------+------+---------------+------+---------+----------------------------+------+-------------+
|
Attachments
Issue Links
- relates to
-
MDEV-37201 Pick the most efficient subset of keys there are too many key parts
-
- Open
-
-
-
- In Progress
-