Controlling the query optimizer

The task of the query optimizer is to find an optimal plan for executing a SQL query. There can be multiple plans to execute a query, especially when joining a table, where the number of plans to be examined increases exponentially. In this section, you will learn about adjusting the optimizer to your needs.

Take the example of the employees table and add the necessary index;

mysql> CREATE TABLE `employees_index_example` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `last_name` (`last_name`) /*!80000 INVISIBLE */,
  KEY `full_name` (`first_name`,`last_name`),
  KEY `full_name_desc` (`first_name` DESC,`last_name`),
  KEY `first_name` (`first_name`)...