yangzhg opened a new issue, #18097: URL: https://github.com/apache/doris/issues/18097
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description Collecting the table, materialized view, and column information hit in the query can help us guide query optimization and table structure optimization ### Use case 1. Show the query hit statistics for `baseall` ```sql MySQL [test_query_db]> show query stats from table baseall; +-------+------------+-------------+ | Field | QueryCount | FilterCount | +-------+------------+-------------+ | k0 | 0 | 0 | | k1 | 0 | 0 | | k2 | 0 | 0 | | k3 | 0 | 0 | | k4 | 0 | 0 | | k5 | 0 | 0 | | k6 | 0 | 0 | | k10 | 0 | 0 | | k11 | 0 | 0 | | k7 | 0 | 0 | | k8 | 0 | 0 | | k9 | 0 | 0 | | k12 | 0 | 0 | | k13 | 0 | 0 | +-------+------------+-------------+ 14 rows in set (0.002 sec) MySQL [test_query_db]> select k0, k1,k2, sum(k3) from baseall where k9 > 1 group by k0,k1,k2; +------+------+--------+-------------+ | k0 | k1 | k2 | sum(`k3`) | +------+------+--------+-------------+ | 0 | 6 | 32767 | 3021 | | 1 | 12 | 32767 | -2147483647 | | 0 | 3 | 1989 | 1002 | | 0 | 7 | -32767 | 1002 | | 1 | 8 | 255 | 2147483647 | | 1 | 9 | 1991 | -2147483647 | | 1 | 11 | 1989 | 25699 | | 1 | 13 | -32767 | 2147483647 | | 1 | 14 | 255 | 103 | | 0 | 1 | 1989 | 1001 | | 0 | 2 | 1986 | 1001 | | 1 | 15 | 1992 | 3021 | +------+------+--------+-------------+ 12 rows in set (0.050 sec) MySQL [test_query_db]> show query stats from table baseall; +-------+------------+-------------+ | Field | QueryCount | FilterCount | +-------+------------+-------------+ | k0 | 1 | 0 | | k1 | 1 | 0 | | k2 | 1 | 0 | | k3 | 1 | 0 | | k4 | 0 | 0 | | k5 | 0 | 0 | | k6 | 0 | 0 | | k10 | 0 | 0 | | k11 | 0 | 0 | | k7 | 0 | 0 | | k8 | 0 | 0 | | k9 | 1 | 1 | | k12 | 0 | 0 | | k13 | 0 | 0 | +-------+------------+-------------+ 14 rows in set (0.001 sec) ``` 2. Show the query hit statistics summary for all the mv in a table ```sql MySQL [test_query_db]> show query stats from table baseall all; +-----------+------------+ | IndexName | QueryCount | +-----------+------------+ | baseall | 1 | +-----------+------------+ 1 row in set (0.005 sec) ``` 3. Show the query hit statistics detail info for all the mv in a table ```sql MySQL [test_query_db]> show query stats from table baseall all verbose; +-----------+-------+------------+-------------+ | IndexName | Field | QueryCount | FilterCount | +-----------+-------+------------+-------------+ | baseall | k0 | 1 | 0 | | | k1 | 1 | 0 | | | k2 | 1 | 0 | | | k3 | 1 | 0 | | | k4 | 0 | 0 | | | k5 | 0 | 0 | | | k6 | 0 | 0 | | | k10 | 0 | 0 | | | k11 | 0 | 0 | | | k7 | 0 | 0 | | | k8 | 0 | 0 | | | k9 | 1 | 1 | | | k12 | 0 | 0 | | | k13 | 0 | 0 | +-----------+-------+------------+-------------+ 14 rows in set (0.017 sec) ``` 4. Show the query hit for a database ```sql MySQL [test_query_db]> show query stats from database test_query_db; +----------------------------+------------+ | TableName | QueryCount | +----------------------------+------------+ | compaction_tbl | 0 | | bigtable | 0 | | empty | 0 | | tempbaseall | 0 | | test | 0 | | test_data_type | 0 | | test_string_function_field | 0 | | baseall | 1 | | nullable | 0 | +----------------------------+------------+ 9 rows in set (0.005 sec) ``` 5. Show query hit statistics for all the databases ```sql MySQL [(none)]> show query stats; +-----------------+------------+ | Database | QueryCount | +-----------------+------------+ | test_query_db | 1 | +-----------------+------------+ 1 rows in set (0.005 sec) ``` SHOW QUERY STATS; ``` ### Related issues _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
