O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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`), ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required