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

How to do it...

You can see the indexes of a table by viewing its definition. You will notice that there is an index on first_name and last_name. If you filter the rows by specifying first_name or by both (first_name and last_name), MySQL can use the index to speed up the query. However, if you specify only last_name, the index cannot be used; this is because the optimizer can only use any of the leftmost prefixes of the index. Refer to https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html for more detailed examples:

mysql> ALTER TABLE employees ADD INDEX name(first_name, last_name);Query OK, 0 rows affected (2.23 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW CREATE TABLE employees\G*************************** 1. row ...

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