Mastering The Faster Web with PHP, MySQL, and JavaScript by Andrew Caya

Author:Andrew Caya [Andrew Caya]
Language: eng
Format: epub
Tags: COM060080 - COMPUTERS / Web / General, COM051260 - COMPUTERS / Programming Languages / JavaScript, COM051400 - COMPUTERS / Programming Languages / PHP
Publisher: Packt Publishing
Published: 2018-08-14T05:29:12+00:00

Confirmation that the index was created

Now that the index has been created, we obtain this result when asking the database engine to EXPLAIN its execution plan:

The execution plan is now optimized

The type column's value is now ref, possible_keys is idx_first_name, key is idx_first_name, ref is const, rows is 1 and Extra is Using index condition. As we can see, the engine has now identified our newly created index as a possible key to use and then proceeds to use it. It uses the constant value given in our query to perform the lookup in the index and considers only one row when accessing the table. All of this is great but, as we expected in our initial considerations, the index is composed of non-unique values. The possible equivocity amongst values of the table column might lead to a degenerated index over time, hence the access type of ref and the extra information indicating that the engine is Using index condition, which means that the WHERE clause is pushed down to the table engine for optimization at the index level. In this example, with the admitted initial considerations, this is, in the absolute sense, the best query optimization that we can do, as it is impossible to get unique values in the first_name column of the actor table. But, in fact, there is a possible optimization depending on the domain use case. If we only wish to use the actor's first name, then we could further optimize the Using index condition in the Extra column by only selecting the appropriate column, thus allowing the database engine to only access the index:

# MariaDB > EXPLAIN SELECT first_name FROM actor WHERE first_name = 'AL';

The database engine then confirms that it is only using the index in the Extra column:

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.