Box Anemometer

Query 2007580622683486090

First Seen: 2012-04-10 14:39:34
Last Seen: 2012-04-10 15:59:56
Fingerprint
select unix_timestamp(substring(ts_min,?,?)) as `hour_ts`, sum(ts_cnt) as `ts_cnt` from `global_query_review` as `fact` join `global_query_review_history` as `dimension` using (`checksum`) where dimension.ts_min >= ? and dimension.ts_min <= ? group by hour_ts order by hour_ts
Last Sample on host mysql.geoffreyanderson.net at 2012-04-10 15:59:56
More Samples 
SELECT unix_timestamp(substring(ts_min,1,13)) AS `hour_ts`,
  SUM(ts_cnt) AS `ts_cnt`
 FROM `global_query_review` AS `fact` 
 JOIN `global_query_review_history` AS `dimension` USING (`checksum`) 
 WHERE dimension.ts_min >= "2012-04-03 15:53:54"
  AND dimension.ts_min <= "2012-04-10 15:59:54"
 GROUP BY hour_ts 
 ORDER BY hour_ts
Explain Plan 
+----+-------------+-----------+------------+-------+------------------------------+----------+---------+------------------------------+------+----------+----------------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys                | key      | key_len | ref                          | rows | filtered | Extra                                        |
+----+-------------+-----------+------------+-------+------------------------------+----------+---------+------------------------------+------+----------+----------------------------------------------+
| 1  | SIMPLE      | fact      |            | index | PRIMARY                      | PRIMARY  | 8       |                              | 146  | 100.00   | Using index; Using temporary; Using filesort |
+----+-------------+-----------+------------+-------+------------------------------+----------+---------+------------------------------+------+----------+----------------------------------------------+
| 1  | SIMPLE      | dimension |            | ref   | hostname_max,ts_min,checksum | checksum | 8       | slow_query_log.fact.checksum | 2    | 68.97    | Using index condition                        |
+----+-------------+-----------+------------+-------+------------------------------+----------+---------+------------------------------+------+----------+----------------------------------------------+
Visual Explain Plan 
Filesort
+- TEMPORARY
   table          temporary(fact,dimension)
   +- JOIN
      +- Index lookup
      |  key            dimension->checksum
      |  possible_keys  hostname_max,ts_min,checksum
      |  key_len        8
      |  ref            slow_query_log.fact.checksum
      |  rows           2
      +- Index scan
         key            fact->PRIMARY
         possible_keys  PRIMARY
         key_len        8
         rows           146
Create Table Statements 
CREATE TABLE `global_query_review` (
  `checksum` bigint unsigned NOT NULL,
  `fingerprint` text NOT NULL,
  `sample` text NOT NULL,
  `first_seen` datetime DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `reviewed_by` varchar(20) DEFAULT NULL,
  `reviewed_on` datetime DEFAULT NULL,
  `comments` text,
  `reviewed_status` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`checksum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

CREATE TABLE `global_query_review_history` (
  `hostname_max` varchar(64) NOT NULL,
  `db_max` varchar(64) DEFAULT NULL,
  `checksum` bigint unsigned NOT NULL,
  `sample` text 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,
  `Disk_tmp_table_cnt` float DEFAULT NULL,
  `Disk_tmp_table_sum` float DEFAULT NULL,
  `Filesort_cnt` float DEFAULT NULL,
  `Filesort_sum` float DEFAULT NULL,
  `Disk_filesort_cnt` float DEFAULT NULL,
  `Disk_filesort_sum` 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=utf8mb3
Table Status 
                Name : global_query_review
              Engine : InnoDB
             Version : 10
          Row_format : Dynamic
                Rows : 146
      Avg_row_length : 1009
         Data_length : 147456
     Max_data_length : 0
        Index_length : 0
           Data_free : 0
      Auto_increment : 
         Create_time : 2025-01-14 23:52:34
         Update_time : 
          Check_time : 
           Collation : utf8mb3_general_ci
            Checksum : 
      Create_options : 
             Comment : 


                Name : global_query_review_history
              Engine : InnoDB
             Version : 10
          Row_format : Dynamic
                Rows : 377
      Avg_row_length : 782
         Data_length : 294912
     Max_data_length : 0
        Index_length : 98304
           Data_free : 0
      Auto_increment : 
         Create_time : 2025-01-14 23:52:34
         Update_time : 
          Check_time : 
           Collation : utf8mb3_general_ci
            Checksum : 
      Create_options : 
             Comment :
comments
Status:

Reviewed By:

90 Day History

Show Raw SQL Permalink 
SELECT DATE(ts_min) AS `date`,
  ROUND(SUM(Rows_examined_sum)/SUM(rows_sent_sum),2) AS `index_ratio`,
  SUM(Query_time_sum) / SUM(ts_cnt) AS `query_time_avg`,
  ROUND(SUM(Rows_sent_sum)/SUM(ts_cnt),0) AS `rows_sent_avg`,
  SUM(ts_cnt) AS `ts_cnt`,
  SUM(Query_time_sum) AS `Query_time_sum`,
  SUM(Lock_time_sum) AS `Lock_time_sum`,
  SUM(Rows_sent_sum) AS `Rows_sent_sum`,
  SUM(Rows_examined_sum) AS `Rows_examined_sum`,
  SUM(Tmp_table_sum) AS `Tmp_table_sum`,
  SUM(Filesort_sum) AS `Filesort_sum`,
  SUM(Full_scan_sum) AS `Full_scan_sum`
 FROM `global_query_review` AS `fact` 
 JOIN `global_query_review_history` AS `dimension` USING (`checksum`) 
 WHERE checksum = "2007580622683486090"
  AND dimension.ts_min >= "2026-02-23 17:36:42"
  AND dimension.ts_min <= "2026-05-24 17:36:42"
 GROUP BY date 
 ORDER BY date DESC 
 LIMIT 90
date index_ratio query_time_avg rows_sent_avg ts_cnt Query_time_sum Lock_time_sum Rows_sent_sum Rows_examined_sum Tmp_table_sum Filesort_sum Full_scan_sum