MySQL – Indexing

Database is nothing but collection of tables. Tables are useless unless we perform some operations on them. Operations on such tables require time to process. Sometimes these operations are pretty fast, sometimes not. In case of databases these operations get executed when we run some query. So depending on what and how much of information is being retrieved from the tables, queries may take some time to return what you want them to. But when this wait becomes annoyingly long, you need to think about it. You need to think judiciously on queries. And you need to add INDEXES.

As it happened in our case, not using indexes on our tables made our application slow. When we analyzed our database queries using EXPLAIN statement, we came to know that for almost all queries, all table rows were being queried.

Lets say we had Users table with total 7 users.

SQL> explain select * from users where role = ‘Director’;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users ALL (NULL) (NULL) (NULL) (NULL) 7 Using where

We added indexes then and reran the explain statement to check the result.

SQL> create index index_on_role on users(role);

SQL> explain select * from users where role = ‘Director’;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users ALL (NULL) (NULL) (NULL) (NULL) 1 Using where

Thus adding index on role resulted into reducing the row search from 7 to 1. Imagine thiss for millions of records. Thus adding indexes to your tables reorders the records in such a way that its retrieval becomes fast.

I will try to put up some thoughts on Indexing soon…till then stay indexed 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s