Indexing Table in MySQL

Issue

A few days ago, I have uploaded my program to production, after ran for two days, my database got 20.000 rows stored data. This causes my program slow down (need mostly 30s to get 100 row) and I got “connection time out” for download feature. I thought “This is just 20K data, how about 1 million data, RIP my program haha”.

Solution

So I decided to make index for my table, the primary purpose is to provide an ordered representation of the indexed data through a logical ordering which is independent of the physical ordering. We do this using a doubly linked list and a tree structure known as the balanced search tree (B-tree).

This is my query for download feature.

Download Query

Before create index, we need to know the column combinations that appear in the WHERE clause. This is my query for create index.

Index Query

Result

Filtered 15.571 rows from 67.209 rows

Indexing column in my table increase performance significantly. Note that operations like UPDATE or INSERT become slower because they are actually two writing operations in the filesystem (one for the table data and one for the index data). Index also takes space in database.