[MDEV-31614] Index Usage statistics are not shown in information_schema.INDEX_STATISTICS - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.11.4
Fix Version/s: None
Component/s: Plugin - userstat
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)

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: Alex

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2023-07-04 02:27

Updated:: 2023-07-04 04:53

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.