Table Optimization: EXPLAIN a Query - PHP

In this section, we want to get some information about the index that MySQL uses for a specific query, and the performance impact of not having defined an index.

Let's assume we want to use the following query:

SELECT *
FROM `books`
WHERE author_id = 2 AND language = 'es'

We want to know which books written by author 2 are in the es language, our code for Spanish.

To enter this query, we use the SQL link from the database or the table menu, or the SQL query window. We enter this query in the query box and click Go. Whether the query finds any results is not important right now.

Let's look at the header: SQL-query: [Edit] [Explain SQL] [Create PHP Code]

We will now use the [Explain SQL] link to get information about which index (if any) has been used for this query:

We can see that the EXPLAIN command has been passed to MySQL, telling us that the possible_keys used is author_language. Thus, we know that this index will be used for this type of query. If this index did not exist, the result would have been quite different:

Here, the possible_keys (NULL) and the type (ALL) mean that no index would be used, and that all rows would need to be examined to find the desired data. Depending on the total number of rows, this could have a serious impact on the performance. We can ascertain the exact impact by examining the query timing that phpMyAdmin displays on each results page and comparing with or without the index:

However, the difference in time can be minimal if we only have limited test data compared to a real table in production.

Summary

In this chapter we covered:

• How to add fields, including special field types like TEXT, BLOB, ENUM, and SET
• How to upload binary data into a BLOB field
• How to manage indexes (multi-field and full-text)
• How to get feedback from MySQL about which indexes are used in a specific query