Multi-Field Indexes and Index Editing

In the next example, we will assume that in a future application we will need to find the books written by a specific author in a specific language. It makes sense to expand our author_id index, adding the language field to it.

We click the Edit link (small pencil) on the line containing the author_id index and choose to add one column to this index as shown in the following screenshot:

We select the language field on the next panel. This time we do not have to put a size since the whole field will be used in the index:

For better documentation, we should also change the key name (author_language would be appropriate). We save this index modification and we are back to:

FULLTEXT Indexes

This special type of index allows for full-text searches. It is supported on tables of type MyISAM for VARCHAR and TEXT fields. We can use the Fulltext quick link in the fields list or go to the index management panel and choose Fulltext in the drop-down menu:

We want a FULLTEXT index on the description field so that we are able to locate a book from words present in its description.

After the index creation, the index list looks like:

Seeing 1 as the field length here might be surprising. In fact, MySQL does not support the idea of an index length for FULLTEXT indexes: the index is always on the whole field, but this 1 is the value reported by MySQL.

Table Optimization: EXPLAIN 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 `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