Changing Table Structures
[ 112 ]
Table Optimization: Explaining a Query
: 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 `book`
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 (see Chapter 12). 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 links: [Edit] [Explain SQL] [Create PHP Code] [Refresh]
We will now use the [Explain SQL] link to get information about which index
(if any) has been used for this query:
Chapter 6
[ 113 ]
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 had not existed, the result would have been
quite different:
Here, 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.
Pages:
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123