MySQL 8 supports invisible indexes, This allows you to on-demand enable\/disable indexes from being used by MySQL optimizer. Now please don’t get confused with “disabled indexes<\/em>“,\u00a0“invisible indexes are not disabled indexes, MYISAM supports disabled indexes, ”\u00a0<\/strong>, The disabled indexes halt maintenance of an index. Invisible indexes are a new feature in MySQL 8.0 , which mark an index unavailable for use by the optimizer. That means, Index will still be maintained and keep up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses a FORCE INDEX hint) .<\/p>\n

Why we really love invisible indexes in MySQL 8.0?<\/strong><\/p>\n

\n
• You want to make only one query to use that index, In this case “invisible index” is a great option<\/li>\n
• On-demand indexing, You will have index (up-to-date with data) but you can make it visible or invisible. Even when optimizer force index, invisible indexes will not be invoked.<\/li>\n
• Testing which index is efficient for many queries, You can test them by enabling invisible index and do not forget to disable them before testing with another invisible index, You can test index efficiency in production system (instant gratification indeed !)<\/li>\n<\/ul>\n

  How can you create MySQL invisible indexes?<\/strong><\/p>\n

  There are two ways you can create invisible indexes:<\/p>\n

  Step1 is quite direct.<\/p>\n

  CREATE TABLE tab1 (\r\ncol1 int(10) DEFAULT NULL,\r\ncol2 int(10) DEFAULT NULL,\r\ncol3 int(10) DEFAULT NULL,\r\nKEY i_idx (col1),\r\nKEY idx_1 (col1,col2,col3) INVISIBLE\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1<\/pre>\n
  Step 2 is with “alter table script”<\/p>\n
  mysql> alter table tab1 alter index idx_1 invisible;\r\nQuery OK, 0 rows affected (0.06 sec)\r\nRecords: 0  Duplicates: 0  Warnings: 0\r\n<\/pre>\n
  Using invisible index\u00a0<\/strong><\/p>\n
  mysql> show create table tab1 \\G;\r\n*************************** 1. row ***************************\r\n       Table: tab1\r\nCreate Table: CREATE TABLE `tab1` (\r\n  `col1` int(10) DEFAULT NULL,\r\n  `col2` int(10) DEFAULT NULL,\r\n  `col3` int(10) DEFAULT NULL,\r\n  KEY `i_idx` (`col1`),\r\n  KEY `idx_1` (`col1`,`col2`,`col3`) \/*!80000 INVISIBLE *\/\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\r\n1 row in set (0.01 sec)\r\n<\/pre>\n
  Now, Let see we can forcefully make optimizer use the invisible index (here it is – idx_1)<\/p>\n
  mysql> explain select * from tab1 force index (idx_1) where col1=20 and col2=30;\r\nERROR 1176 (42000): Key 'idx_1' doesn't exist in table 'tab1'\r\n<\/pre>\n
  Let’s try, How we can forcibly make optimizer use the index i_idx (this works because index is not invisible)<\/p>\n
  mysql> explain select * from tab1 force index (i_idx) where col1=20 and col2=30;\r\n+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+\r\n| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |\r\n+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+\r\n|  1 | SIMPLE      | tab1  | NULL       | ref  | i_idx         | i_idx | 5       | const |    1 |    33.33 | Using where |\r\n+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+\r\n1 row in set, 1 warning (0.00 sec)\r\n<\/pre>\n
  We are converting the index “idx_1” visible to make optimizer forcibly use<\/p>\n
  mysql> alter table tab1 alter index idx_1 visible;\r\nQuery OK, 0 rows affected (0.05 sec)\r\nRecords: 0  Duplicates: 0  Warnings: 0\r\n<\/pre>\n
  mysql> explain select * from tab1 force index (idx_1) where col1=20 and col2=30;\r\n+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+\r\n| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra       |\r\n+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+\r\n|  1 | SIMPLE      | tab1  | NULL       | ref  | idx_1         | idx_1 | 10      | const,const |    1 |   100.00 | Using index |\r\n+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+\r\n1 row in set, 1 warning (0.00 sec)\r\n\r\nmysql> \r\n<\/pre>\n
  We cannot alter Primary Key to invisible index:<\/p>\n
  mysql> create table tab3\r\n    -> (col11   int(10)  not null, \r\n    -> col22    int(10)  not null, \r\n    -> col33    int(10) not null,\r\n    -> unique  uidx(col11));\r\nQuery OK, 0 rows affected (0.10 sec)\r\n\r\nmysql> alter table tab3 alter index uidx invisible; \r\nERROR 3522 (HY000): A primary key index cannot be invisible\r\nmysql> \r\n<\/pre>\n
  Conclusion<\/strong><\/p>\n
  MySQL 8.0 invisible index is an very interesting feature, This enables Database Architects \/ DBAs \/ Database Engineers to choose optimal indexing for \u00a0MySQL performance and scalability. We are very excited about this feature, This makes several of our customer applications more optimal..<\/p>\n
  MySQL 8 invisible indexes (MySQL official documentation) –\u00a0https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/invisible-indexes.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
  MySQL 8 supports invisible indexes, This allows you to on-demand enable\/disable indexes from being used by MySQL optimizer. Now please don’t get confused with “disabled indexes“,\u00a0“invisible indexes are not disabled indexes, MYISAM supports disabled indexes, […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[126,2,125,15,104],"tags":[127,128,16],"yoast_head":"\n