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