MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

`ANALYZE TABLE`

complexity for
`InnoDB`

tables is dependent on:

The number of pages sampled, as defined by

`innodb_stats_persistent_sample_pages`

.The number of indexed columns in a table

The number of partitions. If a table has no partitions, the number of partitions is considered to be 1.

Using these parameters, an approximate formula for estimating
`ANALYZE TABLE`

complexity would
be:

The value of
`innodb_stats_persistent_sample_pages`

* number of indexed columns in a table * the number of
partitions

Typically, the greater the resulting value, the greater the
execution time for `ANALYZE TABLE`

.

Note

`innodb_stats_persistent_sample_pages`

defines the number of pages sampled at a global level. To set
the number of pages sampled for an individual table, use the
`STATS_SAMPLE_PAGES`

option with
`CREATE TABLE`

or
`ALTER TABLE`

. For more
information, see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

If
`innodb_stats_persistent=OFF`

,
the number of pages sampled is defined by
`innodb_stats_transient_sample_pages`

.
See Section 15.8.10.2, “Configuring Non-Persistent Optimizer Statistics Parameters” for
additional information.

For a more in-depth approach to estimating ```
ANALYZE
TABLE
```

complexity, consider the following example.

In Big
O notation, `ANALYZE TABLE`

complexity is described as:

O(n_sample * (n_cols_in_uniq_i + n_cols_in_non_uniq_i + n_cols_in_pk * (1 + n_non_uniq_i)) * n_part)

where:

`n_sample`

is the number of pages sampled (defined by`innodb_stats_persistent_sample_pages`

)`n_cols_in_uniq_i`

is total number of all columns in all unique indexes (not counting the primary key columns)`n_cols_in_non_uniq_i`

is the total number of all columns in all nonunique indexes`n_cols_in_pk`

is the number of columns in the primary key (if a primary key is not defined,`InnoDB`

creates a single column primary key internally)`n_non_uniq_i`

is the number of nonunique indexes in the table`n_part`

is the number of partitions. If no partitions are defined, the table is considered to be a single partition.

Now, consider the following table (table `t`

),
which has a primary key (2 columns), a unique index (2 columns),
and two nonunique indexes (two columns each):

CREATE TABLE t ( a INT, b INT, c INT, d INT, e INT, f INT, g INT, h INT, PRIMARY KEY (a, b), UNIQUE KEY i1uniq (c, d), KEY i2nonuniq (e, f), KEY i3nonuniq (g, h) );

For the column and index data required by the algorithm
described above, query the
`mysql.innodb_index_stats`

persistent index
statistics table for table `t`

. The
`n_diff_pfx%`

statistics show the columns that
are counted for each index. For example, columns
`a`

and `b`

are counted for
the primary key index. For the nonunique indexes, the primary
key columns (a,b) are counted in addition to the user defined
columns.

Note

For additional information about the `InnoDB`

persistent statistics tables, see
Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”

mysql>`SELECT index_name, stat_name, stat_description`

`FROM mysql.innodb_index_stats WHERE`

`database_name='test' AND`

`table_name='t' AND`

+------------+--------------+------------------+ | index_name | stat_name | stat_description | +------------+--------------+------------------+ | PRIMARY | n_diff_pfx01 | a | | PRIMARY | n_diff_pfx02 | a,b | | i1uniq | n_diff_pfx01 | c | | i1uniq | n_diff_pfx02 | c,d | | i2nonuniq | n_diff_pfx01 | e | | i2nonuniq | n_diff_pfx02 | e,f | | i2nonuniq | n_diff_pfx03 | e,f,a | | i2nonuniq | n_diff_pfx04 | e,f,a,b | | i3nonuniq | n_diff_pfx01 | g | | i3nonuniq | n_diff_pfx02 | g,h | | i3nonuniq | n_diff_pfx03 | g,h,a | | i3nonuniq | n_diff_pfx04 | g,h,a,b | +------------+--------------+------------------+`stat_name like 'n_diff_pfx%';`

Based on the index statistics data shown above and the table definition, the following values can be determined:

`n_cols_in_uniq_i`

, the total number of all columns in all unique indexes not counting the primary key columns, is 2 (`c`

and`d`

)`n_cols_in_non_uniq_i`

, the total number of all columns in all nonunique indexes, is 4 (`e`

,`f`

,`g`

and`h`

)`n_cols_in_pk`

, the number of columns in the primary key, is 2 (`a`

and`b`

)`n_non_uniq_i`

, the number of nonunique indexes in the table, is 2 (`i2nonuniq`

and`i3nonuniq`

))`n_part`

, the number of partitions, is 1.

You can now calculate
`innodb_stats_persistent_sample_pages`

* (2 + 4
+ 2 * (1 + 2)) * 1 to determine the number of leaf pages that
are scanned. With
`innodb_stats_persistent_sample_pages`

set to
the default value of `20`

, and with a default
page size of 16 `KiB`

(`innodb_page_size`

=16384), you
can then estimate that 20 * 12 * 16384 `bytes`

are read for table `t`

, or about 4
`MiB`

.

Note

All 4 `MiB`

may not be read from disk, as
some leaf pages may already be cached in the buffer pool.