MySQL Optimization: Concepts, Schema Design, Indexing, and Query Tuning

The article focuses on the relational database MySQL, providing a concise overview of its core concepts and practical optimization strategies for both schema creation and query execution.

Basic concepts : MySQL operates in three logical layers – client connection, server parsing/optimization, and storage engine. It uses lock mechanisms (shared/read and exclusive/write locks) with optimistic and pessimistic variants, and lock granularity (table vs. row). InnoDB implements MVCC to reduce lock overhead. Transactions guarantee atomicity and support four isolation levels – Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Storage engines differ: InnoDB offers ACID transactions and crash recovery, while MyISAM lacks transaction support and row‑level locking.

Creation‑time optimization – schema and data types : Choose the smallest appropriate integer type (TinyInt to BigInt) and use UNSIGNED when negative values are unnecessary. For real numbers, prefer FLOAT / DOUBLE for approximate values and DECIMAL for exact precision. Store strings with VARCHAR (variable length) or CHAR (fixed length) as needed, and reserve BLOB / TEXT for large blobs. Date‑time values should use DATETIME or the more compact TIMESTAMP . Recommendations include using integer identifier columns, avoiding oversized VARCHAR , limiting ORM‑generated schemas, balancing normalization vs. denormalization, employing summary or cache tables, and using shadow tables for safe migrations.

Index optimization : MySQL primarily uses B‑Tree indexes, which work efficiently with leftmost‑most column prefixes. Limitations include inability to use the index if the query does not start with the leftmost column, skipping columns, or having a range condition before other indexed columns. Hash indexes support only equality matches and cannot be used for sorting. Best practices advise selecting the proper index type, avoiding functions on indexed columns, using prefix indexes for long strings, constructing multi‑column indexes wisely, eliminating redundant indexes, placing range predicates last, avoiding NULLs in indexed columns, and keeping index length reasonable.

Query‑time optimization : Three key performance metrics are response time, rows scanned, and rows returned. Practical tips include avoiding SELECT * , eliminating unnecessary rows, splitting large operations into batches, decomposing complex joins, using COUNT(*) for total rows, grouping by identifier columns, applying delayed joins, and optimizing pagination with covering indexes. An example query demonstrates an inner join with LIMIT and suggests using UNION ALL instead of UNION when duplicate removal is not required.

SELECT id,
       NAME,
       age
FROM student s1
INNER JOIN (
    SELECT id
    FROM student
    ORDER BY age
    LIMIT 50,5
) AS s2 ON s1.id = s2.id

Additional MySQL 5.7 features : Generated columns allow a column's value to be computed from other columns, e.g.:

CREATE TABLE triangle (
    sidea DOUBLE,
    sideb DOUBLE,
    area DOUBLE AS (sidea * sideb / 2)
);
INSERT INTO triangle(sidea, sideb) VALUES(3,4);
SELECT * FROM triangle;

MySQL also supports a native JSON type with built‑in functions:

CREATE TABLE json_test (name JSON);
INSERT INTO json_test VALUES('{"name1": "value1", "name2": "value2"}');
SELECT * FROM json_test WHERE JSON_CONTAINS(name, '$.name1');

EXPLAIN output fields are explained: select_type , type , possible_keys , key , key_len , rows , and extra , each indicating how the optimizer plans to execute a query.

Overall, the article provides a comprehensive guide to MySQL performance tuning, from low‑level storage engine behavior to high‑level query optimization techniques.