Query Optimization in MariaDB

·

muragz

This is a follow-up post to Deep Dive into Advanced SQL Techniques with Examples. While this article focus on MariaDB, the concepts outlined here may be applied to other RDBMS.

Query optimization is the elusive process of improving the efficiency and performance of SQL queries to reduce execution time, minimizes resource usage, and enhances overall database performance.

You cannot improve what you cannot measure, this article assumes query logging is enabled, see Slow Query Log. This enables us to identify queries that are taking longer to execute and those that are processing a larger number of data rows.

1. Query Execution Plan

Provides crucial information for identifying bottlenecks in a query by showing how a query will be executed, which indexes will be used, the order of table access, and the estimated number of rows that will be processed.

EXPLAIN WITH RECURSIVE region_metrics AS ( 
    SELECT c.country_id AS c_id
    FROM countries c
    UNION
    SELECT region_id
    FROM countries
    JOIN region_metrics ON countries.region_id = region_metrics.c_id
) 
SELECT * FROM region_metrics;

+------+-----------------+------------+-------+---------------+-----------+---------+----------------------------+------+-------------+
| id   | select_type     | table      | type  | possible_keys | key       | key_len | ref                        | rows | Extra       |
+------+-----------------+------------+-------+---------------+-----------+---------+----------------------------+------+-------------+
|    1 | PRIMARY         | <derived2> | ALL   | NULL          | NULL      | NULL    | NULL                       | 239  |             |
|    2 | DERIVED         | c          | index | NULL          | region_id | 4       | NULL                       | 239  | Using index |
|    3 | RECURSIVE UNION | countries  | index | region_id     | region_id | 4       | NULL                       | 239  | Using index |
|    3 | RECURSIVE UNION | <derived2> | ref   | key0          | key0      | 5       | nation.countries.region_id | 1    |             |
| NULL | UNION RESULT    | <union2,3> | ALL   | NULL          | NULL      | NULL    | NULL                       | NULL |             |
+------+-----------------+------------+-------+---------------+-----------+---------+----------------------------+------+-------------+
5 rows in set (0.011 sec)

Sample interpretation of the second row from the output:

  • Table: c: Refers to the countries table.
  • Type: index: The query scans the entire index, which is more efficient than scanning the table.
  • Key: region_id: This index is being used.
  • Rows: 239: The query scans 239 rows.
  • Extra: Using index: The index covers all the columns needed by the query, so the table itself isn’t accessed.

2. Indexes

Helps to quickly locate rows without scanning the entire table e.g. primary keys, unique indexes, full-text indexes, and spatial indexes. It is not just enough to choose the right type of index but also to index the correct (set of?) columns.

This example reduces query time by approximately 91.67% from 0.012 seconds to 0.001 seconds by simply adding an index.

-- BEFORE INDEXing
SELECT * FROM countries WHERE name = 'Bangladesh';
+------------+------------+-----------+--------------+---------------+---------------+-----------+
| country_id | name       | area      | national_day | country_code2 | country_code3 | region_id |
+------------+------------+-----------+--------------+---------------+---------------+-----------+
|         22 | Bangladesh | 143998.00 | 1971-03-26   | BD            | BGD           |         2 |
+------------+------------+-----------+--------------+---------------+---------------+-----------+
1 row in set (0.012 sec)

-- INDEXing
CREATE INDEX idx_country_name ON countries(name);

-- AFTER INDEXing - the query will use the index on 'name'
SELECT * FROM countries WHERE name = 'Bangladesh';
+------------+------------+-----------+--------------+---------------+---------------+-----------+
| country_id | name       | area      | national_day | country_code2 | country_code3 | region_id |
+------------+------------+-----------+--------------+---------------+---------------+-----------+
|         22 | Bangladesh | 143998.00 | 1971-03-26   | BD            | BGD           |         2 |
+------------+------------+-----------+--------------+---------------+---------------+-----------+
1 row in set (0.001 sec)

3. Join Optimization

When joining multiple tables, the DB can optimize the join order and method e.g., nested loops, hash joins). Joins should use indexed columns and unnecessary columns should be excluded from the result set to reduce the processing time.

In this example we reduce query time from 0.020 seconds to 0.012 ~40.0% by indexing the join columns.

-- BEFORE
SELECT count(origin) FROM country_distances cd
JOIN countries c ON c.country_id = cd.origin
+---------------+
| count(origin) |
+---------------+
|         57121 |
+---------------+
1 row in set (0.020 sec)

CREATE INDEX idx_origin ON country_distances(origin);
Query OK, 0 rows affected (0.099 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE INDEX idx_destination ON country_distances(destination);
Query OK, 0 rows affected (0.087 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- AFTER
SELECT count(origin) FROM country_distances cd
JOIN countries c ON c.country_id = cd.origin
+---------------+
| count(origin) |
+---------------+
|         57121 |
+---------------+
1 row in set (0.012 sec)

4. Query Rewrite

Sometimes rewriting a query may lead to better performance e.g. replacing a subquery with a JOIN or using EXISTS instead of IN. There is also automatic query rewrite through tool like Optimizer Switch.

SELECT count(origin) FROM country_distances cd
WHERE cd.destination IN (
    SELECT country_id FROM countries c WHERE region_id = 5
)
+---------------+
| count(origin) |
+---------------+
|          4302 |
+---------------+
1 row in set (0.002 sec)

SELECT count(origin) FROM country_distances cd
JOIN countries c ON c.country_id = cd.origin
WHERE c.region_id = 5
+---------------+
| count(origin) |
+---------------+
|          4302 |
+---------------+
1 row in set (0.001 sec)

5. Partitioning

This approach splits data in a large database into smaller chunks called partitions. It can improve query performance by reducing the amount of data that needs to be scanned. Partitioning methods include range, list, hash, and key partitioning. See https://mariadb.com/kb/en/partitioning-types/

In this example, query time reduced by 97% after partitioning since the DB scans only the relevant partition.

-- Partitioning a large table by range
CREATE TABLE country_stats_partitioned (
    country_id INT,
    year INT,
    gdp BIGINT,
    population BIGINT
) PARTITION BY RANGE (year) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- Copy data
INSERT INTO country_stats_partitioned SELECT * FROM country_stats

-- Verify partitions
SELECT PARTITION_NAME as part_name,PARTITION_ORDINAL_POSITION as part_pos,PARTITION_METHOD as part_meth,PARTITION_EXPRESSION as part_expr, TABLE_ROWS as table_rows, AVG_ROW_LENGTH as row_len, DATA_LENGTH as data_len
FROM information_schema.partitions 
WHERE TABLE_SCHEMA='nation' 
AND TABLE_NAME = 'country_stats_partitioned' 
AND PARTITION_NAME IS NOT NULL
+------+------+-----------+-----------+------------+---------+----------+
| pn   | pop  | part_meth | part_expr | table_rows | row_len | data_len |
+------+------+-----------+-----------+------------+---------+----------+
| p0   |    1 | RANGE     | `year`    |       5769 |      59 |   344064 |
| p1   |    2 | RANGE     | `year`    |       1969 |      66 |   131072 |
| p2   |    3 | RANGE     | `year`    |       1756 |      74 |   131072 |
| p3   |    4 | RANGE     | `year`    |          0 |       0 |    16384 |
+------+------+-----------+-----------+------------+---------+----------+
4 rows in set (0.020 sec)

SELECT * FROM country_stats WHERE year = 2017;
194 rows in set (0.035 sec)

SELECT * FROM country_stats_partitioned WHERE year = 2017;
194 rows in set (0.001 sec)

6. Caching

The query cache stores results of SELECT queries so that if the identical query is received in future, the results can be quickly returned. This is extremely useful in high-read, low-write environments (such as most websites). It does not scale well in environments with high throughput on multi-core machines, so it is disabled by default.

-- Enable query cache (only applicable in versions where query cache is supported)
SET GLOBAL query_cache_size = 1024 * 1024;  -- Set cache size to 1MB

-- First query execution (result will be cached)
SELECT COUNT(*) FROM countries WHERE region_id = 2;
+----------+
| COUNT(*) |
+----------+
|       14 |
+----------+
1 row in set (0.007 sec)

-- Subsequent execution, retrieved from cache, faster by 85%
SELECT COUNT(*) FROM countries WHERE region_id = 2;
+----------+
| COUNT(*) |
+----------+
|       14 |
+----------+
1 row in set (0.001 sec)

7. Optimizer Hints

Optimizer hints guide the optimizer’s choices for execution plans. For example, you can force the optimizer to use a specific index or join method. This is useful when you have specific knowledge about your data that the optimizer might not be aware of.

-- Force the use of a specific index using an optimizer hint
SELECT /*+ INDEX(countries idx_country_name) */ * FROM countries WHERE name = 'Bangladesh';

8. Statistics

The DB collects and uses statistics about the distribution of data in tables and indexes. You can update statistics with ANALYZE TABLE to maintain query performance as data changes.

-- Analyze table to update statistics
ANALYZE TABLE country_stats_partitioned;

-- Query performance might improve after statistics are updated
SELECT count(gdp) FROM country_stats_partitioned WHERE year=1982;

9. Limits and Offsets

Do you really need all the data in the table all at once? Using LIMIT and OFFSET in queries can reduce the number of rows that need to be processed. See also, indexed pagination.

SELECT * FROM country_stats_partitioned;
9514 rows in set (0.007 sec)

SELECT * FROM country_stats_partitioned LIMIT 5 OFFSET 10;
5 rows in set (0.001 sec)

10. Query Profiling and Diagnostics

Tools like SHOW PROFILE and the Performance Schema can provide detailed information about how resources are used during query execution. This can help investigate slow queries.

-- Enable profiling
SET profiling = 1;
Query OK, 0 rows affected (0.000 sec)

-- Run the query
SELECT VARIANCE(gdp) FROM country_stats_partitioned WHERE year = 1982;
+------------------------+
| VARIANCE(gdp)          |
+------------------------+
| 10731379344963378.0000 |
+------------------------+
1 row in set (0.010 sec)

-- Show profiling results
SHOW PROFILES;
+----------+------------+-----------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                 |
+----------+------------+-----------------------------------------------------------------------+
|        1 | 0.00950140 | SELECT VARIANCE(gdp) FROM country_stats_partitioned WHERE year = 1982 |
+----------+------------+-----------------------------------------------------------------------+
1 row in set (0.000 sec)

-- Detailed breakdown of time spent in each phase of the query execution
SHOW PROFILE FOR QUERY 1;
+------------------------+----------+
| Status                 | Duration |
+------------------------+----------+
| Starting               | 0.000046 |
| checking permissions   | 0.000006 |
| Opening tables         | 0.000019 |
| After opening tables   | 0.000047 |
| System lock            | 0.000009 |
| table lock             | 0.000007 |
| init                   | 0.007261 |
| Optimizing             | 0.000030 |
| Statistics             | 0.000021 |
| Preparing              | 0.000018 |
| Executing              | 0.000005 |
| Sending data           | 0.001850 |
| End of update loop     | 0.000023 |
| Query end              | 0.000007 |
| Commit                 | 0.000021 |
| Query end              | 0.000004 |
| closing tables         | 0.000004 |
| Unlocking tables       | 0.000004 |
| closing tables         | 0.000006 |
| Query end              | 0.000007 |
| Starting cleanup       | 0.000004 |
| Freeing items          | 0.000007 |
| Updating status        | 0.000089 |
| Reset for next command | 0.000008 |
+------------------------+----------+
24 rows in set (0.001 sec)

Conclusion

Optimizing queries is an ongoing process that depends on the specific application requirements. Regularly tune your database and queries as data grows and changes.