mysql wptest < wptest.sql
mysql>
Server version: 10.6.7-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
127.0.0.1:3307> use wptest
Database changed
127.0.0.1:3307> show indexes from wp_options;
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| wp_options | 0 | PRIMARY | 1 | option_id | A | 120 | NULL | NULL | | BTREE | | | NO |
| wp_options | 0 | option_name | 1 | option_name | A | 120 | NULL | NULL | | BTREE | | | NO |
| wp_options | 1 | autoload | 1 | autoload | A | 4 | NULL | NULL | | BTREE | | | NO |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)
127.0.0.1:3307> select TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH from information_schema.tables where TABLE_SCHEMA = "wptest" and TABLE_NAME = "wp_options";
+------------+-------------+--------------+
| TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH |
+------------+-------------+--------------+
| 120 | 1589248 | 32768 |
+------------+-------------+--------------+
1 row in set (0.001 sec)
127.0.0.1:3307> select * from mysql.innodb_index_stats where database_name="wptest" and table_name="wp_options";
+---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+
| wptest | wp_options | PRIMARY | 2023-04-21 10:22:08 | n_diff_pfx01 | 120 | 1 | option_id |
| wptest | wp_options | PRIMARY | 2023-04-21 10:22:08 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| wptest | wp_options | PRIMARY | 2023-04-21 10:22:08 | size | 97 | NULL | Number of pages in the index |
| wptest | wp_options | autoload | 2023-04-21 10:22:08 | n_diff_pfx01 | 2 | 1 | autoload |
| wptest | wp_options | autoload | 2023-04-21 10:22:08 | n_diff_pfx02 | 120 | 1 | autoload,option_id |
| wptest | wp_options | autoload | 2023-04-21 10:22:08 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| wptest | wp_options | autoload | 2023-04-21 10:22:08 | size | 1 | NULL | Number of pages in the index |
| wptest | wp_options | option_name | 2023-04-21 10:22:08 | n_diff_pfx01 | 120 | 1 | option_name |
| wptest | wp_options | option_name | 2023-04-21 10:22:08 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| wptest | wp_options | option_name | 2023-04-21 10:22:08 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+
10 rows in set (0.001 sec)
127.0.0.1:3307> select * from mysql.innodb_table_stats where database_name="wptest" and table_name="wp_options";
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| wptest | wp_options | 2023-04-21 10:22:08 | 120 | 97 | 2 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.001 sec)
After disabling the flag, able to see the problem.
127.0.0.1:3307> Show variables like '%innodb_stats%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | OFF |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_modified_counter | 0 |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_traditional | ON |
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+
select * from mysql.innodb_index_stats where database_name="wptest" and table_name="wp_options";
+------------+-------------+--------------+
| TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH |
+------------+-------------+--------------+
| 0 | 16384 | 32768 |
+------------+-------------+--------------+
1 row in set (0.001 sec)
127.0.0.1:3307>
127.0.0.1:3307> select * from mysql.innodb_index_stats where database_name="wptest" and table_name="wp_options";
+---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+
| wptest | wp_options | PRIMARY | 2023-04-21 14:07:53 | n_diff_pfx01 | 0 | 1 | option_id |
| wptest | wp_options | PRIMARY | 2023-04-21 14:07:53 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| wptest | wp_options | PRIMARY | 2023-04-21 14:07:53 | size | 1 | NULL | Number of pages in the index |
| wptest | wp_options | autoload | 2023-04-21 14:07:53 | n_diff_pfx01 | 0 | 1 | autoload |
| wptest | wp_options | autoload | 2023-04-21 14:07:53 | n_diff_pfx02 | 0 | 1 | autoload,option_id |
| wptest | wp_options | autoload | 2023-04-21 14:07:53 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| wptest | wp_options | autoload | 2023-04-21 14:07:53 | size | 1 | NULL | Number of pages in the index |
| wptest | wp_options | option_name | 2023-04-21 14:07:53 | n_diff_pfx01 | 0 | 1 | option_name |
| wptest | wp_options | option_name | 2023-04-21 14:07:53 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| wptest | wp_options | option_name | 2023-04-21 14:07:53 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+
10 rows in set (0.000 sec)