Comprehensive MySQL Query and Configuration Optimization Guide

This article presents a detailed guide for optimizing MySQL performance, targeting both query writing and server configuration.

General statement optimization emphasizes using appropriate data types and character sets to reduce storage and improve speed. For example, using CREATE TABLE users (is_active TINYINT(1)); for boolean flags and CREATE TABLE messages (content VARCHAR(255) CHARACTER SET latin1); for English‑only text.

It advises avoiding SELECT * and instead selecting only required columns, e.g., SELECT id, name, email FROM users; . Proper use of JOIN and replacing subqueries with joins or EXISTS is recommended, such as SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id; .

Replacing OR conditions with UNION (or UNION ALL when duplicates are acceptable) can improve performance, illustrated by combining two status queries with UNION .

Leading‑wildcard LIKE patterns prevent index usage; the guide suggests using prefix searches or full‑text indexes, e.g., SELECT * FROM products WHERE description LIKE 'keyword%'; .

Batch inserts reduce overhead: INSERT INTO users (name, email) VALUES ('Alice', '[email protected]'), ('Bob', '[email protected]'); . It also shows how to temporarily disable uniqueness and foreign‑key checks during massive loads.

Query caching (for MySQL versions < 8.0) can be enabled with SET GLOBAL query_cache_size = 1048576; SET GLOBAL query_cache_type = ON; , though the article notes its removal in newer releases.

Configuration parameter tuning covers key InnoDB and server settings: SET GLOBAL innodb_buffer_pool_size = 2G; (recommended 60‑80% of RAM), SET GLOBAL thread_cache_size = 100; , SET GLOBAL table_open_cache = 4000; , SET GLOBAL tmp_table_size = 64M; SET GLOBAL max_heap_table_size = 64M; , SET GLOBAL innodb_flush_log_at_trx_commit = 2; , SET GLOBAL innodb_log_file_size = 256M; , SET GLOBAL max_connections = 500; , and others such as sort_buffer_size and read_buffer_size .

Correct index usage stresses creating indexes on frequently filtered or joined columns, following the left‑most prefix rule for composite indexes, avoiding functions on indexed columns, removing duplicate indexes, and being cautious with indexes on high‑update columns. Example: CREATE INDEX idx_abc ON table_name (a, b, c); and using covering indexes like CREATE INDEX idx_covering ON orders (order_id, order_date, customer_id); .

Additional pitfalls include avoiding SELECT DISTINCT when unnecessary, using LIMIT 1 for single‑row queries, preferring WHERE over HAVING , steering clear of functions in WHERE clauses, avoiding IS NULL on indexed columns, and replacing heavy OFFSET pagination with range queries.

The article concludes with a thorough explanation of the EXPLAIN statement, describing each column of its output and showing how to interpret a sample plan. It demonstrates improving a join query by adding an index on departments.name and ensuring hire_date is indexed, then re‑running EXPLAIN to verify a better execution plan.

Overall, the guide equips developers and database administrators with practical techniques to write efficient SQL, tune MySQL server parameters, and leverage EXPLAIN for continuous performance improvement.