Master MySQL Performance: Key Query Optimizations, Index Tips & Config Tweaks

General Statement Optimization

Choosing appropriate data types and character sets reduces storage and speeds queries. Example: use TINYINT(1) for boolean flags and latin1 for English‑only text. CREATE TABLE users (is_active TINYINT(1)); Avoid SELECT * and only retrieve needed columns. SELECT id, name, email FROM users; Prefer proper JOINs with indexed columns over subqueries, and use EXISTS or JOIN instead of subqueries.

SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';

Replace OR conditions with UNION (or UNION ALL) when possible, and ensure ORDER BY / GROUP BY columns are indexed.

CREATE INDEX idx_order_date ON orders (order_date);
SELECT * FROM orders ORDER BY order_date;
SELECT id, name FROM users WHERE status = 'active'
UNION
SELECT id, name FROM users WHERE status = 'pending';

Avoid leading‑wildcard LIKE patterns; use full‑text search or place the wildcard at the end.

SELECT * FROM products WHERE description LIKE 'keyword%';

Batch inserts reduce overhead; temporarily disable unique checks and foreign‑key checks for large loads.

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]'), ('Bob', '[email protected]');
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
-- batch insert
SET unique_checks=1;
SET foreign_key_checks=1;
COMMIT;

Enable query cache (MySQL <8.0) to avoid repeated work.

SET GLOBAL query_cache_size = 1048576;
SET GLOBAL query_cache_type = ON;

Prefer WHERE to HAVING for filtering before aggregation.

SELECT user_id, COUNT(*) FROM orders WHERE order_date > '2020-01-01' GROUP BY user_id HAVING COUNT(*) > 1;
-- better
SELECT user_id, COUNT(*) AS order_count FROM orders WHERE order_date > '2020-01-01' GROUP BY user_id WHERE order_count > 1;

Configuration Parameter Tuning

Adjust InnoDB buffer pool size (≈60‑80% of RAM on a dedicated DB server) to increase cache hit rate. SET GLOBAL innodb_buffer_pool_size = 2G; Query cache is removed in MySQL 8.0; ignore it on newer versions.

Increase thread cache size to reduce thread creation overhead. SET GLOBAL thread_cache_size = 100; Enlarge table_open_cache, tmp_table_size, max_heap_table_size to reduce table‑open and temporary‑table overhead.

SET GLOBAL table_open_cache = 4000;
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

Adjust innodb_flush_log_at_trx_commit according to durability needs. SET GLOBAL innodb_flush_log_at_trx_commit = 2; Increase innodb_log_file_size and innodb_log_buffer_size for better write performance.

SET GLOBAL innodb_log_file_size = 256M;
SET GLOBAL innodb_log_buffer_size = 16M;

Set innodb_io_capacity based on disk I/O capability. SET GLOBAL innodb_io_capacity = 2000; Raise max_connections to support more concurrent clients. SET GLOBAL max_connections = 500; Enlarge sort_buffer_size and read_buffer_size to speed sorting and sequential scans.

SET GLOBAL sort_buffer_size = 4M;
SET GLOBAL read_buffer_size = 2M;

Correct Index Usage

Create indexes on columns used in WHERE clauses and join conditions, following the left‑most prefix rule for composite indexes.

CREATE INDEX idx_abc ON table_name (a, b, c);
SELECT * FROM table_name WHERE a = 1 AND b = 2;

Avoid calculations on indexed columns; rewrite date functions as range predicates.

SELECT * FROM orders WHERE date BETWEEN '2024-06-01' AND '2024-06-30';

Remove duplicate indexes and avoid indexing columns that change frequently.

CREATE INDEX idx_update_col ON table_name (update_col);
-- use with caution if update_col is updated often

Limit the number of columns in a composite index; consider splitting into smaller indexes.

CREATE INDEX idx_columns ON table_name (col1, col2, col3, col4, col5);

Use covering indexes when all selected columns are contained in the index.

CREATE INDEX idx_covering ON orders (order_id, order_date, customer_id);
SELECT order_id, order_date, customer_id FROM orders WHERE customer_id = 123;

Avoid IS NULL/IS NOT NULL on indexed columns; use default values when possible. SELECT * FROM users WHERE email = ''; Prefer positive conditions over NOT IN, !=, <> to allow index usage.

SELECT * FROM orders WHERE status IN ('pending','processing');

For pagination on large tables, avoid large OFFSET; use key‑set pagination.

SELECT * FROM orders WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 999999,1) LIMIT 10;

Choose appropriate lock types (row‑level vs table‑level) based on workload.

SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
LOCK TABLES orders WRITE;

Other Pitfalls

Avoid SELECT DISTINCT unless necessary, and use LIMIT 1 when only one row is needed.

SELECT DISTINCT name FROM users WHERE status = 'active';
SELECT * FROM users WHERE email = '[email protected]' LIMIT 1;

Prefer UNION ALL over UNION to skip the costly duplicate‑removal step.

SELECT name FROM employees WHERE department = 'Sales'
UNION ALL
SELECT name FROM contractors WHERE department = 'Sales';

Using EXPLAIN to Diagnose Queries

Prefix a query with EXPLAIN to view the execution plan, which shows id, select_type, table, type, possible_keys, key, rows, and Extra columns.

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

Interpretation tips: type = ALL or index indicates full scans; create or adjust indexes accordingly.

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

Avoid functions on indexed columns, use range predicates instead.

-- avoid
SELECT * FROM orders WHERE YEAR(order_date) = 2023;
-- better
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Covering indexes can eliminate the need to read the table rows.

CREATE INDEX idx_covering ON orders (customer_id, order_date, order_id);
SELECT customer_id, order_date, order_id FROM orders WHERE customer_id = 123;

Complex queries can be split into simpler steps for better performance.

-- complex join
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.name = 'John Doe';
-- split
SELECT id FROM customers WHERE name = 'John Doe';
SELECT * FROM orders WHERE customer_id = 123;

Summary

By following the checklist—writing efficient statements, tuning MySQL parameters, using proper indexes, avoiding common pitfalls, and leveraging EXPLAIN to verify improvements—developers can dramatically improve query performance and overall database reliability.