0

I am using Mysql 5.7 I have table which having 7006500 rows. My query performing group by and fetching row which has maximum count with each group on column which is already indexed but still takes time for execution. Below is my query,execution plan and table schema.

Table Schema

        CREATE TABLE templog (
          id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
          userid bigint(12) unsigned NOT NULL,
          type tinyint(3) NOT NULL DEFAULT '0',
          os tinyint(4) NOT NULL DEFAULT '0',
          day date DEFAULT NULL,
          activetime smallint(5) unsigned NOT NULL DEFAULT '0',
          createdat datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
          timegroupid tinyint(4) NOT NULL DEFAULT '0',
          PRIMARY KEY (`id`),
          KEY templog_type_IDX (`type`,`day`,`userid`,`timegroupid`) USING BTREE
        ) ENGINE=InnoDB AUTO_INCREMENT=7006500 DEFAULT CHARSET=utf8;

My Query:-

            SELECT SQL_NO_CACHE y.userid, y.timegroupid as besttime,y.cnt
           FROM(
        SELECT @row_number:=CASE WHEN @userid=x.userid THEN @row_number+1 ELSE 1 END AS row_number
                ,@userid:= x.userid AS  userid
                ,x.cnt,x.timegroupid
                FROM(
           SELECT  userid, timegroupid ,COUNT(userid) as cnt
           from templog 
           where type = 3 
           AND  day BETWEEN '2020-01-01' and '2020-01-20' 
           AND userid < 771267
           GROUP by  userid, timegroupid
           ORDER by  userid DESC ,cnt DESC
                 )x,(SELECT @row_number:=0,@userid:='') AS t
               )y 
        where  y.row_number = 1 
        ORDER by y.userid DESC
        LIMIT 1000;

Query Explain format:

                {
              "query_block": {
                "select_id": 1,
                "cost_info": {
                  "query_cost": "12.00"
                },
                "ordering_operation": {
                  "using_filesort": true,
                  "table": {
                    "table_name": "y",
                    "access_type": "ref",
                    "possible_keys": [
                      "<auto_key0>"
                    ],
                    "key": "<auto_key0>",
                    "used_key_parts": [
                      "row_number"
                    ],
                    "key_length": "9",
                    "ref": [
                      "const"
                    ],
                    "rows_examined_per_scan": 10,
                    "rows_produced_per_join": 10,
                    "filtered": "100.00",
                    "cost_info": {
                      "read_cost": "10.00",
                      "eval_cost": "2.00",
                      "prefix_cost": "12.00",
                      "data_read_per_join": "320"
                    },
                    "used_columns": [
                      "row_number",
                      "userid",
                      "cnt",
                      "timegroupid"
                    ],
                    "attached_condition": "((`y`.`row_number` <=> 1))",
                    "materialized_from_subquery": {
                      "using_temporary_table": true,
                      "dependent": false,
                      "cacheable": true,
                      "query_block": {
                        "select_id": 2,
                        "cost_info": {
                          "query_cost": "6441.25"
                        },
                        "nested_loop": [
                          {
                            "table": {
                              "table_name": "t",
                              "access_type": "system",
                              "rows_examined_per_scan": 1,
                              "rows_produced_per_join": 1,
                              "filtered": "100.00",
                              "cost_info": {
                                "read_cost": "0.00",
                                "eval_cost": "0.20",
                                "prefix_cost": "0.00",
                                "data_read_per_join": "16"
                              },
                              "used_columns": [
                                "@row_number:=0",
                                "@userid:=''"
                              ],
                              "materialized_from_subquery": {
                                "using_temporary_table": true,
                                "dependent": false,
                                "cacheable": true,
                                "query_block": {
                                  "select_id": 4,
                                  "message": "No tables used"
                                }
                              }
                            }
                          },
                          {
                            "table": {
                              "table_name": "x",
                              "access_type": "ALL",
                              "rows_examined_per_scan": 25725,
                              "rows_produced_per_join": 25725,
                              "filtered": "100.00",
                              "cost_info": {
                                "read_cost": "1296.25",
                                "eval_cost": "5145.00",
                                "prefix_cost": "6441.25",
                                "data_read_per_join": "602K"
                              },
                              "used_columns": [
                                "userid",
                                "timegroupid",
                                "cnt"
                              ],
                              "materialized_from_subquery": {
                                "using_temporary_table": true,
                                "dependent": false,
                                "cacheable": true,
                                "query_block": {
                                  "select_id": 3,
                                  "cost_info": {
                                    "query_cost": "140807.11"
                                  },
                                  "ordering_operation": {
                                    "using_filesort": true,
                                    "grouping_operation": {
                                      "using_temporary_table": true,
                                      "using_filesort": false,
                                      "table": {
                                        "table_name": "templog",
                                        "access_type": "range",
                                        "possible_keys": [
                                          "templog_type_IDX"
                                        ],
                                        "key": "templog_type_IDX",
                                        "used_key_parts": [
                                          "type",
                                          "day"
                                        ],
                                        "key_length": "13",
                                        "rows_examined_per_scan": 694718,
                                        "rows_pr
            oduced_per_join": 25725,
                                        "filtered": "33.33",
                                        "using_index": true,
                                        "cost_info": {
                                          "read_cost": "1863.51",
                                          "eval_cost": "5145.03",
                                          "prefix_cost": "140807.11",
                                          "data_read_per_join": "803K"
                                        },
                                        "used_columns": [
                                          "id",
                                          "userid",
                                          "type",
                                          "day",
                                          "timegroupid"
                                        ],
                                        "attached_condition": "((`templog`.`type` = 3) and (`templog`.`day` between '2020-01-01' and '2020-01-20') and (`templog`.`userid` < 771267))"
                                      }
                                    }
                                  }
                                }
                              }
                            }
                          }
                        ]
                      }
                    }
                  }
                }
              }
            }

Is there any other to optimize query or change index order or rewrite query in another way for better performance?

0

Only the most inner subquery can be optimized. But you use two range conditions in it... I think that covering index (type, userid, timegroupid, day) is the best (but diskspace-expensive) way to optimize. All outer (sub)queries works with non-indexed dynamic dataset and cannot be optimized. - Akina

  • ,thank you.i created index that was suggested by you but not whole index are used by optimizer. So query not optimized. – 5a01d01P Apr 10 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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