MySQL Index - Overclock Your Tables

Hardware enthusiasts have been overclocking their PCs for years now, trying to push the limits of their hardware for maximum performance. Sometimes they are successful and their applications run speedy fast, while other times they push a little too hard and end up damaging the computer!


Although it isn't quite as extreme, indexes in MySQL can increase the speed of your MySQL queries to squeeze a bit more performance out of your database.

MySQL Index - Speed and Extra Overhead

Indexes are created on a per column basis. If you have a table with the columns: name, age, birthday and employeeID and want to create an index to speed up how long it takes to find employeeID values in your queries, then you would need to create an index for employeeID. When you create this index, MySQL will build a lookup index where employeeID specific queries can be run quickly. However, the name, age and birthday queries would not be any faster.
Indexes are something extra that you can enable on your MySQL tables to increase performance,cbut they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

Creating a MySQL Index - New Table

If you are creating a new MySQL table you can specify a column to index by using the INDEX term as we have below. We have created two fields: name and employeeID (index).

MySQL Code:

CREATE TABLE employee_records (
 name VARCHAR(50), 
 employeeID INT, INDEX (employeeID)
)

Creating a MySQL Index - Existing Table

You can also add an index to an older table that you think would benefit from some indexing. The syntax is very similar to creating an index in a new table. First, let's create the table.

MySQL Code:

CREATE TABLE employee_records2 (name VARCHAR(50), employeeID INT)

With our newly created table we are going to update the "employee_records2" table to include an index.

MySQL Code:

CREATE INDEX id_index ON employee_records2(employeeID)

We keep our existing employeeID field and create a new index id_index that is made up of employeeID data.

Posted in: MySQL