1Z0-908: MySQL 8.0 Database AdministratorDemo

Question

Consider the following statement, which executes successfully:

CREATE TABLE `t` (  
  `a` int(11) DEFAULT NULL,  
  `b` int(11) DEFAULT NULL,  
  KEY `b_idx` (`b`) /*!80000 INVISIBLE */,  
  KEY `ab_idx` (`a`,`b`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Which statement is true?

A If b_idx is converted to VISIBLE, then the execution plan for SELECT * FROM t WHERE b=3; is of type eq_ref.
B SELECT * FROM t WHERE b=3; performs a full table scan.
C b_idx must be VISIBLE for its statistics to be updated by the ANALYZE TABLE t command.
D ALTER TABLE t ADD INDEX ba_idx(b, a); returns an error.
E SELECT * FROM t FORCE INDEX(b_idx) WHERE b=3; returns an error.

Explanation

An invisible index is not available to the optimizer by default, and MySQL reports errors for queries whose index hints refer to an invisible index. Therefore, FORCE INDEX(b_idx) cannot be used while b_idx remains invisible.

Learn more

MySQL Reference Manual: Invisible Indexes

1Z0-908: MySQL 8.0 Database AdministratorDemo

AstroTutor

Notes

Readiness

QuestionQ196

Community Discussion