- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL Performance: System Config & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
- MySQL Performance: MySQL/MariaDB Indexes
- MySQL Performance: Intro to JOINs in SQL
Data in a MySQL/MariaDB database is stored in tables. A simple way of thinking about indexes is to imagine an extensive spreadsheet. This type of system is not always conducive to quick searching; that’s where an index becomes essential. If there is no index, then the database engine has to start at row one and browse through all the rows looking for the corresponding values. If this is a small table, then it is no big deal, but in larger tables and applications where there can be tables with millions and even billions of rows, it becomes problematic. As you can imagine, searching through those rows one by one will be time-consuming, even on the latest hardware. The solution is to create an INDEX (or more than one) for your data.
What Is An Index?
An index is an organized lookup table that contains pointers to where data can be retrieved in the database. This concept is similar to book indexes. In a book, the index lists page numbers where various topics are located. In a database, the index stores pointers that are in a logical and sorted order, allowing speedy lookups in the database. Imagine a book without an index. If you want to look up information on a specific topic, you have to start at the beginning and flip through the pages until you find the information you are looking for. A book index solves this by telling you exactly which pages the information you are looking for is. This is essentially how a MySQL index works.
When Does MySQL Use Indexes?
Anytime the database server has to look up information, it will use an index to speed up the process. Examples of this include WHERE, JOIN ON, MIN/MAX, and ORDER BY/GROUP BY clauses. If your queries are going to search or match a column often in any of these situations, you should consider creating an index.
Do Indexes Improve Database Performance?
Most of the time, the short answer is yes. For reads and searches through the data, indexes absolutely increase performance. Not doing full table scans will improve just about any SELECT query. This does come at a small price. For columns that are often manipulated, there is a cost for each change. This can bog down INSERTs and UPDATEs on the database.
Common MySQL Index Types
There are three common types of MySQL indexes:
- Primary Key
A primary key tells the database server which is the most crucial column(s) in the table. Typically, this is used for auto_increment id columns. For instance, if you have a table that contains employee information, you would want a primary key on the “EMPLOYEE_ID” column. In other tables, i.e., a salary table, you would simply refer to the EMPLOYEE_ID instead of all of the employee’s data.
A standard INDEX is used to tell the database server that one or more of the columns in the table are going to be searched a great deal, and thus an index should be created on them. This can be built during the table creation or if you find you need an index on a column later, it can be added then.
A UNIQUE index is a bit distinct in that it puts specific constraints on what can be written into the table. If you try to insert a row that contains a value that is already included within the data, then the INSERT will produce an error. This allows for quick searches as well as some data integrity.
How To Create An Index
There are multiple ways to create an index on a column. You can set up an index when you CREATE TABLE, or afterward with either the ALTER TABLE or CREATE INDEX statements.
Creating a PRIMARY KEY Index
The easiest way to create a PRIMARY KEY index is to do it when you create the table. We will use an example of an employee table for this tutorial. The table will have three columns: ID, FIRSTNAME, and LASTNAME. The create may look something like this:
CREATE TABLE EMPLOYEE ( ID INT, FIRSTNAME CHAR(32), LASTNAME CHAR(32) );
This statement will create the table without an index. We can add the primary key with a statement like this:
ALTER TABLE EMPLOYEE ADD CONSTRAINT PRIMARY KEY (ID);
This tells MySQL that we have a primary key on the column ID.
A more straightforward way to do this is during the creation of the table. Starting over, we will recreate the table and add the primary key in one statement.
CREATE TABLE EMPLOYEE ( ID INT, FIRSTNAME CHAR(32), LASTNAME CHAR(32), PRIMARY KEY (ID) );
This single statement performs the same tasks as the first two statements above. A primary key can contain more than one column, but the columns must all be unique. The primary key can be used to identify any individual row in the table.
Creating An Index Using CREATE INDEX
Using the example table above, imagine that the company has thousands of employees, and you want to be able to search the employees quickly by the last name. With a primary key, there can only be one entry. You can’t have multiple people who have the same employee ID. With last names, it is quite common to have multiple people share that name. Again, there are a few ways to do this. Using the table above:
CREATE TABLE EMPLOYEE ( ID INT, FIRSTNAME CHAR(32), LASTNAME CHAR(32), PRIMARY KEY (ID) );
We can add an index to the column LASTNAME like this:
CREATE INDEX idx1 ON EMPLOYEE (LASTNAME);
Searches by LASTNAME will now be faster on this table. In this example, “idx1” is the name of the index. This will make it easier to reference the index itself in the future if we need to.
Another way to add an index to a table is with the ALTER TABLE command. To accomplish the same thing we did with the previous statement, we would run the following command:
ALTER TABLE EMPLOYEE ADD INDEX idx1 (LASTNAME);
The final way to add an index that would save some time is to do it during the CREATE TABLE. If you know up front that you are going to be searching or joining on a specific column then you can create the initial table with the index already there:
CREATE TABLE EMPLOYEE ( ID INT, FIRSTNAME CHAR(32), LASTNAME CHAR(32), PRIMARY KEY (ID), INDEX idx1 (LASTNAME) );
All three of these statements create the same result.
An index doesn’t only have to be on a single column. If we want faster lookups on both FIRSTNAME and LASTNAME, we can create an index on both columns. In the table create it would look like this:
CREATE TABLE EMPLOYEE ( ID INT, FIRSTNAME CHAR(32), LASTNAME CHAR(32), PRIMARY KEY (ID), INDEX idx2 (LASTNAME,FIRSTNAME) );
Using the CREATE INDEX command:
CREATE INDEX idx2 ON EMPLOYEE (LASTNAME, FIRSTNAME);
And finally, the ALTER TABLE command:
ALTER TABLE EMPLOYEE ADD INDEX idx2 (LASTNAME, FIRSTNAME);
Creating A UNIQUE INDEX
There may be times when you would like to create an index on a column and also force all entries in that column to be unique. If we extended our example table to include an OFFICE column (denoting which office in the building they have), then we may want to make this a UNIQUE index. Let's work with this table:
CREATE TABLE EMPLOYEE ( ID INT, FIRSTNAME CHAR(32), LASTNAME CHAR(32), OFFICE INT, PRIMARY KEY (ID) );
Notice that now we have an integer stored in the table to denote which office the person occupies. This may refer to another table, “OFFICES,” which will contain information about various offices in the building.
To create an index on this, we would issue the following command:
CREATE UNIQUE INDEX idx3 ON EMPLOYEE (OFFICE);
You can use similar commands to create these in the CREATE TABLE and ALTER TABLE commands. Now if you tried to run these two inserts:
INSERT INTO EMPLOYEE (ID, FIRSTNAME, LASTNAME, OFFICE) VALUES (1, 'JANE', 'JOHNSON', 1); INSERT INTO EMPLOYEE (ID, FIRSTNAME, LASTNAME, OFFICE) VALUES (2, 'JOHN', 'SMITH', 1);
You would receive an error on the second INSERT:
Duplicate entry '1' for key 'idx3'
Removing An Index
There may come a time when you no longer want an index on a column. Perhaps you realized that you do not search a column all that often, and the index is slowing down inserts and updates. To do this, we will use the DROP INDEX command. Our table was created like this:
CREATE TABLE EMPLOYEE ( ID INT, FIRSTNAME CHAR, LASTNAME CHAR, PRIMARY KEY (ID), INDEX idx (LASTNAME,FIRSTNAME) );
If we want to remove the index on the columns LASTNAME and FIRSTNAME, we will issue this command:
DROP INDEX IDX ON EMPLOYEE;
Database indexes are a fundamental tool that optimizes the speed of data retrieval within a structure which takes advantage of search and read performance in a table. Index enhancement is primarily driven at the cost of more writes and/or storage space in order to maintain an optimal index data structure.
MySQL and MariaDB databases offer multiple other searches and read options, including many that were not covered here. Key takeaways here include:
- Ensure you take the time to plan out your tables in advance.
- If you do not plan ahead when creating the tables, evaluate the columns that are being searched the most and create an index for them.
See It In Action!
Our broad line of dedicated servers provides the power, stability, and security needed to host the smallest database for growing businesses up to a high-availability, clustered servers setup for the largest of corporations. Want to see what solutions we can offer to meet your needs?
- Remove Permissions for a MySQL User on Linux via Command Line
- Grant Permissions to a MySQL User on Linux via Command Line
- Create a MySQL User on Linux via Command Line
- Delete a MySQL or MariaDB Database via Command Line
- Select a MySQL Database on Linux via Command Line
- Create a MySQL Database on Linux via Command Line
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.