• Tweet
• Sumo

• Tweet

Ok, so maybe I’m done with the Database Management course, but that doesn’t mean that I’m done playing around with the database. This is just some easy to follow guide how to see the difference that an index made in a real database system using MySQL.

To see the significant time difference of a query with and without index, we will need to generate a significant amount of records and you know I’m not talking about a hundred or two hundred records here, I’m talking about at least one hundred thousand (100,000) records to one million (1,000,000) records.

The first question that probably come to your mind is ‘How can I create such huge amount of data?’. The answer is you don’t, let an application do that for you, and its name is Benerator. Anyway, let’s see the steps I did for this query performance test.

Step 1 – Create a Database

Clear enough, to test a database you need a database, so I execute this query:

[sql]CREATE DATABASE TestIndex;[/sql]

Step 2 – Create a Table

We will create a very simple table, it’s a Person table. So the query is:

[sql]

USE TestIndex;

CREATE TABLE Person (id int, name varchar(20), INDEX(name));

[/sql]

Step 3 – Create the Data

Ok, so this is the interesting part, how can we generate that one million (1,000,000) records? I use an open-source data generator application called Benerator. It’s a powerful application that can actually do more than just generating data, but we will only use its ability to generate a CSV data that can be imported to MySQL.

If you are familiar with Benerator already, basically this is the benerator.xml file I use to generate the data:

[xml]

[/xml]

I will not discuss how to use Benerator here because it needs another blog post on its own. However this is the CSV file that I’ve generated using Benerator:

users.csv

Step 4 – Import the Data

I’m going to be honest with you, I don’t know how to import CSV file using MySQL command line, I use a GUI query editor for Mac OS X, Sequel Pro. If I’m using Windows or Linux I would use SQLyog, it beats the other GUI query editor I know in terms of importing CSV file speed.

Different GUI query editor will have different menu position for importing the CSV, so just try to find the option, it shouldn’t be hard to find.

Step 5 – Testing the query

After a couple of seconds importing the CSV file, it’s now the moment of truth.

With Index

If you follow my create table query above then you should have a very simple table with no index at all. Now try to run the following query:

[sql]SELECT * FROM Person WHERE name = ‘Oufmwlkoxrwal’;[/sql]

The time needed to execute the query in my laptop is 0.6 ms. Now let’s see how long does it take without index.

No Index

We’ll first need to remove the index from the table by running this query:

[sql]ALTER TABLE Person DROP INDEX ‘name’;[/sql]

Now running the same query, it took 233 ms. Now that’s a lot of difference, from 0.6 ms without the index it jumps to 233 ms, or 389 times more.

Conclusion

Allright, maybe this is a very simplified example of how index affects query speed. In fact the time difference is unnoticable by any human because both are less than one second. But it display a good example how putting index can increase the query speed significantly. 😀