How to optimize query performance in MariaDB and what are the common optimization techniques?

Query optimization in MariaDB is crucial for improving database performance. Here are the main optimization strategies:

1. Use EXPLAIN to Analyze Queries

sql
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 25;

Key Metrics:

• type: Access type (ALL < index < range < ref < eq_ref < const < system)
• key: Index used
• rows: Estimated number of rows scanned
• Extra: Additional information (Using filesort, Using temporary need optimization)

2. Index Optimization

sql
-- Create appropriate indexes
CREATE INDEX idx_name_age ON users(name, age);

-- Use covering indexes
SELECT id, name, age FROM users WHERE name = 'John';

-- Avoid index invalidation
-- Not recommended: WHERE name LIKE '%John%'
-- Recommended: WHERE name LIKE 'John%'

3. Query Rewriting

sql
-- Avoid SELECT *
-- Not recommended: SELECT * FROM users WHERE id = 1;
-- Recommended: SELECT id, name, email FROM users WHERE id = 1;

-- Use LIMIT to limit results
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- Avoid subqueries, use JOIN
-- Not recommended: SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- Recommended: SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;

-- Use UNION ALL instead of UNION (if deduplication is not needed)
-- Not recommended: SELECT name FROM users UNION SELECT name FROM admins;
-- Recommended: SELECT name FROM users UNION ALL SELECT name FROM admins;

4. Pagination Optimization

sql
-- Traditional pagination (poor performance for deep pagination)
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- Optimization 1: Use cursor-based pagination
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- Optimization 2: Delayed association
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp
ON o.id = tmp.id;

5. JOIN Optimization

sql
-- Ensure the driven table has an index
CREATE INDEX idx_user_id ON orders(user_id);

-- Small table drives large table
SELECT * FROM small_table s JOIN large_table l ON s.id = l.small_id;

-- Use STRAIGHT_JOIN to force join order
SELECT * FROM large_table l STRAIGHT_JOIN small_table s ON s.id = l.small_id;

6. Configuration Optimization

ini
# my.cnf configuration
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M

7. Monitor Slow Queries

sql
-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

-- View slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Through these optimization strategies, you can significantly improve MariaDB's query performance.