Indexing MySQL Tables
One of the best ways to improve the efficiency of your MySQL database and increase the speed of your SELECT queries is to create table indexes. This article will explain the reason for creating indexes, the pros and cons of indexing, and how to create basic indexes.
Before we get into the meat and potatoes of creating indexes, let's take a step back and discuss why you should index your MySQL tables. Databases are made up of tables. Each table is made up of rows and columns. When you run a query to extract information from a database, MySQL has to search through the tables to find the information. If you have a small database with a few dozen or even a few hundred records, then MySQL only has to search through a small amount of data.
However, if you have thousands or even hundreds of thousands of records in numerous tables, then it becomes inefficient to search through those records without indexes. This inefficiency can lead MySQL to slow down or even lock up. This is where indexing comes into play.
Table indexes allow MySQL to look up rows more quickly. A MySQL table is an unsorted collection of rows. Indexes let MySQL find matching rows more quickly because they store the indexed column in sorted order. When searching an indexed column, MySQL doesn't need to scan the entire unsorted table to find a match; it can search the column's sorted index more quickly instead.
For example, let's say pair Networks had a table in its customer database with the columns: customer_last_name, account_type, and monthly_fee. Without indexing, if you were to run a query to extract information on customers whose last name is Smith and who has a Webmaster account type, then MySQL would have to search through the entire table to extract the information.
However, if you created an index on the "customer_last_name" and "account_type" columns, MySQL will look at the table and see the indexes on these columns. MySQL then looks at your query and decides which of those indexes is likely to give the best performance for the query. It uses the "best" index for the query and does not use the other index. There are even occasions where MySQL will simply search the table instead of the index because it wasn't worth it performance-wise to use the indexes.
Now that you have a little background on indexes, let's go over the different types of indexes available in MySQL:
- Regular (non-unique) indexes: An index that allows duplicate values. A "NOT NULL" option is available for indexes which aren't primary keys.
- Unique index: An index that disallows duplicate values, which means that for single-column indexes, no values are duplicated in the index, and for multiple-column indexes, no combination of values are duplicated. A "NOT NULL" option is available for indexes which aren't primary keys.
- Primary Key index: Similar to a Unique index but which does not allow "NULL" values and allows only a single primary key index per table.
- FULLTEXT index: Used for full text searches, which needs a specific table type called MyISAM, which is the default table type used at pair Networks. Please note that a FULLTEXT index should only be used with text columns such as "var" and "char" and should NOT be used with "int" columns.
You can create more than one index per table, but it is not required. MySQL will only ever use one index per table for a given query. Having more than one index on your table is useful when you are running several different queries on a table. But for a single query, it is more important to have the right type of index than lots of indexes. Remember that while indexes can speed up SELECT queries if created correctly, they also slow down INSERT/UPDATE queries.
Below is an example of how to create one regular, single-column index for one table:
ALTER TABLE tbl_name ADD INDEX index_name (index_column);
As you can see in this example, if your table already exist, you can use the ALTER TABLE command to create indexes. In this example, replace "tbl_name" with the name of the table you wish to index. Replace "index_name" with an index name of your choosing. Replace "index_column_1" with the name of the columns you wish to index.
Here's an example of adding different types of multiple-column indexes to a particular table:
ALTER TABLE tbl_name ADD INDEX index_name (index_column_1, index_column_2);
ALTER TABLE tbl_name ADD UNIQUE index_name (index_column_1, index_column_2);
ALTER TABLE tbl_name ADD PRIMARY KEY index_name (index_column_1, index_column_2);
ALTER TABLE tbl_name ADD FULLTEXT index_name (index_column_1, index_column_2);
In this example, replace "index_column_1" and "index_column_2" with the names of the columns you wish to index. There are certain times, however, when you should use single-column indexes and when you should use multiple-column indexes.
Let's say we wanted to create a multiple-column index for a table that listed pair Networks customer names and account types. The command to create this type of index could look like:
ALTER TABLE tbl_name ADD INDEX index_name (name, acct_type);
This is a good option if you wish to run queries that reference both name and account type columns or just the name column. However, if you want to run queries on the account type column without referencing the name column, you should create two single-column indexes as demonstrated below:
ALTER TABLE tbl_name ADD INDEX index_name (name);
ALTER TABLE tbl_name ADD INDEX index_name (acct_type);
Creating two separate, single-column indexes will allow you to search by both name and account type as well as by name and account type separately. Please note that two separate indexes are not always as good as a multiple-column index if you're searching both columns in a single query. Please note that only one index per table is used in any MySQL query.
If you are creating a table from scratch, follow the example below to add indexes to the table:
CREATE TABLE tbl_name
(
...column declarations...
INDEX index_name (index_column_1),
UNIQUE index_name (index_column_1, index_column_2),
PRIMARY KEY (index_column_1),
FULLTEXT index_name (index_column_1, index_column_2),
);
In the example above, you must replace "index_name" with the name of the index. In addition, the example above simply shows how to create different types of indexes. You do not need to use different index types when creating indexes.
If you leave out a name in the example above, MySQL will create one for you. Primary key indexes do not need a table name because there can only be one primary key index per table. When creating indexes for the MyISAM table type, you can have NULL values except when creating a Primary key index or for regular or unique indexes which have been declared "NOT NULL" as in the example below:
INDEX index_name (index_column_1) NOT NULL,
NULL values are fields that do not contain any data. Please note that if you attempt an INSERT command which violates a NOT NULL or UNIQUE restriction, the INSERT query will fail with an error. If you do not check for errors after every INSERT, you may lose data inadvertently.
One other note on indexing: For CHAR, VARCHAR, BLOB, and TEXT columns, you could specify the length of the index using the format: index_column(length). You do not have to do this, but for BLOB and TEXT columns, there are byte limits.
While this article has presented the benefits of indexing, there are potential drawbacks to watch out for as well. Using indexes properly can improve SELECT queries; however, they also slow down INSERT/UPDATE queries. FULL TEXT indexes will especially cause INSERT commands to run slowly.
Other ways that indexes can actually slow down performance are if an index has many duplicated values. For example, if a certain "flag" can only be turned "ON" or "OFF," and if the table has 50% "ON" values and 50% "OFF" values, then the index may not be used.
Lastly, if you're matching a column by something other than the first part of the column, then using the index will not help. For example, if you're searching a "name" field for "bob%," then it will use the index. However, if you search for "%smith," then it will not use the index because there's no benefit to the sorted index.
Bottom line: make sure you only create indexes that will be used with your queries to ensure optimal performance. Indexes are mostly useful if the ratio of SELECT to INSERT queries is high enough. If you will be using significantly more SELECT queries than INSERT queries, then indexing may be able to improve your database performance. However, if you are frequently updating your database, for example a busy bulletin board system, then it may not be beneficial to use indexes on your database tables. There are not hard and fast rules on when to use indexes and when not to use indexes.
One simple trick to optimize indexes for one query is to add a bunch of different indexes to the tables, and then use the EXPLAIN command to have MySQL tell you which indexes it is using and which it is not using.
It should also be noted that indexing will not fix all MySQL performance issues, but it is a good place to start to improve your database efficiency, particularly if you have a large database or use complex queries. This article only scratches the surface of indexing. For more detailed information on indexing, please consult the official MySQL online manual at:
http://dev.mysql.com/doc/
|
Volume 10 Number 5 Issue 109