>>show create table t_config; CREATE TABLE `t_config` ( `recid` int(10) unsigned NOT NULL, `cdgrp` varchar(8) COLLATE utf8mb4_bin NOT NULL COMMENT 'code group', `l_appl` int(10) unsigned DEFAULT NULL COMMENT 'loose coupled to config ApplInfo to get an idea where the entry belongs to', `tshort` varchar(16) COLLATE utf8mb4_bin NOT NULL COMMENT 'code identification', `sysflag` tinyint(1) NOT NULL DEFAULT 0, `tlong` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'depending on content can be used as comment', `info` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL, `numint` int(11) NOT NULL DEFAULT 0 COMMENT 'configuration value', `numflt` float DEFAULT NULL, `attrib1` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL, `attrib2` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL, `l_ref1` int(10) unsigned DEFAULT NULL COMMENT 'loose coupled link to internal tables', `l_ref2` int(10) unsigned DEFAULT NULL COMMENT 'loose coupled link to internal tables', `extkey` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'applications have avoid duplicates', `url` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL, `rflags` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '8bit recordflags see config', `active` tinyint(1) NOT NULL DEFAULT 1, `r_regusr` int(10) unsigned NOT NULL, `regtm` timestamp NOT NULL DEFAULT current_timestamp(), `r_updusr` int(10) unsigned NOT NULL, `updtm` timestamp NOT NULL DEFAULT current_timestamp(), `updcnt` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`recid`), UNIQUE KEY `uq_cfg_1` (`cdgrp`,`tshort`,`numint`), KEY `fk_cfg_rusr` (`r_regusr`), KEY `fk_cfg_uusr` (`r_updusr`), CONSTRAINT `fk_cfg_rusr` FOREIGN KEY (`r_regusr`) REFERENCES `t_id` (`recid`) ON UPDATE CASCADE, CONSTRAINT `fk_cfg_uusr` FOREIGN KEY (`r_updusr`) REFERENCES `t_id` (`recid`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='1.0.0: configuration information for all applications' 1 row in set (0.000 sec) >>analyze table t_config; +---------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+---------+----------+----------+ | base.t_config | analyze | status | OK | +---------------+---------+----------+----------+ 1 row in set (0.021 sec) >>analyze table t_config persistent for all; +---------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+---------+----------+-----------------------------------------+ | base.t_config | analyze | status | Engine-independent statistics collected | | base.t_config | analyze | status | OK | +---------------+---------+----------+-----------------------------------------+ 2 rows in set (0.078 sec) >>show index from t_config; +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t_config | 0 | PRIMARY | 1 | recid | A | 5559 | NULL | NULL | | BTREE | | | | t_config | 0 | uq_cfg_1 | 1 | cdgrp | A | 24 | NULL | NULL | | BTREE | | | | t_config | 0 | uq_cfg_1 | 2 | tshort | A | 5465 | NULL | NULL | | BTREE | | | | t_config | 0 | uq_cfg_1 | 3 | numint | A | 5559 | NULL | NULL | | BTREE | | | | t_config | 1 | fk_cfg_rusr | 1 | r_regusr | A | 6 | NULL | NULL | | BTREE | | | | t_config | 1 | fk_cfg_uusr | 1 | r_updusr | A | 6 | NULL | NULL | | BTREE | | | +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.001 sec) >>select * from mysql.innodb_index_stats where database_name='base' and table_name = 't_config'; +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ | base | t_config | PRIMARY | 2020-04-10 10:59:13 | n_diff_pfx01 | 5667 | 20 | recid | | base | t_config | PRIMARY | 2020-04-10 10:59:13 | n_leaf_pages | 60 | NULL | Number of leaf pages in the index | | base | t_config | PRIMARY | 2020-04-10 10:59:13 | size | 96 | NULL | Number of pages in the index | | base | t_config | fk_cfg_rusr | 2020-04-10 10:59:13 | n_diff_pfx01 | 6 | 7 | r_regusr | | base | t_config | fk_cfg_rusr | 2020-04-10 10:59:13 | n_diff_pfx02 | 5559 | 7 | r_regusr,recid | | base | t_config | fk_cfg_rusr | 2020-04-10 10:59:13 | n_leaf_pages | 7 | NULL | Number of leaf pages in the index | | base | t_config | fk_cfg_rusr | 2020-04-10 10:59:13 | size | 8 | NULL | Number of pages in the index | | base | t_config | fk_cfg_uusr | 2020-04-10 10:59:13 | n_diff_pfx01 | 6 | 7 | r_updusr | | base | t_config | fk_cfg_uusr | 2020-04-10 10:59:13 | n_diff_pfx02 | 5559 | 7 | r_updusr,recid | | base | t_config | fk_cfg_uusr | 2020-04-10 10:59:13 | n_leaf_pages | 7 | NULL | Number of leaf pages in the index | | base | t_config | fk_cfg_uusr | 2020-04-10 10:59:13 | size | 8 | NULL | Number of pages in the index | | base | t_config | uq_cfg_1 | 2020-04-10 10:59:13 | n_diff_pfx01 | 25 | 16 | cdgrp | | base | t_config | uq_cfg_1 | 2020-04-10 10:59:13 | n_diff_pfx02 | 5677 | 16 | cdgrp,tshort | | base | t_config | uq_cfg_1 | 2020-04-10 10:59:13 | n_diff_pfx03 | 5774 | 16 | cdgrp,tshort,numint | | base | t_config | uq_cfg_1 | 2020-04-10 10:59:13 | n_leaf_pages | 16 | NULL | Number of leaf pages in the index | | base | t_config | uq_cfg_1 | 2020-04-10 10:59:13 | size | 17 | NULL | Number of pages in the index | +---------------+------------+-------------+---------------------+--------------+------------+-------------+-----------------------------------+ 16 rows in set (0.001 sec) >>explain select cdgrp, tshort from t_config where cdgrp = 'CDGRP'; +------+-------------+----------+------+---------------+----------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+---------------+----------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t_config | ref | uq_cfg_1 | uq_cfg_1 | 34 | const | 31 | Using where; Using index | +------+-------------+----------+------+---------------+----------+---------+-------+------+--------------------------+ 1 row in set (0.000 sec) >>select cdgrp, tshort from t_config where cdgrp = 'CDGRP'; Empty set (0.000 sec) >>select cdgrp, tshort from t_config where cdgrp = 'cdgrp'; Empty set (0.000 sec) >>select cdgrp, tshort from t_config where cdgrp like 'CD%'; Empty set (0.000 sec) >>select cdgrp, tshort from t_config where cdgrp like 'c%'; Empty set (0.000 sec) >>select cdgrp, tshort from t_config where sysflag = 1; +-------+----------+ | cdgrp | tshort | +-------+----------+ | CDGRP | ApplInfo | | CDGRP | ObjInfo | | CDGRP | RecFlg | | CDGRP | Role | | CDGRP | Unit | | CDGRP | TxtBlk | | CDGRP | PrsTitl | | CDGRP | PrsTyp | | CDGRP | Domain | | CDGRP | Lang | | CDGRP | Calendar | | CDGRP | TVA | | CDGRP | Quali | | CDGRP | Gender | | CDGRP | MdiaTyp | | CDGRP | DocTyp | | CDGRP | ProdGrp | | CDGRP | StatBill | | CDGRP | Archive | | CDGRP | Currency | | CDGRP | Region | | CDGRP | MIC | | CDGRP | Spell | | CDGRP | rate | | CDGRP | TZ | | CDGRP | CStat | | CDGRP | WStat | | CDGRP | HRStat | +-------+----------+ 28 rows in set (0.005 sec) >>analyze table t_config; +---------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+---------+----------+----------+ | base.t_config | analyze | status | OK | +---------------+---------+----------+----------+ 1 row in set (0.019 sec) >>analyze table t_config persistent for all; +---------------+---------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+---------+----------+-----------------------------------------+ | base.t_config | analyze | status | Engine-independent statistics collected | | base.t_config | analyze | status | OK | +---------------+---------+----------+-----------------------------------------+ 2 rows in set (0.065 sec) >>select cdgrp, tshort from t_config where cdgrp = 'CDGRP'; Empty set (0.001 sec) >>drop index uq_cfg_1 on t_config; Query OK, 0 rows affected (0.023 sec) Records: 0 Duplicates: 0 Warnings: 0 >>alter table t_config add constraint uq_cfg_1 unique (cdgrp,tshort,numint); Query OK, 0 rows affected (0.053 sec) Records: 0 Duplicates: 0 Warnings: 0 select cdgrp, tshort from t_config where cdgrp = 'CDGRP'; +-------+----------+ | cdgrp | tshort | +-------+----------+ | CDGRP | ApplInfo | | CDGRP | Archive | | CDGRP | CStat | | CDGRP | Calendar | | CDGRP | Currency | | CDGRP | DocTyp | | CDGRP | Domain | | CDGRP | Gender | | CDGRP | HRStat | | CDGRP | Lang | | CDGRP | MIC | | CDGRP | MdiaTyp | | CDGRP | ObjInfo | | CDGRP | ProdGrp | | CDGRP | PrsTitl | | CDGRP | PrsTyp | | CDGRP | Quali | | CDGRP | RecFlg | | CDGRP | Region | | CDGRP | Role | | CDGRP | Spell | | CDGRP | StatBill | | CDGRP | TVA | | CDGRP | TZ | | CDGRP | TxtBlk | | CDGRP | Unit | | CDGRP | WStat | | CDGRP | rate | +-------+----------+ 28 rows in set (0.001 sec) select cdgrp, tshort from t_config where cdgrp = 'cdgrp'; Empty set (0.001 sec) EOF