Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.14
-
None
Description
analyze table hostname_max
|
|
|
explain SELECT DISTINCT `hostname_max` FROM `global_query_review_history`;
|
+------+-------------+-----------------------------+-------+---------------+--------------+---------+------+---------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------------------------+-------+---------------+--------------+---------+------+---------+-------------+
|
| 1 | SIMPLE | global_query_review_history | index | NULL | hostname_max | 218 | NULL | 3167247 | Using index |
|
+------+-------------+-----------------------------+-------+---------------+--------------+---------+------+---------+-------------+
|
1 row in set (0.01 sec)
|
work around is :
create index hostname_max1 on global_query_review_history (hostname_max);
|
[11/14/14 4:19:25 PM] Nicolas Payart: slow_query_log=# explain SELECT DISTINCT `hostname_max` FROM `global_query_review_history`;
|
+------+-------------+-----------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-----------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | global_query_review_history | range | NULL | hostname_max1 | 194 | NULL | 27 | Using index for group-by |
|
+------+-------------+-----------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
1 row in set (0.00 sec)
|
was ok on MySQL 5.6 (without index hostname_max1)
slow_query_log=# show table status like 'global_query_review_history'\G
|
*************************** 1. row ***************************
|
Name: global_query_review_history
|
Engine: InnoDB
|
Version: 10
|
Row_format: Compact
|
Rows: 3167374
|
Avg_row_length: 1091
|
Data_length: 3458203648
|
Max_data_length: 0
|
Index_length: 335953920
|
Data_free: 17825792
|
Auto_increment: NULL
|
Create_time: 2014-11-14 16:18:52
|
Update_time: NULL
|
Check_time: NULL
|
Collation: utf8_general_ci
|
Checksum: NULL
|
Create_options:
|
Comment:
|
1 row in set (0.00 sec)
|
CREATE TABLE `global_query_review_history` (
|
`hostname_max` varchar(64) NOT NULL,
|
`db_max` varchar(64) DEFAULT NULL,
|
`checksum` bigint(20) unsigned NOT NULL,
|
`sample` longtext NOT NULL,
|
`ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
`ts_cnt` float DEFAULT NULL,
|
`Query_time_sum` float DEFAULT NULL,
|
`Query_time_min` float DEFAULT NULL,
|
`Query_time_max` float DEFAULT NULL,
|
`Query_time_pct_95` float DEFAULT NULL,
|
`Query_time_stddev` float DEFAULT NULL,
|
`Query_time_median` float DEFAULT NULL,
|
`Lock_time_sum` float DEFAULT NULL,
|
`Lock_time_min` float DEFAULT NULL,
|
`Lock_time_max` float DEFAULT NULL,
|
`Lock_time_pct_95` float DEFAULT NULL,
|
`Lock_time_stddev` float DEFAULT NULL,
|
`Lock_time_median` float DEFAULT NULL,
|
`Rows_sent_sum` float DEFAULT NULL,
|
`Rows_sent_min` float DEFAULT NULL,
|
`Rows_sent_max` float DEFAULT NULL,
|
`Rows_sent_pct_95` float DEFAULT NULL,
|
`Rows_sent_stddev` float DEFAULT NULL,
|
`Rows_sent_median` float DEFAULT NULL,
|
`Rows_examined_sum` float DEFAULT NULL,
|
`Rows_examined_min` float DEFAULT NULL,
|
`Rows_examined_max` float DEFAULT NULL,
|
`Rows_examined_pct_95` float DEFAULT NULL,
|
`Rows_examined_stddev` float DEFAULT NULL,
|
`Rows_examined_median` float DEFAULT NULL,
|
`Rows_affected_sum` float DEFAULT NULL,
|
`Rows_affected_min` float DEFAULT NULL,
|
`Rows_affected_max` float DEFAULT NULL,
|
`Rows_affected_pct_95` float DEFAULT NULL,
|
`Rows_affected_stddev` float DEFAULT NULL,
|
`Rows_affected_median` float DEFAULT NULL,
|
`Rows_read_sum` float DEFAULT NULL,
|
`Rows_read_min` float DEFAULT NULL,
|
`Rows_read_max` float DEFAULT NULL,
|
`Rows_read_pct_95` float DEFAULT NULL,
|
`Rows_read_stddev` float DEFAULT NULL,
|
`Rows_read_median` float DEFAULT NULL,
|
`Merge_passes_sum` float DEFAULT NULL,
|
`Merge_passes_min` float DEFAULT NULL,
|
`Merge_passes_max` float DEFAULT NULL,
|
`Merge_passes_pct_95` float DEFAULT NULL,
|
`Merge_passes_stddev` float DEFAULT NULL,
|
`Merge_passes_median` float DEFAULT NULL,
|
`InnoDB_IO_r_ops_min` float DEFAULT NULL,
|
`InnoDB_IO_r_ops_max` float DEFAULT NULL,
|
`InnoDB_IO_r_ops_pct_95` float DEFAULT NULL,
|
`InnoDB_IO_r_bytes_pct_95` float DEFAULT NULL,
|
`InnoDB_IO_r_bytes_stddev` float DEFAULT NULL,
|
`InnoDB_IO_r_bytes_median` float DEFAULT NULL,
|
`InnoDB_IO_r_wait_min` float DEFAULT NULL,
|
`InnoDB_IO_r_wait_max` float DEFAULT NULL,
|
`InnoDB_IO_r_wait_pct_95` float DEFAULT NULL,
|
`InnoDB_IO_r_ops_stddev` float DEFAULT NULL,
|
`InnoDB_IO_r_ops_median` float DEFAULT NULL,
|
`InnoDB_IO_r_bytes_min` float DEFAULT NULL,
|
`InnoDB_IO_r_bytes_max` float DEFAULT NULL,
|
`InnoDB_IO_r_wait_stddev` float DEFAULT NULL,
|
`InnoDB_IO_r_wait_median` float DEFAULT NULL,
|
`InnoDB_rec_lock_wait_min` float DEFAULT NULL,
|
`InnoDB_rec_lock_wait_max` float DEFAULT NULL,
|
`InnoDB_rec_lock_wait_pct_95` float DEFAULT NULL,
|
`InnoDB_rec_lock_wait_stddev` float DEFAULT NULL,
|
`InnoDB_rec_lock_wait_median` float DEFAULT NULL,
|
`InnoDB_queue_wait_min` float DEFAULT NULL,
|
`InnoDB_queue_wait_max` float DEFAULT NULL,
|
`InnoDB_queue_wait_pct_95` float DEFAULT NULL,
|
`InnoDB_queue_wait_stddev` float DEFAULT NULL,
|
`InnoDB_queue_wait_median` float DEFAULT NULL,
|
`InnoDB_pages_distinct_min` float DEFAULT NULL,
|
`InnoDB_pages_distinct_max` float DEFAULT NULL,
|
`InnoDB_pages_distinct_pct_95` float DEFAULT NULL,
|
`InnoDB_pages_distinct_stddev` float DEFAULT NULL,
|
`InnoDB_pages_distinct_median` float DEFAULT NULL,
|
`QC_Hit_cnt` float DEFAULT NULL,
|
`QC_Hit_sum` float DEFAULT NULL,
|
`Full_scan_cnt` float DEFAULT NULL,
|
`Full_scan_sum` float DEFAULT NULL,
|
`Full_join_cnt` float DEFAULT NULL,
|
`Full_join_sum` float DEFAULT NULL,
|
`Tmp_table_cnt` float DEFAULT NULL,
|
`Tmp_table_sum` float DEFAULT NULL,
|
`Filesort_cnt` float DEFAULT NULL,
|
`Filesort_sum` float DEFAULT NULL,
|
`Tmp_table_on_disk_cnt` float DEFAULT NULL,
|
`Tmp_table_on_disk_sum` float DEFAULT NULL,
|
`Filesort_on_disk_cnt` float DEFAULT NULL,
|
`Filesort_on_disk_sum` float DEFAULT NULL,
|
`Bytes_sum` float DEFAULT NULL,
|
`Bytes_min` float DEFAULT NULL,
|
`Bytes_max` float DEFAULT NULL,
|
`Bytes_pct_95` float DEFAULT NULL,
|
`Bytes_stddev` float DEFAULT NULL,
|
`Bytes_median` float DEFAULT NULL,
|
UNIQUE KEY `hostname_max` (`hostname_max`,`checksum`,`ts_min`,`ts_max`),
|
KEY `ts_min` (`ts_min`),
|
KEY `checksum` (`checksum`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
|
|
Please backport MDEV-6657 as soon as possible in 10.0
Attachments
Issue Links
- relates to
-
MDEV-4120 UNIQUE indexes should not be considered for loose index scan
-
- Closed
-
Activity
I suppose if psergey pushed the change for MDEV-6657 into 10.1 only, it was too intrusive for a post-GA version; but I'll leave it to him to decide whether it's possible to backport it.
After discussion on IRC with Sergei , it looks like it may be not related to 6657 but ignoring Using index for group-by on multi part keys , i sent sergei by email a dump to reproduce, do you need it for test case as well ?
MariaDB 5.5.41:
MariaDB [j4]> explain SELECT DISTINCT `hostname_max` FROM `global_query_review_test` ;
|
+------+-------------+--------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | global_query_review_test | range | NULL | hostname_max1 | 194 | NULL | 3 | Using index for group-by |
|
+------+-------------+--------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
MariaDB [j4]> explain SELECT DISTINCT `hostname_max` FROM `global_query_review_test` ignore index(hostname_max1);
|
+------+-------------+--------------------------+-------+---------------+--------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+--------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | global_query_review_test | range | NULL | hostname_max | 194 | NULL | 19 | Using index for group-by |
|
+------+-------------+--------------------------+-------+---------------+--------------+---------+------+------+--------------------------+
|
MariaDB 10.0.15:
MariaDB [j4]> explain SELECT DISTINCT `hostname_max` FROM `global_query_review_test`;
|
+------+-------------+--------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | global_query_review_test | range | NULL | hostname_max1 | 194 | NULL | 5 | Using index for group-by |
|
+------+-------------+--------------------------+-------+---------------+---------------+---------+------+------+--------------------------+
|
MariaDB [j4]> explain SELECT DISTINCT `hostname_max` FROM `global_query_review_test` ignore index(hostname_max1);
|
+------+-------------+--------------------------+-------+---------------+--------------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------------+-------+---------------+--------------+---------+------+-------+-------------+
|
| 1 | SIMPLE | global_query_review_test | index | NULL | hostname_max | 218 | NULL | 23478 | Using index |
|
+------+-------------+--------------------------+-------+---------------+--------------+---------+------+-------+-------------+
|
The change is caused by: MDEV-4120: UNIQUE indexes should not be considered for loose index scan
Looking at MDEV-4120 and not fully understanding it.
It is apparent that one shouldn't attempt to use Loose Index Scan over a full unique index.
If index tuples are unique, jumping forward brings no benefits.
However, prefix of unique index is not unique. It may have a very low cardinality, and it will make perfrect sense to use Loose Index Scan.
.. on the other hand, if the index tuples are unique, why are we considering to use or not use loose index scan? We should be able to figure out that grouping is not needed at all.
I mean, Loose Scan is applied in two cases:
1. DISTINCT which was converted into GROUP BY
SELECT DISTINCT t.keypart1, t.keypart2, .... FROM t1 ...
|
2. GROUP BY with MIN/MAX
SELECT MIN(t.keypart2) FROM t1 ... GROUP BY t.keypart1
|
The testcase for MDEV-4120 is like #2:
create table t4 as select distinct a1, a2, b, c from t1;
|
alter table t4 add unique index idxt4 (a1, a2, b, c);
|
|
|
explain
|
select a1, a2, b, min(c) from t4 group by a1, a2, b;
|
Here, one could argue that Loose Scan can still be a good strategy when (a1,a2,b) have low cardinality, while `c` has high cardinality.
(we can't check whether the testcase for MDEV-4120 demonstrates a speedup, because table t1 has just 128 rows).
If I have a
UNIQUE KEY `col1` (`col1`,`col2`,`col3`,`col4`)
|
and run a query:
explain select distinct col1,col2,col3,col4 from tpk1;
|
I see that Loose Scan is considered, despite that the index is unique. The cost
numbers prevent it from being chosen. The optimizer choses:
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | tpk1 | index | NULL | col1 | 20 | NULL | 9990 | Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
If I adjust the cost numbers in so that loose index scan is chosen, it is used:
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | tpk1 | range | NULL | col1 | 20 | NULL | 9991 | Using index for group-by |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
The same goes for "SELECT DISTINCT pk_part1, ... pk_partN". Perhaps, this is what MDEV-4120 was trying to prevent?
The cost of lose scan should be evaluated cost * (cardinality at the key_len /records) * cost_loose_scan_complexity /cost_index_scan_complexity
[stephane], your formula is not clear.
"cost of lose scan"= ... * cost_lose_scan_complexity ...
Is this formula recursive? if not, what is cost_lose_scan_complexity and how is it different from cost of loose scan?
Viable options so far
- Undo the fix for
MDEV-4120completely - Change the fix for
MDEV-4120to disable Loose Index Scan only when doing "SELECT DISTINCT unique_key_col1 ,... unique_key_colN".
I am not sure if the second one is meaningful. I was not able to come up with a SELECT DISTINCT statement where cost of loose scan is smaller than the cost of index scan. Too bad we don't know the original testcase for MDEV-4120.
Implemented the second option. Fix pushed into 10.0 tree.
While debugging, also saw that Loose Index Scan cost formulas are poor - it can chose to use Loose Index Scan with group sizes as low as 4 rows, which seems to be wrong (at least, in some cases).
show profiles;| Query_ID | Duration | Query |