1

The following SQL statement, execution time , Somestimes fast,sometimes slow. ※execut in Navicat Premium tab windows Information as below.Do you have any suggestions?

SELECT 
        device.xmbh AS xmbh,
        IFNULL(COUNT(*),0) AS alarmCount
FROM
        t_gj_device_alarm device
INNER JOIN
        t_sb_register_info info 
ON info.xmbh = device.xmbh --number
AND info.device_no = device.device_no --device number
AND info.delete_flag = '0' --delete flag
WHERE device.delete_flag = '0' --delete flag
AND (device.gjjb = 'SPSBWXH' OR device.gjjb = 'SPSBHMBD' ) --Query some device
AND device.gjsj >= '2019-03-04' --start day(one year)
AND device.gjsj <= '2020-03-11' --end day(one year)
GROUP BY device.xmbh
ORDER BY null

execution time:2.6s~6.166s result:1415

data volume:

SELECT count(*) FROM t_gj_device_alarm
result:365027

SELECT count(*) from t_sb_register_info
result:8560


select count(*)/(SELECT count(*) FROM t_gj_device_alarm WHERE delete_flag = 0) as rate
from t_gj_device_alarm device
WHERE
(device.gjjb = 'SPSBWXH' OR device.gjjb = 'SPSBHMBD' )
AND device.gjsj >= '2019-03-04'
AND device.gjsj <= '2020-03-11'
and device.delete_flag = 0
result:0.6448

index information is below:

table:t_gj_device_alarm 
name        column                                      type  indexMethod   sortRule    base
xmbh_device xmbh, device_no, delete_flag, gjjb          NORMAL  BTREE           A       25090               
xmbh_time   xmbh, gjsj, delete_flag                     NORMAL  BTREE           A       376363              
index1      gjsj, gjjb, delete_flag, device_no, xmbh    NORMAL  BTREE           A       376363              
index3      gjjb, xmbh, gjsj                            NORMAL  BTREE           A       376363              
index2      device_no, xmbh, gjsj                       NORMAL  BTREE           A       376363  

table:t_sb_register_info 
name        column                      type  indexMethod   sortRule    base
index1      xmbh, device_no             NORMAL  BTREE           A       8616                
index2      sblxcode, xmbh, device_no   NORMAL  BTREE           A       8616                

Execution plan is below:

id  select_type table   type possible_keys                              key       key_len ref                                               rows    filtered extra
1   SIMPLE      info    ALL  PRIMARY,index1                                                                                                 8616    100      Using where; Using temporary
1   SIMPLE      device  ref  xmbh_device,xmbh_time,index1,index3,index2 xmbh_device 305 zhgd_2.0.info.xmbh,zhgd_2.0.info.device_no,const    16      100      Using index condition; Using where

profiles (One time in a long Execution time)

select state,sum(duration) as total_r,round(100*sum(duration)/(select sum(duration) from information_schema.profiling where query_id = 17),2) as pct_r,count(*) as calls,sum(duration)/count(*) as "R/Call" from information_schema.profiling where query_id = 17 group by state order by total_r desc;

state                   total_r     pct_r   calls   R/Call
Sending data            5.698915    99.77   1       5.698915
statistics              0.012113    0.21    1       0.012113
end                     0.000229    0       2       0.0001145
starting                0.00017     0       1       0.00017
removing tmp table      0.000116    0       1       0.000116
preparing               0.000107    0       1       0.000107
checking permissions    0.000096    0       2       0.000048
Creating tmp table      0.000084    0       1       0.000084
init                    0.000076    0       1       0.000076
freeing items           0.000066    0       1       0.000066
query end               0.000063    0       1       0.000063
closing tables          0.000058    0       1       0.000058
cleaning up             0.000056    0       1       0.000056
optimizing              0.000051    0       1       0.000051
Opening tables          0.00005     0       1       0.00005
executing               0.000038    0       1       0.000038
System lock             0.000037    0       1       0.000037

variable_name:(One time  in a long Execution time)
Created_tmp_disk_tables     0
Created_tmp_files           0
Created_tmp_tables          9
Handler_commit              1
Handler_delete              0
Handler_discover            0
Handler_external_lock       4
Handler_mrr_init            0
Handler_prepare             0
Handler_read_first          1
Handler_read_key            213549
Handler_read_last           0
Handler_read_next           208637
Handler_read_prev           0
Handler_read_rnd            74
Handler_read_rnd_next       11041
Handler_rollback            0
Handler_savepoint           0
Handler_savepoint_rollback  0
Handler_update              204064
Handler_write               2471

profiles (One time  in a short Execution time)
state                   total_r     pct_r   calls   R/Call
Sending data            2.311715    99.87   1       2.311715
statistics              0.002046    0.09    1       0.002046
end                     0.000186    0.01    2       0.000093
starting                0.000166    0.01    1       0.000166
checking permissions    0.000079    0       2       0.0000395
removing tmp table      0.000078    0       1       0.000078
init                    0.000059    0       1       0.000059
preparing               0.000054    0       1       0.000054
Creating tmp table      0.000052    0       1       0.000052
Opening tables          0.000048    0       1       0.000048
freeing items           0.000042    0       1       0.000042
closing tables          0.00004     0       1       0.00004
optimizing              0.000038    0       1       0.000038
cleaning up             0.000035    0       1       0.000035
System lock             0.000031    0       1       0.000031
executing               0.000031    0       1       0.000031
query end               0.000028    0       1       0.000028


variable_name:(One time  in a short Execution time)
Created_tmp_disk_tables     0
Created_tmp_files           2
Created_tmp_tables          9
Handler_commit              1
Handler_delete              0
Handler_discover            0
Handler_external_lock       4
Handler_mrr_init            0
Handler_prepare             0
Handler_read_first          1
Handler_read_key            213464
Handler_read_last           0
Handler_read_next           208637
Handler_read_prev           0
Handler_read_rnd            65
Handler_read_rnd_next       10889
Handler_rollback            0
Handler_savepoint           0
Handler_savepoint_rollback  0
Handler_update              203988
Handler_write               2319

update: table create sql is below :

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_gj_device_alarm
-- ----------------------------
DROP TABLE IF EXISTS `t_gj_device_alarm`;
CREATE TABLE `t_gj_device_alarm`  (
  `alarm_no` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `xmbh` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `device_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `gjsj` datetime(0) NOT NULL COMMENT '',
  `gjjb` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `photofilepath` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `audiofilepath` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `send_msg` tinyint(4) NOT NULL DEFAULT 0 COMMENT '',
  `is_read` tinyint(4) NOT NULL DEFAULT 0 COMMENT '',
  `delete_flag` tinyint(4) NOT NULL DEFAULT 0 COMMENT '',
  `create_time` datetime(0) NOT NULL COMMENT '',
  `create_user` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `modify_time` datetime(0) NOT NULL COMMENT '',
  `modify_user` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  PRIMARY KEY (`alarm_no`) USING BTREE,
  INDEX `xmbh_device`(`xmbh`, `device_no`, `delete_flag`, `gjjb`) USING BTREE,
  INDEX `xmbh_time`(`xmbh`, `gjsj`, `delete_flag`) USING BTREE,
  INDEX `index1`(`gjsj`, `gjjb`, `delete_flag`, `device_no`, `xmbh`) USING BTREE,
  INDEX `index3`(`gjjb`, `xmbh`, `gjsj`) USING BTREE,
  INDEX `index2`(`device_no`, `xmbh`, `gjsj`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_sb_register_info
-- ----------------------------
DROP TABLE IF EXISTS `t_sb_register_info`;
CREATE TABLE `t_sb_register_info`  (
  `device_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `xmbh` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `sbbh` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `sblxcode` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `xpoint` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '0.000000' COMMENT '',
  `ypoint` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '0.000000' COMMENT '',
  `sbxh` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `azrq` date DEFAULT NULL COMMENT '',
  `ccrq` date DEFAULT NULL COMMENT '',
  `qsdw` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `wxdw` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `zrren` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `lxdh` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `lybh` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `sort` int(11) NOT NULL DEFAULT 0 COMMENT '',
  `bz` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `dev_status` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '',
  `device_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '',
  `delete_flag` tinyint(4) NOT NULL DEFAULT 0 COMMENT '',
  `create_time` datetime(0) NOT NULL COMMENT '',
  `create_user` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  `modify_time` datetime(0) NOT NULL COMMENT '',
  `modify_user` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '',
  PRIMARY KEY (`device_no`) USING BTREE,
  INDEX `index1`(`xmbh`, `device_no`) USING BTREE,
  INDEX `index2`(`sblxcode`, `xmbh`, `device_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

iotop capture(One time )

Total DISK READ :       3.93 M/s | Total DISK WRITE :     144.53 K/s
Actual DISK READ:       3.93 M/s | Actual DISK WRITE:     507.82 K/s
  PID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
  281  be/3 root      0.00 B/s    0.00 B/s    0.00 %  3.37 % [jbd2/vda1-8]
  1834 be/4 mysql     527.94 K/s 1256.27 K/s  0.00 %  0.01 % mysqld --~mysql.sock

Is that the reason?

update-03/16

"AND device.gjsj >= '2019-03-04'AND device.gjsj <= '2020-03-11'"

{
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`t_sb_register_info` `info`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "index1",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "scan",
                      "rows": 8407,
                      "cost": 1778.4,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 1778.4,
                "rows_for_plan": 8407,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t_sb_register_info` `info`"
                    ],
                    "table": "`t_gj_device_alarm` `device`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "xmbh_device",
                          "rows": 23,
                          "cost": 193366,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "xmbh_time",
                          "rows": 103,
                          "cost": 865942,
                          "chosen": false
                        },
                        {
                          "access_type": "ref",
                          "index": "index2",
                          "rows": 23,
                          "cost": 193366,
                          "chosen": false
                        },
                        {
                          "access_type": "range",
                          "rows": 136677,
                          "cost": 7.4e8,
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 233812,
                    "rows_for_plan": 193361,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`t_gj_device_alarm` `device`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "xmbh_device",
                      "rows": 2e308,
                      "cost": 0,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "xmbh_time",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "index2",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "range",
                      "rows": 136677,
                      "cost": 88081,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 88081,
                "rows_for_plan": 136677,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t_gj_device_alarm` `device`"
                    ],
                    "table": "`t_sb_register_info` `info`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 136677,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "index1",
                          "rows": 1,
                          "cost": 136677,
                          "chosen": false
                        },
                        {
                          "access_type": "scan",
                          "using_join_cache": true,
                          "rows": 6306,
                          "cost": 1.72e8,
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 252094,
                    "rows_for_plan": 136677,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },

"AND device.gjsj BETWEEN '2019-03-04' and '2020-03-11'"

{
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`t_sb_register_info` `info`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "index1",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "scan",
                      "rows": 8407,
                      "cost": 1778.4,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 1778.4,
                "rows_for_plan": 8407,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t_sb_register_info` `info`"
                    ],
                    "table": "`t_gj_device_alarm` `device`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "index2",
                          "rows": 23,
                          "cost": 193366,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "xmbh_device_1",
                          "rows": 21,
                          "cost": 176551,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "xmbh_time_1",
                          "rows": 111,
                          "cost": 933199,
                          "chosen": false
                        },
                        {
                          "access_type": "range",
                          "rows": 136677,
                          "cost": 7.4e8,
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 213635,
                    "rows_for_plan": 176547,
                    "chosen": true
                  }
                ]
              },
              {
                "plan_prefix": [
                ],
                "table": "`t_gj_device_alarm` `device`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "index2",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "xmbh_device_1",
                      "rows": 2e308,
                      "cost": 0,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "xmbh_time_1",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "range",
                      "rows": 136677,
                      "cost": 88081,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 88081,
                "rows_for_plan": 136677,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t_gj_device_alarm` `device`"
                    ],
                    "table": "`t_sb_register_info` `info`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 136677,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "index1",
                          "rows": 1,
                          "cost": 136677,
                          "chosen": false
                        },
                        {
                          "access_type": "scan",
                          "using_join_cache": true,
                          "rows": 6306,
                          "cost": 1.72e8,
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 252094,
                    "rows_for_plan": 136677,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },          

the reason of not work

"AND device.gjsj >= '2019-03-04'AND device.gjsj <= '2020-03-11'"

          {
            "refine_plan": [
              {
                "table": "`t_sb_register_info` `info`",
                "access_type": "table_scan"
              },
              {
                "table": "`t_gj_device_alarm` `device`",
                "pushed_index_condition": "((`device`.`gjjb` = 'SPSBWXH') or (`device`.`gjjb` = 'SPSBHMBD'))",
                "table_condition_attached": "((`device`.`gjsj` >= '2019-03-04') and (`device`.`gjsj` <= '2020-03-11'))"
              }
            ]
          }

"AND device.gjsj BETWEEN '2019-03-04' and '2020-03-11'"

          {
            "refine_plan": [
              {
                "table": "`t_sb_register_info` `info`",
                "access_type": "table_scan"
              },
              {
                "table": "`t_gj_device_alarm` `device`",
                "pushed_index_condition": "((`device`.`gjjb` = 'SPSBWXH') or (`device`.`gjjb` = 'SPSBHMBD'))",
                "table_condition_attached": "(`device`.`gjsj` between '2019-03-04' and '2020-03-11')"
              }
            ]
          }

update--3/16 After I adjust the index and modify the group by field length, the query time will be reduced to 1-2s. As for the instability, I think it has something to do with the server wa parameter and the network. below is data:

Modification before

    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 160,
                "key_length": 152,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 104857
              }
            }
          }
        ]
      }
    }

device.xmbh varchar(50) -> varchar(30) Modification after

  {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 100,
                "key_length": 92,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 167772
              }
            }
          }
        ]
      }
    }

index1 modify: before:gjsj, gjjb, delete_flag, device_no, xmbh

"considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`t_sb_register_info` `info`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "index1",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "scan",
                      "rows": 8407,
                      "cost": 1778.4,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 1778.4,
                "rows_for_plan": 8407,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t_sb_register_info` `info`"
                    ],
                    "table": "`t_gj_device_alarm` `device`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "index2",
                          "rows": 23,
                          "cost": 193366,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "xmbh_device_1",
                          "rows": 21,
                          "cost": 176551,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "xmbh_time_1",
                          "rows": 111,
                          "cost": 933199,
                          "chosen": false
                        },
                        {
                          "access_type": "range",
                          "rows": 136677,
                          "cost": 7.4e8,
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 213635,
                    "rows_for_plan": 176547,
                    "chosen": true
                  }
                ]
              }

after:xmbh,device_no,gjsj, gjjb, delete_flag


{
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`t_sb_register_info` `info`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "ref",
                      "index": "index1",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "access_type": "scan",
                      "rows": 8395,
                      "cost": 1776,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 1776,
                "rows_for_plan": 8395,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t_sb_register_info` `info`"
                    ],
                    "table": "`t_gj_device_alarm` `device`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "index2",
                          "rows": 23,
                          "cost": 193090,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "xmbh_device_1",
                          "rows": 17,
                          "cost": 142718,
                          "chosen": true
                        },
                        {
                          "access_type": "ref",
                          "index": "xmbh_time_1",
                          "rows": 108,
                          "cost": 906682,
                          "chosen": false
                        },
                        {
                          "access_type": "ref",
                          "index": "index1",
                          "rows": 22,
                          "cost": 20992,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "cause": "covering_index_better_than_full_scan",
                          "chosen": false
                        }
                      ]
                    },
                    "cost_for_plan": 59702,
                    "rows_for_plan": 184690,
                    "chosen": true
                  }
                ]
              },
5
  • MySQL version is“5.6.44”
    – tom.z.t.m
    Commented Mar 13, 2020 at 4:13
  • The explain posted above clearly indicates 8,616 rows were sent to Temporary and with this volume of data, Writes become necessary, especially when you have a PRIMARY Key of VARCHAR (40). Of course, this type of Primary Key causes all the other indexes to need this same VARCHAR (40) stored for every row contributing to your need for space for each key and cpu cycles every time you reference a row. Reconsider your Table Design as it relates to High Performance and the use of short PRIMARY Keys. Commented Mar 13, 2020 at 12:56
  • You may find that your SELECT count(*) FROM t_gj_device_alarm would complete quicker with SELECT COUNT(alarm_no) FROM t_gj_device_alarm. Theory, counting alarm_no (your PRIMARY KEY column) would only need to read your PRIMARY key. If this does conserve time, consider using this technique always counting a PRIMARY key rather than asterisk when working with INNODB tables. Commented Mar 15, 2020 at 17:32
  • Did you time each query twice? (To avoid I/O caching differences.) Do you get the same answer if you leave out info?
    – Rick James
    Commented Mar 16, 2020 at 4:44
  • Thank you very much for your reply. As you said,should redesign my table.
    – tom.z.t.m
    Commented Mar 16, 2020 at 9:29

3 Answers 3

0

I tend to take a rather 'suck-it-and-see' approach to indexing, so I'd remove all indexes except info.index1 and device.index1, and then rearrange those indexes and see what happens. Also, you might want to add delete_flag to the info index.

And, FWIW, I find this easier to read (although it represents only a miniscule improvement, performance-wise)

SELECT device.xmbh 
     , COALESCE(COUNT(*),0) alarmCount
  FROM t_gj_device_alarm device
  JOIN t_sb_register_info info 
    ON info.xmbh = device.xmbh 
   AND info.device_no = device.device_no 
   AND info.delete_flag = 0
 WHERE device.delete_flag = 0
   AND device.gjjb IN('SPSBWXH','SPSBHMBD') 
   AND device.gjsj >= '2019-03-04' 
   AND device.gjsj <= '2020-03-11' 
 GROUP 
    BY device.xmbh
0
0

I would suggest writing the query as:

select avg( device.gjjb in ('SPSBWXH', 'SPSBHMBD') and
            device.gjsj >= '2019-03-04' and
            device.gjsj <= '2020-03-11'
          ) as rate
from t_gj_device_alarm device
where device.delete_flag = 0;

This pretty much requires scanning the table, unless most rows are deleted. In that case, you could create an index on delete_flag, gjjb, gjsj).

2
  • It's best to use an covering index, as the book says. But,business not allowed.
    – tom.z.t.m
    Commented Mar 13, 2020 at 4:31
  • What do you mean by "business not allowed"?
    – Rick James
    Commented Mar 16, 2020 at 4:46
0

You may wish to consider from

AND device.gjsj >= '2019-03-04'
AND device.gjsj <= '2020-03-11'

to

AND device.gjsj BETWEEN '2019-03-04' and '2020-03-11'

for a single range to deal with.

3
  • Thanks for your reply.I mean, the situation as before,Somestimes fast,sometimes slow. But,I noticed that there were some fluctuations in the server(Please refer to update information),Is that the reason?
    – tom.z.t.m
    Commented Mar 13, 2020 at 7:48
  • I do not see your posting of the query you tried with BETWEEN dates or any snippet from your error log. Thanks for posting the 'create table' you used. There is NOTHING like a SHOW CREATE TABLE table_name; for better accurate current table definition and row count. Welcome to SO. Commented Mar 13, 2020 at 13:11
  • I used OPTIMIZER_TRACE,I found "AND device.gjsj BETWEEN '2019-03-04' and '2020-03-11'" ,the reason of not working. see update.With that in mind, I'll reconsider the index settings.
    – tom.z.t.m
    Commented Mar 16, 2020 at 1:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.