Best Practice for Creating Indexes on your MySQL Tables
Tags: mysql, database, index, performance
Published: 2019-11-20 18:34:14

---
title: Best Practice for Creating Indexes on your MySQL Tables
published: true
description: How to add indexes to MySQL tables with rolling index creation to improve the performance of SELECT queries without impacting your regular workload - ScaleGrid Blog
tags: MySQL, Database, Index, Performance,
canonical_url: https://scalegrid.io/blog/best-practice-for-creating-indexes-on-your-mysql-tables/
---
<link rel="canonical" href="https://scalegrid.io/blog/best-practice-for-creating-indexes-on-your-mysql-tables/" />

<a href="https://scalegrid.io/blog/best-practice-for-creating-indexes-on-your-mysql-tables/"><img class="alignnone size-full wp-image-4829" src="https://scalegrid.io/blog/wp-content/uploads/2019/11/Best-Practices-For-Creating-Indexes-On-Your-MySQL-Tables.jpg" alt="alterblog3" width="100%" height="auto" /></a>
<p style="text-align: justify;">By having appropriate indexes on your MySQL tables, you can greatly enhance the performance of <a style="color: #2da964;" href="https://dev.mysql.com/doc/refman/5.7/en/select.html" target="_blank">SELECT</a> 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 <a style="color: #2da964;" title="Best Practice for Creating Indexes on your MySQL Tables - ScaleGrid Blog" href="https://scalegrid.io/blog/best-practice-for-creating-indexes-on-your-mysql-tables/" target="_blank">optimize the MySQL index creation process</a> in such a way that your regular workload is not impacted.</p>

<h2 style="padding-top: 15px;">MySQL Rolling Index Creation</h2>
<p style="text-align: justify;">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.</p>

<h2 style="padding-top: 15px;">Performance Benefits of Rolling Index Creation</h2>
<p style="text-align: justify;">We did a small experiment to understand the performance benefits of Rolling Index Creation.</p>
<p style="text-align: justify;">The test utilized a MySQL dataset created using <a style="color: #2da964;" href="https://github.com/akopytov/sysbench" target="_blank">Sysbench</a> 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:</p>

<ol>
	<li>Creating the index directly on the master</li>
	<li>Creating the index on the slave</li>
</ol>
<h3 style="padding: 15px 0px;">MySQL Test Bed Configuration</h3>
<table style="width: 100%;">
<tbody>
<tr>
<th style="padding: 10px; border: 1px solid #999; background: #f9ebd5;" width="30%">MySQL Instance Type</th>
<td style="padding: 10px; border: 1px solid #999;" width="70%">EC2 instance m4.large with 8GB RAM</td>
</tr>
<tr>
<th style="padding: 10px; border: 1px solid #999; background: #f9ebd5;" width="30%">Deployment Type</th>
<td style="padding: 10px; border: 1px solid #999;">2 Node Master-Slave Set with Semisynchronous Replication</td>
</tr>
<tr>
<th style="padding: 10px; border: 1px solid #999; background: #f9ebd5;" width="30%">MySQL Version</th>
<td style="padding: 10px; border: 1px solid #999;">5.7.25</td>
</tr>
</tbody>
</table>
<h3 style="padding: 15px 0px;">Performance Results</h3>
<table style="width: 100%;">
<tbody>
<tr>
<th style="padding: 10px; border: 1px solid #999; background: #f9ebd5;" width="30%">Scenario</th>
<th style="padding: 10px; border: 1px solid #999; background: #f9ebd5; line-height: 1.25em;" width="35%">Workload Throughput (Queries Per Second)</th>
<th style="padding: 10px; border: 1px solid #999; background: #f9ebd5;" width="35%">95th Percentile Latency</th>
</tr>
<tr>
<td style="padding: 10px; border: 1px solid #999;">Index Creation on Master</td>
<td style="padding: 10px; border: 1px solid #999;">453.63</td>
<td style="padding: 10px; border: 1px solid #999;">670 ms</td>
</tr>
<tr>
<td style="padding: 10px; border: 1px solid #999;">Rolling Index Creation</td>
<td style="padding: 10px; border: 1px solid #999;">790.03</td>
<td style="padding: 10px; border: 1px solid #999;">390 ms</td>
</tr>
</tbody>
</table>
<h3 style="padding-top: 15px;">Takeaway</h3>
<p style="text-align: justify;">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.</p>

<h2 style="padding-top: 15px;">Automating the Rolling Index Creation</h2>
<p style="text-align: justify;">ScaleGrid automates the Rolling Index Creation for your <a style="color: #2da964;" title="MySQL Hosting" href="https://scalegrid.io/mysql.html" target="_blank">MySQL deployment</a> with a simple user interface to initiate it.</p>
<a href="https://scalegrid.io/blog/best-practice-for-creating-indexes-on-your-mysql-tables/"><img class="alignnone size-full wp-image-4829" src="https://scalegrid.io/blog/wp-content/uploads/2019/11/alterblog3.png" alt="alterblog3" width="100%" height="auto" /></a>
<p style="text-align: justify;">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.</p>
<p style="text-align: justify; padding-bottom: 20px;">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!</p>