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

Open VDF: Öztürk Multiplier Design Overview

Get an android app for your website

Get an android app for your website

The 6 Best Standup Bots for Microsoft Teams in 2022

The Best 6 optionally free standup bots for Microsoft Teams in 2022: Standuply; AgilePolly; ScrumGenius; Team O’clock; Jell; ScrumGenius

How to Replicate and Isolate Projects

My experience of writing a Jinja extension

Why should I use a framework?

Plot on top of a Live Image Preview

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

Create Unit tests for WordPress Plugin

How to run feature tests in Laravel 9 in memory using SQLite

A new era for web development 🚀

How to build your own Captcha for security in Laravel — SS Blog

How to build your own Captcha for security in Laravel — SS Blog