When To Use Indexes In MySQL

When deciding when and how to create an index in your MySQL database, it's important to consider how the data is being used.

Let's say you have a database of students. We will create it like this:

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `class` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by".

You should also pay attention to whether or not this information will change frequently, because it will slow down your updates and inserts. Since you wont frequently be adding students, you don't have to worry about the inserts

Let's say that you will be looking up the students with a web interface and the end user will be typing in the students name to find them, since remembering the students ID's would be cumbersome.

It sounds like this situation would be good to use an index.

• You won't be updating the student's name very often, so you don't have to worry about a performance hit there.
• you will be using the student in where clauses like this:

SELECT * FROM students WHERE first_name ='kawshi';

• You WILL be generating reports, which will probably be alphabetic, like this:

SELECT * FROM students ORDER BY first_name ASC;

           So in this simple example it's easy to see when it would be important to use indexes.

           So, you could do it like this:

CREATE INDEX name_index ON students (first_name);

You might be working on a more complex database, so it's good to remember a few simple rules.

• Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated.
• Indexes speed up where clauses and order by.
• It is always wise to start by indexing columns that join with other tables 

Summary

If you find your SELECT queries are taking a long time to run, you are most likely missing an index on your table. Look at which columns are searching on, and create an index on each one using the following syntax:

CREATE INDEX by_student_id ON grades (student_id);

It is always wise to start by indexing columns that join with other tables. After that, look for columns you know you will commonly search on. Focus on your largest tables.

References

[1]. https://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html