Is it possible to use LIMIT for each GROUP BY used in MySQL-CodePudding

I have to find the top 10 tables for each databases from INFORMATION_SCHEMA.TABLES using a single query. The query i used gives total top 10 tables for all the databases, but i need top 10 for each databases

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, IFNULL(SUM(data_length index_length),0) AS size_byt, IFNULL(SUM(data_length),0) AS data_size_byt, IFNULL(SUM(index_length),0) AS index_size_byt, IFNULL(SUM(table_rows),0)
AS row_count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA in (select schema_name FROM information_schema.schemata) GROUP BY table_name,TABLE_SCHEMA ORDER BY size_byt DESC LIMIT 10;


 -------------- ---------------------------- ---------- --------------- ---------------- ----------- 
| TABLE_SCHEMA | TABLE_NAME                 | size_byt | data_size_byt | index_size_byt | row_count |
 -------------- ---------------------------- ---------- --------------- ---------------- ----------- 
| mysql        | innodb_index_stats         | 22675456 |      22675456 |              0 |     87679 |
| mysql        | innodb_table_stats         |  2637824 |       2637824 |              0 |     11252 |
| mysql        | help_topic                 |  1687552 |       1589248 |          98304 |       977 |
| db2654db     | WM_NOTIFICATION_ATTRIBUTES |  1589248 |       1589248 |              0 |      4328 |
| db2328db     | WM_NOTIFICATION_ATTRIBUTES |  1589248 |       1589248 |              0 |      4276 |
| db3015db     | WM_NOTIFICATION_ATTRIBUTES |  1589248 |       1589248 |              0 |      4650 |
| db3011db     | WM_NOTIFICATION_ATTRIBUTES |  1589248 |       1589248 |              0 |      5662 |
| db2725db     | WM_NOTIFICATION_ATTRIBUTES |  1589248 |       1589248 |              0 |      4422 |
| db2530db     | WM_NOTIFICATION_ATTRIBUTES |  1589248 |       1589248 |              0 |      4070 |
| db2721db     | WM_NOTIFICATION_ATTRIBUTES |  1589248 |       1589248 |              0 |      4045 |
 -------------- ---------------------------- ---------- --------------- ---------------- ----------- 
10 rows in set (1.17 sec)

CodePudding user response：

For MySQL 8 , you may use ROW_NUMBER() partition By TABLE_SCHEMA as the following:

Select * From 
  (
    SELECT TABLE_SCHEMA, TABLE_NAME, 
           IFNULL(SUM(data_length index_length),0) AS size_byt, 
           IFNULL(SUM(data_length),0) AS data_size_byt, 
           IFNULL(SUM(index_length),0) AS index_size_byt, 
           IFNULL(SUM(table_rows),0) AS row_count,
           ROW_NUMBER() Over (Partition By TABLE_SCHEMA Order By IFNULL(SUM(data_length index_length),0) DESC) RN
   FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_SCHEMA in (select schema_name FROM information_schema.schemata) 
   GROUP BY table_name,TABLE_SCHEMA 
 ) D 
Where D.RN <=10;

See a demo from db<>fiddle.

For older versions of MySQL you may simulate the ROW_NUMBER() function as the following:

Set @RN = 0;
Set @TS = null;
Select D.TABLE_SCHEMA, D.TABLE_NAME, D.size_byt, 
       D.data_size_byt, D.index_size_byt, D.row_count,
       D.ROW_NUM
From 
  (
    SELECT TABLE_SCHEMA, TABLE_NAME, 
           IFNULL(SUM(data_length index_length),0) AS size_byt, 
           IFNULL(SUM(data_length),0) AS data_size_byt, 
           IFNULL(SUM(index_length),0) AS index_size_byt, 
           IFNULL(SUM(table_rows),0) AS row_count,
           IF(@TS <> TABLE_SCHEMA, @RN:=1, @RN:=@RN 1) ROW_NUM,
           @TS := TABLE_SCHEMA
   FROM INFORMATION_SCHEMA.TABLES 
   WHERE TABLE_SCHEMA in (select schema_name FROM information_schema.schemata) 
   GROUP BY table_name,TABLE_SCHEMA 
   Order By size_byt
 ) D 
Where D.ROW_NUM <=10
Order By D.TABLE_SCHEMA, D.ROW_NUM;

See a demo.