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.

--

--

--

sarjanakomputer.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Getting Started With Modals

Using Google Sheets and Azure Functions as a headless CMS

Apache Zookeeper Internals

Building GKE with Terraform

New Release: Stripe, Klarna, Multi-store, Granular cache control in ScandiPWA 2.6.

Search in a Sorted Array of Unknown Size — Day 105(Python)

First Missing Positive — Day 94(Python)

MINX Transfer 2021

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ach Iqro

Ach Iqro

sarjanakomputer.com

More from Medium

Learn Lumen Framework

Static config variables on a Laravel project

How to Stop Spam Emails with reCAPTCHA, HTML & PHP

reCAPTCHA logo

Write Your Own Filters and Functions in Twig