SHOW-ANALYZE

Name

SHOW ANALYZE

Description

Use SHOW ANALYZE to view information about statistics collection jobs.

Syntax:

SHOW [AUTO] ANALYZE < table_name | job_id >
    [ WHERE [ STATE = [ "PENDING" | "RUNNING" | "FINISHED" | "FAILED" ] ] ];

AUTO: Show historical information for automatic collection jobs only. Note that, by default, the status of only the last 20,000 completed automatic collection jobs is retained.
table_name: Table name, specify to view statistics job information for that table. It can be in the format db_name.table_name. When not specified, it returns information for all statistics jobs.
job_id: Job ID for statistics collection, obtained when executing ANALYZE. When not specified, this command returns information for all statistics jobs.

Output:

Column Name	Description
`job_id`	Job ID
`catalog_name`	Catalog Name
`db_name`	Database Name
`tbl_name`	Table Name
`col_name`	Column Name List
`job_type`	Job Type
`analysis_type`	Analysis Type
`message`	Job Information
`last_exec_time_in_ms`	Last Execution Time
`state`	Job Status
`schedule_type`	Scheduling Method

Here's an example:

mysql> show analyze 245073\G;
*************************** 1. row ***************************
              job_id: 245073
        catalog_name: internal
             db_name: default_cluster:tpch
            tbl_name: lineitem
            col_name: [l_returnflag,l_receiptdate,l_tax,l_shipmode,l_suppkey,l_shipdate,l_commitdate,l_partkey,l_orderkey,l_quantity,l_linestatus,l_comment,l_extendedprice,l_linenumber,l_discount,l_shipinstruct]
            job_type: MANUAL
       analysis_type: FUNDAMENTALS
             message: 
last_exec_time_in_ms: 2023-11-07 11:00:52
               state: FINISHED
            progress: 16 Finished  |  0 Failed  |  0 In Progress  |  16 Total
       schedule_type: ONCE

Each collection job can contain one or more tasks, with each task corresponding to the collection of a column. Users can use the following command to view the completion status of statistics collection for each column.

Syntax:

SHOW ANALYZE TASK STATUS [job_id]

Here's an example:

mysql> show analyze task status 20038 ;
+---------+----------+---------+----------------------+----------+
| task_id | col_name | message | last_exec_time_in_ms | state    |
+---------+----------+---------+----------------------+----------+
| 20039   | col4     |         | 2023-06-01 17:22:15  | FINISHED |
| 20040   | col2     |         | 2023-06-01 17:22:15  | FINISHED |
| 20041   | col3     |         | 2023-06-01 17:22:15  | FINISHED |
| 20042   | col1     |         | 2023-06-01 17:22:15  | FINISHED |
+---------+----------+---------+----------------------+----------+

Keywords

SHOW, ANALYZE

SHOW-ALTER SHOW-BACKENDS