`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 14.3.11.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 14.3.11.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 non-unique 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 non-unique 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 non-unique 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 non-unique 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 14.3.11.1, “Configuring Persistent Optimizer Statistics Parameters”

SELECT index_name, stat_name, stat_description FROM mysql.innodb_index_stats WHERE database_name='test' AND table_name='t' AND stat_name like 'n_diff_pfx%'; +------------+--------------+------------------+ | 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 | +------------+--------------+------------------+

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 non-unique 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 non-unique 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.