Mysql Performance Issue with Group By/Order By

group byMySQLorder-byperformancequery-performance

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?

Best Answer

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

Related Solutions

Mysql – Slow complex query with group/order

I can see couple things that should improve your query performance.

1 As you already found out there is absolutely no need to join mentioncache. Using EXISTS seems more natural (or IN as you did, but EXISTS may work better from performance point of view).

2 DATE(m.indexed) BETWEEN "2012-09-16" AND "2012-10-16" can be rewritten to m.indexed between "2012-09-16" AND "2012-10-16 23:59:59", so mysql can use index.

3 urlinfluranks doesn't seem to be used anywhere except in LEFT JOIN, why do you need it?

4 f.foreign_id can be either null or m.id, and this is the only reference to favoureditems table, I'd rather use subquery in this case.

Finally, I think you can get the same results without GROUP BY m.id (as far as I understood , mentions.id a primary key).

SELECT   
m.id, m.title, m.title_text, m.content_text, m.url,m.root_url,m.sub_type,m.indexed,  
CASE 
 WHEN EXISTS 
    (SELECT NULL FROM favoureditems f WHERE f.model = "Mention" 
    AND f.foreign_id = m.id AND f.owner_id = 803) THEN m.id 
END AS f.foreign_id,
, v.foreign_id, v.created, mfs.score,  
Image.id,Image.model,Image.foreign_key, Image.dirname,Image.basename,  
(REPLACE(REPLACE(m.host_url, 'http://www.', ''), 'http://', '')) AS Mention__plain_url  
FROM mentions AS m  

LEFT JOIN 
(
  SELECT id,model,foreign_key,dirname,basename 
  FROM attachments Image  
  WHERE model = 'Mention'
  GROUP BY foreign_key
 )Image  ON (Image.foreign_key = m.id)      

LEFT JOIN 
(
   SELECT v.foreign_id, v.created 
   FROM visiteditems AS v  
   WHERE (v.model = "Mention"  AND v.owner_id = 803)  
    GROUP BY v.foreign_id
)v ON (v.foreign_id = m.id)
LEFT JOIN 
(
   SELECT mention_id,score
   FROM mentionfeedscores mfs  
   WHERE mfs.feed_id = '474737584865424564398208323289092'
   GROUP BY mention_id
)mfs ON (mfs.mention_id = m.id )

WHERE m.indexed BETWEEN "2012-09-16" AND "2012-10-16 23:59:59"  
   AND EXISTS 
  (
     SELECT NULL FROM mentioncache mc  
      WHERE mc.mention_id = m.id AND mc.profile_id = 803  
   )    
ORDER BY m.indexed DESC  
LIMIT 10

Mysql – Slow MySQL query with Order and Group by

As @Back in a Flash has suggested, an index on created_at in user_articles will probably help if the optimizer chooses to use it... but the optimizer might not catch on to the viability of that index, because you're doing something else that needs an explanation.

You're using GROUP BY but you're not aggregating anything that I can see. If you're not looking for the MIN(), MAX(), SUM(), COUNT(), or the result of some similar aggregate function, then your use of GROUP BY here is incorrect. By default, MySQL does not throw an error when GROUP BY is used like this.

It looks as if multiple rows in user_articles could reference the same row in articles. Your query, as written, will only return one matching row from user_articles for each referenced row in articles, and the selection of that row will not be deterministic.

That doesn't seem like what you want to be doing. If it is, you shouldn't be doing it this way. :)

If it isn't... or if each row in user_articles references a unique row in articles, then your query is unnecessarily burdening the optimizer by insisting that this is indeed what it wants the server to try to do. As the query is written, the GROUP BY is superfluous at best, and possibly contributing to the performance problem, since, even if you had an index on created_at, that conflicts with the fact that the server also needs to GROUP BY (potentially) a different index.

Best Answer

Related Solutions

Mysql – Slow complex query with group/order

Mysql – Slow MySQL query with Order and Group by

Related Question