MySQL database optimization with indices

21 June 2014
/ MySQL
/ By Jan Reilink

Why MySQL indices are so important? At Vevida, we like to help our customers as much as possible. Even with optimizing a MySQL database when they don’t ask for it, or when a customer doesn’t know performance can be improved. For example by adding an index because we spotted a slow query in our slow-query log.

MySQL fix. Image via Flickr by Luis M. Gallardo D

Fix MySQL indices and slow queries

Indices are used by MySQL to help find rows quickly. We want to make it as easy as possible for MySQL to find the relevant rows, the more precise or specific we are the less the number of rows MySQL has to fetch.

The other day I spotted the following in MySQL slow-query log, in which MySQL needed roughly 3 seconds to examine 8035 and send 872 rows. That looks like a good optimization candidate…

# Time: 140605 16:41:34
# User@Host: database[database] @ server-01.example.com [1.1.11.111]
# Thread_id: 4660034  Schema: database  QC_hit: No
# Query_time: 3.010892  Lock_time: 0.000062  Rows_sent: 872  Rows_examined: 8035
use database;
SET timestamp=1401979294;
SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimited

So I started with examining the query:

Examining a query with MySQL EXPLAIN SELECT

The MySQL EXPLAIN statement is used to obtain a query execution plan. That is, an explanation of how MySQL would execute a query. This means I can use EXPLAIN SELECT to examine the query.

MariaDB [database]> explain SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimited = 1) ORDER BY sDate_insert DESC;
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
| id   | select_type | table       | type | possible_keys                          | key            | key_len | ref   | rows | Extra                       |
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
|    1 | SIMPLE      | sContent | ref     | sPublish,sVisible_unlimited            | sPublish       | 2       | const | 7091 | Using where; Using filesort |
+------+-------------+-------------+------+----------------------------------------+----------------+---------+-------+------+-----------------------------+
1 row in set (0.03 sec)

Here I see indices exist: sPublish, sVisible_unlimited, and only sPublish is used.

This may interest you: Check, repair and optimize MySQL tables with mysqlcheck

In the WHERE clause there is sYear = '2014', but sYear isn’t indexed. I use the statement DESCRIBE (or DESC) to describe the table definitions. This is an synonym of EXPLAIN, but mostly used for describing table definitions.

MariaDB [database]> desc sContent;
+--------------------------+--------------+------+-----+---------------------+-----------------------------+
| Field                    | Type         | Null | Key | Default             | Extra                       |
+--------------------------+--------------+------+-----+---------------------+-----------------------------+
| cnt_id                   | int(11)      | NO   | PRI | NULL                | auto_increment              |
[...]
| sYear                    | varchar(4)   | YES  |     | NULL                |                             |
+--------------------------+--------------+------+-----+---------------------+-----------------------------+

I added an index on sYear:

ALTER TABLE `sContent` ADD INDEX (`sYear`);

and this gives a better result examining the query again with EXPLAIN:

MariaDB [database]> explain SELECT * FROM sContent WHERE sYear = '2014' AND sPublish = 1 and ('2014-06-05 16:41:30' Between sVisible_from And sVisible_untill or sVisible_unlimited = 1) ORDER BY sDate_insert DESC;
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
| id   | select_type | table       | type | possible_keys                                   | key      | key_len | ref   | rows | Extra                                              |
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
|    1 | SIMPLE      | sContent | ref     | sPublish,sVisible_unlimited,sYear               | sYear    | 7       | const | 1503 | Using index condition; Using where; Using filesort |
+------+-------------+-------------+------+-------------------------------------------------+----------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

By adding an index on this one column sYear, MySQL doesn’t have to search through 7091 rows, but only 1503. This saves considerable, which will improve database and website performance.

MySQL index optimization

Update: 2-4-2015, Why is a MySQL index so important? Today, I noticed the following query in the MySQL slow-log of a server (EXPLAIN added for investigation):

MariaDB [db_name]> EXPLAIN  SELECT * FROM tblproducts WHERE CampaignId=379 AND CampaignProductId='AB1005257';
+------+-------------+------------------+------+---------------+---------+---------+-------+--------+-------------+
| id   | select_type | table            | type | possible_keys | key     | key_len | ref   | rows   | Extra       |
+------+-------------+------------------+------+---------------+---------+---------+-------+--------+-------------+
|    1 | SIMPLE      | tblproducts | ref  | Index_5       | Index_5 | 5       | const | 146104 | Using where |
+------+-------------+------------------+------+---------------+---------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

As you can see, there is an index present on the tblproducts table, called Index_5. But still MySQL has to plow through 146104 rows. Given the CampaignId field contains what appears to be an identifier, that makes a great option for an extra index!

This may interest you: MySQL InnoDB performance improvement: InnoDB buffer pool instances - Updated!

I added the index: ALTER TABLE `tblproducts` ADD INDEX (`CampaignId`);

The added index optimized the query to only one result:

MariaDB [db_name]> EXPLAIN  SELECT * FROM tblproducts WHERE CampaignId=379 AND CampaignProductId='AB1005257';
+------+-------------+------------------+------+--------------------+------------+---------+-------------+------+-----------------------+
| id   | select_type | table            | type | possible_keys      | key        | key_len | ref         | rows | Extra                 |
+------+-------------+------------------+------+--------------------+------------+---------+-------------+------+-----------------------+
|    1 | SIMPLE      | tblproducts | ref  | Index_5,CampaignId | CampaignId | 308     | const,const |    1 | Using index condition |
+------+-------------+------------------+------+--------------------+------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

This is why MySQL index optimization is so important, even on the WordPress wp_options table.

About the Author Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.

follow me on:

Thank you!

21 Aug, 2017

Restore single MySQL table from a full mysqldump backup file

10 Jun, 2016

MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!

13 Sep, 2014

How to string replace on all WordPress posts in MySQL

19 Jun, 2014

Convert MySQL MyISAM tables to InnoDB

15 Jun, 2014

How to flush MySQL query cache from time to time