# Sampling and Accuracy - Cont'd

+ Options for `ANALYZE TABLE table_name`  

![`smart`](CollectStatisticsSamplingandAccuracy_aa_001_20230915.png)

------------------------------------------------------------------------------
In the slide, we can see that beyond using `ANALYZE TABLE table_name` for collecting overall table statistics with default precision, you can also specify `INDEX index_name`, `PARTITION partition_name`, and `COLUMNS column_name` to gather statistics specifically for frequently used indexes, partitions, and columns. 

Note that Collecting statistics on `PREDICATE COLUMNS` is an experimental feature; exercise caution when employing it in a production environment.

To collect statistics for specific columns, ensure the system variable `tidb_enable_column_tracking` is set to `ON` and `tidb_analyze_version` is set to 2. Additionally, the database version must be v5.4 or newer.

After `ANALYZE TABLE table_name`, we can further specify the desired precision with the following options:
- `WITH NUM BUCKETS` specifies the maximum number of buckets for histogram generation.
- `WITH NUM TOPN` determines the maximum number of `TopN` values to generate.
- `WITH NUM CMSKETCH DEPTH` sets the length of the `CM Sketch` two-dimensional array. (Applicable only when `tidb_analyze_version = 1`)
- `WITH NUM CMSKETCH WIDTH` sets the width of the `CM Sketch` two-dimensional array. (Applicable only when `tidb_analyze_version = 1`)
- `WITH NUM SAMPLES` specifies the number of samples. When included, samples are collected using the reservoir method, and the value should be numerical.
- `WITH FLOAT_NUM SAMPLERATE` indicates the sampling rate. When specified, samples are collected using the Bernoulli sampling method, with a rate between `(0, 1]`.

Understanding the basics of statistics precision, here are some recommendations for setting sampling rate precision in daily operations:
- For tables with tens of thousands of rows, a sampling rate of 100% (`WITH 1 SAMPLERATE`) is generally sufficient.
- For tables with millions of rows, the sampling rate should not be less than `SAMPLERATE = round(100000/rowcount)`.
- For tables with tens of millions of rows, the sampling rate should not be less than `SAMPLERATE = round(100000*10/rowcount)`.
- For larger partitioned tables, it is advised to collect statistics per partition, as varying partitions may have different precision needs.
- Avoid specifying an excessively high sampling rate to prevent undue memory consumption.

Since version 5.4.0, TiDB supports the persistence of some `ANALYZE` configurations, simplifying the reuse of existing configurations for future statistics collection. This feature is activated by default (with `tidb_analyze_version` set to 2 and `tidb_persist_analyze_options` enabled).

This feature allows for the persistent recording of configurations specified in the `ANALYZE` statement during manual execution. Once recorded, these configurations are automatically employed in subsequent statistics collection efforts, whether manual or automatic, unless new configurations are specified.

When manually executing the `ANALYZE` statement with persistent configurations multiple times, TiDB updates the persistent configuration with those specified in the latest `ANALYZE` statement.

For more details, please refer to: [Persist Analyze Configurations](https://docs.pingcap.com/tidb/stable/statistics#persist-analyze-configurations).