Home Data News Best Practice for Creating Indexes on your MySQL Tables

Best Practice for Creating Indexes on your MySQL Tables

This image has an empty alt attribute; its file name is best-practices-for-creating-indexes-on-your-mysql-tables.jpg

<img “=”” src=”http://cdn.datafloq.com/blog_pictures/Best-Practices-For-Creating-Indexes-On-Your-MySQL-Tables.jpg”>

By having appropriate indexes on your MySQL tables, you can greatly enhance the performance of SELECT queries. But, did you know that adding indexes to your tables in itself is an expensive operation, and may take a long time to complete depending on the size of your tables? During this time, you are also likely to experience a degraded performance of queries as your system resources are busy in index-creation work as well. In this blog post, we discuss an approach to optimize the MySQL index creation process in such a way that your regular workload is not impacted.

MySQL Rolling Index Creation

We call this approach a ‘Rolling Index Creation’ – if you have a MySQL master-slave replica set, you can create the index one node at a time in a rolling fashion. You should create the index only on the slave nodes so the master’s performance is not impacted. When index creation is completed on the slaves, we demote the current master and promote one of the slaves that is up-to-date as the new master. At this time, the index building continues on the original master node (which is a slave now). There will be a short duration (tens of seconds) during which you will lose connectivity to your database due to the failover, but this can be overcome by having application-level retries.

Performance Benefits of Rolling Index Creation

We did a small experiment to understand the performance benefits of Rolling Index Creation.

The test utilized a MySQL dataset created using Sysbench which had 3 tables with 50 million rows each. We generated load on the MySQL master with 30 clients running a balanced workload (50% reads and 50% writes) for 10 minutes, and at the same time, built a simple secondary index on one of the tables in two scenarios:

  1. Creating the index directly on the master
  2. Creating the index on the slave

MySQL Test Bed Configuration

MySQL Instance TypeEC2 instance m4.large with 8GB RAM
Deployment Type2 Node Master-Slave Set with Semisynchronous Replication
MySQL Version5.7.25

Performance Results

ScenarioWorkload Throughput (Queries Per Second)95th Percentile Latency
Index Creation on Master453.63670 ms
Rolling Index Creation790.03390 ms


By running index creation directly on the MySQL master, we could experience only 60% of the throughput that was achieved by running index creation on the MySQL slave through a rolling operation. The 95th percentile latency of queries was also 1.8 times higher when the index creation happened on the master server.

Automating the Rolling Index Creation

ScaleGrid automates the Rolling Index Creation for your MySQL deployment with a simple user interface to initiate it.


In the UI above, you can select your Database and Table name, and ‘Add Index’ as the Alter Table Operation. Then, specify a Column Name and Index Name, and an Alter Table Command will be generated and displayed for you. Once you click to Create, the index creation will happen one node at a time in a rolling fashion.

Additionally, ScaleGrid also supports other simple Alter Table operations like adding a new column to your table in a rolling fashion. Stay tuned for my follow-on blog post with more details!

Source link

Must Read

Karl Friston: Neuroscience and the Free Energy Principle

https://media.blubrry.com/takeituneasy/s/content.blubrry.com/takeituneasy/lex_ai_karl_friston.mp3Karl Friston is one of the greatest neuroscientists in history, cited over 245,000 times, known for many influential ideas in brain imaging, neuroscience, and...

Extraction of Data from Templatic Documents

Templatic documents, such as receipts, bills, insurance quotes, and others, are extremely common and critical in a diverse range of business workflows. Currently, processing...

Using Blockchain for collaboration

<!----> Share this post:Blockchain and the disaster relief system Our latest guest is Chelsey Delaney, Global Design Lead for Blockchain Services at IBM, who brings a...

Open Banking and Blockchain

The finance industry continues to evolve thanks to technological advancement, sustained innovation, market dynamics, and changing customer needs. This evolution has accelerated in the last...

Biomedicine and Machine Learning

https://media.blubrry.com/takeituneasy/s/content.blubrry.com/takeituneasy/lex_ai_daphne_koller.mp3Daphne Koller is a professor of computer science at Stanford University, a co-founder of Coursera with Andrew Ng and Founder and CEO of insitro,...

A New Era Of Perceptive Intelligence

The human interface that connects us with machines — the way we interact and control them — has changed a lot over the years....

A Job interview with AI

https://vimeo.com/422241727"How do you feel about your relationship with your mother?" is not a question most people expect to be asked in a job interview. But in...

Preserving Privacy using A.I

I spent part of last week listening to the panel discussions at CogX, the London “festival of A.I. and emerging technology” that takes place each...

Achieving High Trading Profit With Software

Bitcoin trading has become one of the major sources of profit for long years. With its very promising and lucrative features, many individuals have...

Ripple Planning New XRP Corridor

Ripple’s senior VP of product management and corporate development, Asheesh Birla, says the San Francisco-based payments startup intends to open up new XRP remittance...
banner image