You are here

SQL Query Tuning - Performance

How could the following SQL queries be improved performance wise and otherwise and can you also explain why your change is more optimal?

Please consider, when testing, that your results are not confused by the Query Cache or by reading data from your I/O system which are an order of magnitude faster (Query Cache) or slower (I/O) than the in-memory behaviour.

Table of Contents


SQL Query Tuning - Covering function

Difficulty: Very easy.

Query:

SELECT *
  FROM bills
 WHERE UNIX_TIMESTAMP(due_date) < 1601560170
;

Table and data:

CREATE TABLE bills (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, amount DECIMAL (10,2) NOT NULL
, due_date DATETIME NOT NULL
, customer_id SMALLINT UNSIGNED NOT NULL
, KEY `due_date` (`due_date`)
);

INSERT INTO bills
SELECT NULL, ROUND(RAND()*10000, 2), FROM_UNIXTIME(UNIX_TIMESTAMP()+RAND()*86400*30), ROUND(RAND()*10000, 0);
INSERT INTO bills
SELECT NULL, ROUND(RAND()*10000, 2), FROM_UNIXTIME(UNIX_TIMESTAMP()+RAND()*86400*30), ROUND(RAND()*10000, 0)
  FROM bills;
-- Repeat this command about 14 times

Query Execution Plan (EXPLAIN) before improvement:

+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | bills | ALL  | NULL          | NULL | NULL    | NULL | 16300 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+

Query runtime before improvement: 11 - 12 ms.

Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|    1 | SIMPLE      | bills | range | due_date      | due_date | 5       | NULL | 1    | Using index condition |
+------+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+

Query runtime after improvement: About 1 ms.

Difficulty: Very easy to pretty costly.

Query:

SELECT *
  FROM customers
 WHERE email LIKE '%hans.meier%'
;

Table and data:

CREATE TABLE customers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, first_name VARCHAR(63)
, last_name VARCHAR(127)
, email VARCHAR(255)
, zip_code VARCHAR(9)
, city VARCHAR(63)
, KEY `email` (`email`)
, INDEX (city, last_name, first_name)
);

INSERT INTO customers SELECT NULL, MD5(RAND()), MD5(RAND()), CONCAT(CRC32(RAND()), '@', MD5(RAND())), ROUND(RAND()*9999, 0), CRC32(RAND())
;
INSERT INTO customers SELECT NULL, MD5(RAND()), MD5(RAND()), CONCAT(CRC32(RAND()), '@', MD5(RAND())), ROUND(RAND()*9999, 0), CRC32(RAND())
  FROM customers;
-- Repeat this command about 14 times

Query Execution Plan (EXPLAIN) before improvement:

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | customers | ALL  | NULL          | NULL | NULL    | NULL | 324988 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

Query runtime before improvement: About 150 ms.

Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-----------+-------+---------------+-------+---------+------+------+-----------------------+
| id   | select_type | table     | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |
+------+-------------+-----------+-------+---------------+-------+---------+------+------+-----------------------+
|    1 | SIMPLE      | customers | range | email         | email | 258     | NULL | 1    | Using index condition |
+------+-------------+-----------+-------+---------------+-------+---------+------+------+-----------------------+

Query runtime after improvement: About 1 ms.

SQL Query Tuning - Covering function twice

Difficulty: Very easy to pretty costly.

Query:

SELECT *
  FROM customers
 WHERE LOWER(email) = LOWER('hans.meier@fromdual.com')
;

Table and data: See above.

Query Execution Plan (EXPLAIN) before improvement:

+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | customers | ALL  | NULL          | NULL | NULL    | NULL | 519800 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

Query runtime before improvement: About 140 ms.

Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-----------+------+---------------+-------+---------+-------+------+-----------------------+
| id   | select_type | table     | type | possible_keys | key   | key_len | ref   | rows | Extra                 |
+------+-------------+-----------+------+---------------+-------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | customers | ref  | email         | email | 258     | const | 1    | Using index condition |
+------+-------------+-----------+------+---------------+-------+---------+-------+------+-----------------------+

Query runtime after improvement: About 1 ms.

SQL Query Tuning - Compare 2 indexed columns

Difficulty: Medium.

Query:

SELECT COUNT(*)
  FROM cdrs
 WHERE start >= end
;

Table and data:

CREATE TABLE `cdrs` (
  `uniqueid` varchar(40) NOT NULL,
  `callid` varchar(40) NOT NULL,
  `asteriskid` varchar(20) NOT NULL DEFAULT '',
  `machine` int(11) NOT NULL DEFAULT 0,
  `status` varchar(15) NOT NULL DEFAULT '',
  `start` TIMESTAMP(6) NOT NULL,
  `end` TIMESTAMP(6) NOT NULL,
  `scustomer` int(11) NOT NULL DEFAULT 0,
  `stype` varchar(30) NOT NULL DEFAULT '',
  `snumber` varchar(255) NOT NULL DEFAULT '',
  `dcustomer` int(11) NOT NULL DEFAULT 0,
  `dtype` varchar(30) NOT NULL DEFAULT '',
  `dnumber` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`uniqueid`),
  KEY `server` (`callid`),
  KEY `start` (`start`),
  KEY `scustomer` (`scustomer`,`start`),
  KEY `dcustomer` (`dcustomer`,`start`),
  KEY `asteriskid` (`asteriskid`)
);

INSERT INTO cdrs SELECT UUID(), MD5(RAND()), '', 0, '', FROM_UNIXTIME(ROUND(RAND()*UNIX_TIMESTAMP(), 6)), 0, 0, '', '', 0, '', '';
INSERT INTO cdrs SELECT UUID(), MD5(RAND()), '', 0, '', FROM_UNIXTIME(ROUND(RAND()*UNIX_TIMESTAMP(), 6)), 0, 0, '', '', 0, '', '' FROM cdrs;
-- Repeat this command about 20 times...
UPDATE cdrs SET end = FROM_UNIXTIME(UNIX_TIMESTAMP(start)+RAND()*3600);

Query Execution Plan (EXPLAIN) before improvement:

+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | cdrs  | ALL  | NULL          | NULL | NULL    | NULL | 520304 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+

Query runtime before improvement: About 110 ms.

Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | cdrs  | index | NULL          | start_2 | 16      | NULL | 260250 | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+

Query runtime after improvement: About 105 ms. Not so much improvement achieved. But should be come much better when you have memory pressure.

SQL Query Tuning - Function and 2 indexed columns

Difficulty: medium

Query:

SELECT uniqueid
  FROM cdrs
 WHERE CURRENT_TIMESTAMP() BETWEEN start AND end
;

Table and data: See above.

Query Execution Plan (EXPLAIN) before improvement:

+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | cdrs  | ALL  | start         | NULL | NULL    | NULL | 520304 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+

Query runtime before improvement: About 120 ms.

Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | cdrs  | range | start,start_2 | start_2 | 7       | NULL | 260152 | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+

Query runtime after improvement: About 140 ms. Worse than original. Depends heavily on data distribution!

SQL Query Tuning - Time series problem

Difficulty: High.

Query:

SELECT *
  FROM history_of_int_values
 WHERE item_id = 42
   AND timestamp BETWEEN '2020-10-01 00:00:00' AND '2010-10-31 23:59:59'
;

Table and data:

CREATE TABLE `history_of_int_values` (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `item_id` bigint(20) unsigned NOT NULL,
  `timestamp` timestamp(6) NOT NULL,
  `value` bigint(20) signed NOT NULL DEFAULT '0',
  UNIQUE KEY (`item_id`,`timestamp`),
  KEY (timestamp)
) ENGINE = InnoDB;

INSERT INTO history_of_int_values
SELECT NULL, ROUND(RAND()*100, 0), FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(6))-(86400*365*rand())), ROUND(RAND()*1000000, 0);
INSERT IGNORE INTO history_of_int_values
SELECT NULL, ROUND(RAND()*100, 0), FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(6))-(86400*365*rand())), ROUND(RAND()*1000000, 0) FROM history_of_int_values;

Query Execution Plan (EXPLAIN) before improvement:

+------+-------------+-----------------------+-------+-------------------+---------+---------+------+------+-----------------------+
| id   | select_type | table                 | type  | possible_keys     | key     | key_len | ref  | rows | Extra                 |
+------+-------------+-----------------------+-------+-------------------+---------+---------+------+------+-----------------------+
|    1 | SIMPLE      | history_of_int_values | range | item_id,timestamp | item_id | 15      | NULL | 859  | Using index condition |
+------+-------------+-----------------------+-------+-------------------+---------+---------+------+------+-----------------------+

Query runtime before improvement: 11 ms.

Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-----------------------+-------+-------------------+---------+---------+------+------+-------------+
| id   | select_type | table                 | type  | possible_keys     | key     | key_len | ref  | rows | Extra       |
+------+-------------+-----------------------+-------+-------------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | history_of_int_values | range | PRIMARY,timestamp | PRIMARY | 15      | NULL | 859  | Using where |
+------+-------------+-----------------------+-------+-------------------+---------+---------+------+------+-------------+

Query runtime after improvement: About 1 ms. Has dramatic impact if you run out of RAM!

SQL Query Tuning - Wrong built index or query

Difficulty: Medium.

Query:

SELECT *
  FROM customers
 WHERE last_name = 'Maier'
   AND first_name = 'Hans'
;

Table and data: See above.

Query Execution Plan (EXPLAIN) before improvement:

+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | customers | ALL  | NULL          | NULL | NULL    | NULL | 65044 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+

Query runtime before improvement: About 20 ms.

Here we have 2 different possible solutions which come to my mind:

a) Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-----------+------+---------------+-----------+---------+-------------+------+-----------------------+
| id   | select_type | table     | type | possible_keys | key       | key_len | ref         | rows | Extra                 |
+------+-------------+-----------+------+---------------+-----------+---------+-------------+------+-----------------------+
|    1 | SIMPLE      | customers | ref  | last_name     | last_name | 196     | const,const | 1    | Using index condition |
+------+-------------+-----------+------+---------------+-----------+---------+-------------+------+-----------------------+

a) Query runtime after improvement: About 1 ms.

b) Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-----------+------+---------------+------+---------+-------------------+------+-----------------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref               | rows | Extra                 |
+------+-------------+-----------+------+---------------+------+---------+-------------------+------+-----------------------+
|    1 | SIMPLE      | customers | ref  | city          | city | 262     | const,const,const | 1    | Using index condition |
+------+-------------+-----------+------+---------------+------+---------+-------------------+------+-----------------------+

b) Query runtime after improvement: About 1 ms.

SQL Query Tuning - OR optimization

Difficulty: Medium.

Query:

SELECT *
  FROM customers
 WHERE last_name = 'Meier' OR first_name = 'Hans'
;

Table and data: See above.

Query Execution Plan (EXPLAIN) before improvement:

+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | customers | ALL  | NULL          | NULL | NULL    | NULL | 65044 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+

Query runtime before improvement: About 20 ms.

Here we have 2 different possible solutions which come to my mind:

a) Query Execution Plan (EXPLAIN) after improvement:

+------+-------------+-----------+-------------+----------------------+----------------------+---------+------+------+------------------------------------------------+
| id   | select_type | table     | type        | possible_keys        | key                  | key_len | ref  | rows | Extra                                          |
+------+-------------+-----------+-------------+----------------------+----------------------+---------+------+------+------------------------------------------------+
|    1 | SIMPLE      | customers | index_merge | last_name,first_name | last_name,first_name | 130,66  | NULL | 2    | Using union(last_name,first_name); Using where |
+------+-------------+-----------+-------------+----------------------+----------------------+---------+------+------+------------------------------------------------+

a) Query runtime after improvement: About 2 ms.

b) Query Execution Plan (EXPLAIN) after improvement:

+------+--------------+------------+------+---------------+------------+---------+-------+------+-----------------------+
| id   | select_type  | table      | type | possible_keys | key        | key_len | ref   | rows | Extra                 |
+------+--------------+------------+------+---------------+------------+---------+-------+------+-----------------------+
|    1 | PRIMARY      | customers  | ref  | last_name     | last_name  | 130     | const | 1    | Using index condition |
|    2 | UNION        | customers  | ref  | first_name    | first_name | 66      | const | 1    | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL       | NULL    | NULL  | NULL |                       |
+------+--------------+------------+------+---------------+------------+---------+-------+------+-----------------------+

b) Query runtime after improvement: About 1 ms.