Optimization

Chapter 7 - Advanced SQL Usage
byGareth Downes-Powellet al.
Wrox Press 2003

When you want to look up an old friend's telephone number in a telephone directory, how do you do it? Would you go to the first page and start reading all names until you find your friend's name? That would take a very long time and you would get hungry quickly before even reaching the letter 'B'.

Instead you take the index part of the directory, look up your friend's name first letter, read the starting page and go there. Next you will look up the second letter and so on. It takes less than a minute to find a person in a book that may contains thousands of names.

In this example we have introduced the concept of optimization. Just think about it: instead of hours, if not days, you took a minute to find the information you were looking for.

Now apply this concept to a query where you would look up one row of information between thousands. Using the index would speed up the search tremendously. The second part of this chapter is about optimization. We will look at how to optimize your database to get the best performance out of it.

Indexes

In this section we will briefly cover what an index is, and how it can help you, before moving on to adding indexes to existing tables.

What Is an Index?

An index is a collection of data that is hidden from the user, and is organized in a way that allows a search to be executed extremely quickly. This data is actually a part of the real data it works with. When you define an index, you specifically define it to work on one table. An index cannot be spanned across multiple tables, and furthermore across multiple databases.

You will not be able to query against an index. All you can know about indexes is that they exist, the size in bytes they represent and how they are organized. The data is organized to allow quick searching; MySQL references the index when you execute a query on the relevant table.

Consider the directory example. You found your friend's name in less than a minute. How did you do that? You implicitely used the fact that the names are sorted in an alphabetical order. It is easy to know that the name "Doe" is placed way before the name "Smith". So if you are at the page containing the name "Gill" and you search the name "Doe" you know you must look at the earlier pages.

But the data in the table is not automatically organized in alphabetical order. It is stored in the exact same order that you inserted it. In the case of the directory, it is obvious that it will be sorted by name, but how would MySQL know which row it should use? Suppose that you have to look up a telephone number now, and not a name.

To address this issue, the database designers introduced the index concept. In our directory example, an index could be a list of letters with the page the names start at. You can have many indexes on a table. We could define a second index that would know the telephone number and the associated page.

MySQL Indexes

MySQL supports four types of index:

• Primary key

• Unique index

• Multiple index (also called a classic index, or just index)

• The Full-Text index.

All these indexes make use of the techniques described above.

Primary Keys

The primary key is a core concept in SQL. We have already encountered some use of a primary key. The table room of our hotel reservation application has a primary key made of the field 'ID'. To illustrate the concept of a primary key, we will compare it to a simplified postal address system.

In your house's street, if you want to identify your house, you will, in most cases, use the number of your house. The house that has the number 1 is obviously different from the house that has number 10. But there is certainly not only one house in your city that has the number 1.

So what do we do if we want to identify a house in a city? We would use the street and the house's number. A street name and a number are sufficient to find a house in a city, but what if we are speaking about the whole country? We need to specify the city also.

This way, we can identify a house anywhere in the country. If we want to find a house anywhere in the world, all we have to do is to add the country on the address, and we are done.

What have we done in this example? We started by defining the house number, and then we noticed that it is just enough for one street. Then we added the street, then the city and finally the country. By doing this, we have ensured a group of data that identify one and only one house in the world.

We could therefore say that we have a primary key for identifying a house. A more rigorous definition of a primary key is:

Important 

A primary key is a set of fields in a table that identifies in a unique manner each and every row of a table.

How to choose the fields of the primary key? It is not that difficult to get the fields that will fulfill the requirements of the primary key definition. Either you precisely know them, or you can create a new unique numeric field.

auto_Increment

In the table room, how did we choose the primary key? Well, no fields could exactly represent a room in a unique manner. Therefore we add a field named 'ID'. The field ID is numeric and has the extra features auto_increment. The auto_increment attribute is a specific feature made specifically for the primary key. That way, we don't need to make sure the 'ID' value of a row is unique, MySQL will find the unique value for you.

You may then ask: why not always use a 'ID' field for each table we create in MySQL? First, because it is not always required: Sometimes you will build a very short table where the primary key will not be a numeric type. My suggestion is to use it whenever you don't know what to use as the primary key. This happens in 99% of the tables you will create.

The Unique Index

With an UNIQUE index, you ensure that there are no two rows in the table that have the same value. If you create a unique index on the field 'number' of the table room, you are sure that there won't be two rooms that will have the same number in your table.

MySQL itself enforces these constraints. MySQL won't let you insert the same value twice in a column that has been indexed as unique.

Important 

Note that if you do not specify a primary key, but have a unique index, MySQL will silently convert it into a primary key.

The Multiple Index

The multiple index is the most common type of index. It provides the basic functionality of an index. It doesn't have any specific features. In MySQL you will see it called 'index'. You may refer to a normal index with the keyword index. See the section about creating an index to see examples of the normal index.

The Full-Text Index

The full-text index type is a special index type. It provides the functionality for searching through long text values (and thus will only index VARCHAR and TEXT columns). The lookup on a full-text index has been designed to search on human language. More information on full-text searching can be found at http://www.mysql.com/doc/

Handling Indexes On Existing Tables

To create an index, you will use the ALTER TABLE SQL command. We will review here the part of the ALTER table syntax that allows you to create, change, and delete indexes on a table. Normally you would create indexes at the table creation time, but it may happen that you want to add an index on an existing table. The following table shows you the syntax of the ALTER TABLE for the INDEX part:

    ALTER TABLE table_name alter_spec [, alter_spec ...] 

The alter_spec explains to MySQL how you want to alter the table. Here is the syntax for the alter_spec expression:

    ADD INDEX [index_name] (index_col_name, ...)    OR ADD PRIMARY KEY (index_col_name,...)    OR ADD UNIQUE [index_name] (index_col_name,...)    OR ADD FULLTEXT [index_name] (index_col_name,...)    OR DROP PRIMARY KEY    OR DROP INDEX index_name 

Creating an Index

To create a new index on an existing table, you will use the ADD index_type index_name (index_col_name,...) command. For example, if we want to index the column 'number' of the table room we would do the following:

    ALTER TABLE room ADD INDEX (number) 

This SQL command will create an INDEX named number containing the field 'number'. It is called automatically by MySQL according to the first column in the column list and the first available index name for this column. So if the table room already has an index named 'number' the second one will be called 'number_2'

The name of indexes exists only to reference them when using the JOIN keyword, where you can specify that you specifically want to use an index. This index is a normal index type. If we wanted to create a unique index the SQL query would have been:

    ALTER TABLE room ADD UNIQUE (number) 

And in the case of a full-text index type:

    ALTER TABLE room ADD FULLTEXT (number) 

Altering an Index

In MySQL, it is not possible to alter an index. If you want to change something on an existing index, you first have to delete this index and creating a new one with the new structure. If you don't care about the index name, you can create the new index first and then delete the old one.

Deleting an Index

To delete an index, the alter_expression will be 'DROP INDEX index_name'. If you want to delete the previously created index on the number column of the table room, you will use the following SQL command:

    ALTER TABLE room DROP INDEX number 

A Note On Indexes

Indexes are very powerful, but they have their drawbacks. They are very efficient when doing a SELECT on an indexed table. But they need to be accurate. That means that each time you insert a new row into your database the index must be updated. If you have many indexes or if your table contains a lot of records it can slow down the insert time.

You should not create an index on each field of a table. Instead, my suggestion is to design completely your application and the tables. Then review your SELECT query and check for those that are executed many times and that could benefit from using an index.