- You are here:
- Home »
- MySQL
- » MySQL database optimization with indices
MySQL database optimization with indices
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_unlimitedSo 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: Set WP_MEMORY_LIMIT value correctly in wp-config.php
Powered by Inline Related Posts
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!
Powered by Inline Related Posts
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.
Related Posts
System.Collections.Generic.KeyNotFoundException “The given key was not present in the dictionary” with MySQL Connector/NET and utf8mb4 character set
Restore single MySQL table from a full mysqldump backup file
MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!
How to string replace on all WordPress posts in MySQL
