[MDEV-31614] Index Usage statistics are not shown in information_schema.INDEX_STATISTICS Created: 2023-07-04  Updated: 2023-07-04

Status: Open
Project: MariaDB Server
Component/s: Plugin - userstat
Affects Version/s: 10.11.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Alex Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian



 Description   

Userstat does not account usage for particular index. Explain, Analyze and the speed of the query shows that end_date_index index is being used during the query. But is's usage statistics does now appear in information_schema.INDEX_STATISTICS.

userstat=1

Table:

 CREATE TABLE `sale` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(100) NOT NULL DEFAULT '',
 
  `salesman` int(11) NOT NULL DEFAULT 0,
 
  `min_price` double(16,2) DEFAULT NULL,
 
  `curency` int(11) NOT NULL DEFAULT 1,
 
  `inc` double(16,2) DEFAULT NULL,
 
  `beg_date` int(11) NOT NULL DEFAULT 0,
 
  `end_date` int(11) NOT NULL DEFAULT 0,
 
  `type` int(11) NOT NULL DEFAULT 0,
 
  `notify` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT 'N',
 
  `antisniper` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `end_date_extended` varchar(0) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `cur_price` double(16,2) DEFAULT NULL,
 
  `num_of_bids` int(11) DEFAULT 0,
 
  `pic` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `max_price` double(16,2) DEFAULT 0.00,
 
  `pop` int(11) DEFAULT 0,
 
  `sale_type` enum('Auction','Sale') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT 'Auction',
 
  `s_bold` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `s_featured` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `bulk` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `solo_delivery` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `quant` int(11) NOT NULL DEFAULT 1,
 
  `b_quant` int(11) NOT NULL DEFAULT 0,
 
  `rep_eat` smallint(6) DEFAULT 0,
 
  `reposted` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `l_c_d` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `add_item_id` int(11) DEFAULT NULL,
 
  `pay_method` set('CASH','BANK','NALOZH','CARD','YANDEX','WEBMONEY','DESC','CONTACT','TERMINAL','MAIL','PAYPAL','BITCOIN') CHARACTER SET utf8mb3 COLLATE utf8
 
mb3_general_ci NOT NULL DEFAULT 'CASH',
 
  `d_abrod` enum('NO','COUNTRY','WORLD') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'NO',
 
  `d_local` enum('SELF','FREE','CHARGE') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'SELF',
 
  `d_local_p` double(16,2) DEFAULT NULL,
 
  `d_country_p` double(16,2) DEFAULT NULL,
 
  `d_world_p` double(16,2) DEFAULT NULL,
 
  `min_rate` smallint(5) unsigned DEFAULT 0,
 
  `private` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `is_reposted` enum('Y','N','A') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `new_id` int(11) DEFAULT NULL,
 
  `new` enum('Y','N','NA') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'NA',
 
  `best_offer` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `norm_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `pic_url` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `item_source` int(11) DEFAULT NULL,
 
  `m_blck` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `cty` int(11) NOT NULL DEFAULT 0,
 
  `cntry` int(11) NOT NULL DEFAULT 0,
 
  `num_of_pic` tinyint(4) NOT NULL DEFAULT 0,
 
  `longevity` tinyint(4) NOT NULL DEFAULT 7,
 
  `ya_forget` enum('Y','N') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
 
  `rep_eat_done` int(10) unsigned NOT NULL DEFAULT 0,
 
  `youtube_video` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `youtube_video_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `strike_price` double(16,2) NOT NULL DEFAULT 0.00,
 
  `charity_pr` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
  `charity` enum('AUTO','N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'AUTO',
 
  `private_list` int(10) unsigned NOT NULL DEFAULT 0,
 
  `tags` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `status` tinyint(4) NOT NULL DEFAULT 0,
 
  `pic_p` float NOT NULL DEFAULT 1,
 
  `pic_p2` float NOT NULL DEFAULT 1,
 
  `pic_p3` float NOT NULL DEFAULT 1,
 
  `pic_p4` float NOT NULL DEFAULT 1,
 
  `pic_p5` float NOT NULL DEFAULT 1,
 
  `pic_p6` float NOT NULL DEFAULT 1,
 
  `pic_p7` float NOT NULL DEFAULT 1,
 
  `pic_p8` float NOT NULL DEFAULT 1,
 
  `pic_p9` float NOT NULL DEFAULT 1,
 
  `ut` blob NOT NULL,
 
  `PV` int(5) unsigned NOT NULL DEFAULT 0,
 
  `editable_pics` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `old_price` double(16,2) DEFAULT NULL,
 
  `createdAt` timestamp NOT NULL DEFAULT current_timestamp(),
 
  PRIMARY KEY (`id`,`status`),
 
  KEY `end_date_index` (`end_date`),
 
  KEY `index_s_featured` (`s_featured`),
 
  KEY `salesman_end_date` (`salesman`,`end_date`),
 
  KEY `s_f_e_i` (`salesman`,`s_featured`,`end_date`),
 
  KEY `t_mb_i` (`type`,`m_blck`),
 
  KEY `s_ut_i` (`salesman`,`ut`(1)),
 
  KEY `type_pop_i` (`type`,`pop`),
 
  KEY `salesman_beg_date` (`salesman`,`beg_date`),
 
  KEY `i2s` (`id`,`salesman`),
 
  KEY `should_be_in_listing2` (`m_blck`,`end_date`,`beg_date`,`l_c_d`),
 
  KEY `profile_sale` (`salesman`,`sale_type`,`num_of_bids`,`end_date`),
 
  KEY `l_c_d_i` (`l_c_d`),
 
  KEY `should_not_be_in_listing` (`id`,`l_c_d`,`end_date`,`beg_date`,`m_blck`),
 
  KEY `fav_sellers_new_items` (`salesman`,`is_reposted`,`beg_date`),
 
  KEY `items_with_bids_by_salesman` (`salesman`,`num_of_bids`),
 
  KEY `types_by_salesman` (`salesman`,`type`),
 
  KEY `leftovers_by_salesman` (`salesman`,`b_quant`,`quant`),
 
  KEY `fav_sellers_dashboard_index` (`salesman`,`beg_date`,`end_date`,`num_of_bids`,`is_reposted`),
 
  KEY `type_beg_date_i` (`type`,`beg_date`),
 
  KEY `s_long_i` (`sale_type`,`longevity`,`end_date`,`beg_date`),
 
  KEY `listing_check_index` (`id`,`end_date`,`l_c_d`),
 
  KEY `s_beg_date_id_DA_i` (`salesman`,`beg_date` DESC,`id`),
 
  KEY `s_beg_date_id_AA_i` (`salesman`,`beg_date`,`id`),
 
  KEY `s_num_of_bids_id_AA_i` (`salesman`,`num_of_bids`,`id`),
 
  KEY `s_num_of_bids_id_DA_i` (`salesman`,`num_of_bids` DESC,`id`),
 
  KEY `s_end_date_id_DA_i` (`salesman`,`end_date` DESC,`id`),
 
  KEY `s_end_date_id_AA_i` (`salesman`,`end_date`,`id`),
 
  KEY `s_id_A_i` (`salesman`,`id`),
 
  KEY `s_id_D_i` (`salesman`,`id` DESC),
 
  KEY `sale_type_s_end_date_id_A_i` (`sale_type`,`salesman`,`end_date`,`id`),
 
  KEY `sale_type_s_end_date_id_D_i` (`sale_type`,`salesman`,`end_date` DESC,`id`),
 
  KEY `s_sale_type_end_date_id_D_i` (`salesman`,`sale_type`,`end_date` DESC,`id`),
 
  KEY `s_sale_type_end_date_id_A_i` (`salesman`,`sale_type`,`end_date`,`id`),
 
  KEY `salesman_name_i` (`salesman`,`name`(8))
 
) ENGINE=InnoDB AUTO_INCREMENT=296053895 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
 
 PARTITION BY LIST (`status`)
 
(PARTITION `work` VALUES IN (0) ENGINE = InnoDB,
 
 PARTITION `ended` VALUES IN (1) ENGINE = InnoDB,
 
 PARTITION `deleted` VALUES IN (2) ENGINE = InnoDB,
 
 PARTITION `drafts` VALUES IN (-1) ENGINE = InnoDB,
 
 PARTITION `posponds` VALUES IN (-2) ENGINE = InnoDB)

Query: 

select sale.id ,sale.sale_type ,sale.rep_eat ,sale.rep_eat_done ,sale.longevity ,sale.num_of_bids from sale where sale.status=0 and sale.end_date < UNIX_TIMESTAMP() - 60;

Explain:

+------+-------------+-------+-------+----------------+----------------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+-------------+
 
|    1 | SIMPLE      | sale  | range | end_date_index | end_date_index | 4       | NULL | 289  | Using where |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+-------------+

Analyze

+------+-------------+-------+-------+----------------+----------------+---------+------+------+--------+----------+------------+-------------+
 
| id   | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | sale  | range | end_date_index | end_date_index | 4       | NULL | 678  | 649.00 |   100.00 |     100.00 | Using where |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+--------+----------+------------+-------------+


> SELECT * FROM information_schema.INDEX_STATISTICS  WHERE TABLE_NAME = "sale" order by INDEX_NAME;
 
+--------------+------------+-----------------------------+-----------+
 
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME                  | ROWS_READ |
 
+--------------+------------+-----------------------------+-----------+
 
| meshok       | sale       | fav_sellers_dashboard_index |  17179770 |
 
| meshok       | sale       | fav_sellers_new_items       |   8532704 |
 
| meshok       | sale       | i2s                         |  18303036 |
 
| meshok       | sale       | index_s_featured            |   2190110 |
 
| meshok       | sale       | items_with_bids_by_salesman |   2110612 |
 
| meshok       | sale       | leftovers_by_salesman       |  10518312 |
 
| meshok       | sale       | PRIMARY                     | 154462860 |
 
| meshok       | sale       | profile_sale                |   7417433 |
 
| meshok       | sale       | salesman_beg_date           |   5450593 |
 
| meshok       | sale       | salesman_end_date           |   8894406 |
 
| meshok       | sale       | salesman_name_i             |   1079669 |
 
| meshok       | sale       | sale_type_s_end_date_id_A_i |   1340707 |
 
| meshok       | sale       | sale_type_s_end_date_id_D_i |     64107 |
 
| meshok       | sale       | should_not_be_in_listing    |   5280368 |
 
| meshok       | sale       | s_beg_date_id_AA_i          |   3142354 |
 
| meshok       | sale       | s_beg_date_id_DA_i          |  14450041 |
 
| meshok       | sale       | s_end_date_id_AA_i          |   4802345 |
 
| meshok       | sale       | s_end_date_id_DA_i          |  11186440 |
 
| meshok       | sale       | s_f_e_i                     |  21763584 |
 
| meshok       | sale       | s_id_A_i                    |  10007715 |
 
| meshok       | sale       | s_id_D_i                    |   5815010 |
 
| meshok       | sale       | s_mb_i                      |  97082154 |
 
| meshok       | sale       | s_num_of_bids_id_AA_i       |    745849 |
 
| meshok       | sale       | s_num_of_bids_id_DA_i       |    664596 |
 
| meshok       | sale       | s_sale_type_end_date_id_A_i |   8707314 |
 
| meshok       | sale       | s_sale_type_end_date_id_D_i |   2412177 |
 
| meshok       | sale       | s_ut_i                      |  13204403 |
 
| meshok       | sale       | types_by_salesman           |  80635514 |
 
| meshok       | sale       | type_pop_i                  |    301370 |
 
| meshok       | sale       | t_mb_i                      |  73118519 |
 
+--------------+------------+-----------------------------+-----------+
 
30 rows in set (0.001 sec)



 Comments   
Comment by Alex [ 2023-07-04 ]

Recreating the index does not help.

Comment by Daniel Black [ 2023-07-04 ] 

$ podman exec mtest mariadb test -e 'drop table if exists sale;CREATE or replace TABLE `sale` (    `id` int(11) NOT NULL AUTO_INCREMENT,  `end_date` int(11) NOT NULL DEFAULT 0, `status` tinyint(4) NOT NULL DEFAULT 0,  PRIMARY KEY (`id`,`status`),    KEY `end_date_index` (`end_date`)) select seq as id, UNIX_TIMESTAMP() - 1000 + seq as end_date, seq % 5 as status  from seq_1_to_1000;select *  from sale where sale.status=0 and sale.end_date < UNIX_TIMESTAMP() - 60;SELECT * FROM information_schema.INDEX_STATISTICS  WHERE TABLE_NAME = "sale" order by INDEX_NAME;'
 
...
 
925	1688440216	0
 
930	1688440221	0
 
935	1688440226	0
 
TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	ROWS_READ
 
test	sale	end_date_index	940

I can't reproduce on 10.4 -> 11.1

Can you double check the query was actually executed before the information_schema table was queried?

Comment by Alex [ 2023-07-04 ]

I tried to reproduce the problem on the same table:

 
 
MariaDB [meshok]> create table sale_test like sale;
 
Query OK, 0 rows affected (0.751 sec)
 
 
 
MariaDB [meshok]> insert into sale_test select * from sale where status=0 and end_date < unix_timestamp() + 5000;
 
Query OK, 42389 rows affected (2.311 sec)
 
Records: 42389  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [meshok]> explain select sale.id ,sale.sale_type ,sale.rep_eat ,sale.rep_eat_done ,sale.longevity ,sale.num_of_bids from sale_test as sale where sale.status=0 and sale.end_date < UNIX_TIMESTAMP() - 60;
 
+------+-------------+-------+-------+----------------+----------------+---------+------+-------+-------------+
 
| id   | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows  | Extra       |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+-------+-------------+
 
|    1 | SIMPLE      | sale  | range | end_date_index | end_date_index | 4       | NULL | 17982 | Using where |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+-------+-------------+
 
1 row in set (0.001 sec)
 
 
 
 
 
MariaDB [meshok]> select sale.id ,sale.sale_type ,sale.rep_eat ,sale.rep_eat_done ,sale.longevity ,sale.num_of_bids from sale_test as sale *force index (end_date_index)* where sale.status=0 and sale.end_date < UNIX_TIMESTAMP() - 60;
 
 
 
+-----------+-----------+---------+--------------+-----------+-------------+
 
| id        | sale_type | rep_eat | rep_eat_done | longevity | num_of_bids |
 
+-----------+-----------+---------+--------------+-----------+-------------+
 
| 294720393 | Auction   |       0 |            0 |         5 |           3 |
 
| 294723719 | Auction   |       0 |            0 |         5 |          25 |
 
| 295497398 | Auction   |       0 |            0 |         7 |           1 |
 
| 295499013 | Auction   |       0 |            0 |         7 |           1 |
 
| 295499520 | Auction   |       0 |            0 |         7 |           1 |
 
| 295505908 | Auction   |       0 |            0 |         7 |           1 |
 
| 295505978 | Auction   |       0 |            0 |         7 |           1 |
 
| 295506021 | Auction   |       0 |            0 |         7 |           1 |
 
........
 
+-----------+-----------+---------+--------------+-----------+-------------+
 
3475 rows in set (0.010 sec)
 
 
 
 
 
*MariaDB [meshok]> SELECT * FROM information_schema.INDEX_STATISTICS WHERE TABLE_NAME = "sale_test" order by INDEX_NAME;
 
Empty set (0.001 sec)*
 
 
 
MariaDB [meshok]> explain select sale.id ,sale.sale_type ,sale.rep_eat ,sale.rep_eat_done ,sale.longevity ,sale.num_of_bids from sale_test as sale where sale.status=0 and sale.end_date < UNIX_TIMESTAMP() - 60;
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+-------------+
 
| id   | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+-------------+
 
|    1 | SIMPLE      | sale  | range | end_date_index | end_date_index | 4       | NULL | 5335 | Using where |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+-------------+
 
 
 
MariaDB [meshok]> analyze select sale.id ,sale.sale_type ,sale.rep_eat ,sale.rep_eat_done ,sale.longevity ,sale.num_of_bids from sale_test as sale where sale.status=0 and sale.end_date < UNIX_TIMESTAMP() - 60;
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+---------+----------+------------+-------------+
 
| id   | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | r_rows  | filtered | r_filtered | Extra       |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+---------+----------+------------+-------------+
 
|    1 | SIMPLE      | sale  | range | end_date_index | end_date_index | 4       | NULL | 8341 | 8341.00 |   100.00 |     100.00 | Using where |
 
+------+-------------+-------+-------+----------------+----------------+---------+------+------+---------+----------+------------+-------------+
 
1 row in set (0.027 sec)

It is reproducible with the data from the original table.

Comment by Alex [ 2023-07-04 ]

I used force index (end_date_index) to be sure MariaDB is using end_date_index index.

Generated at Thu Feb 08 10:25:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.