Essential MySQL Optimization Tips for Faster Queries

1. Choose the Most Appropriate Column Types

Store only the necessary amount of data by defining columns with the smallest suitable type (e.g., CHAR(6) for postal codes instead of CHAR(255), MEDIUMINT instead of BIGINT).

2. Prefer NOT NULL Columns

Mark columns as NOT NULL whenever possible so the engine can skip NULL checks during queries.

For enumerated text fields such as "province" or "gender", use ENUM because MySQL treats it as a numeric value, which is faster to compare.

3. Use JOINs Instead of Sub‑Queries

JOIN operations avoid the overhead of temporary tables created by sub‑queries. For example, deleting customers without orders can be done efficiently with a JOIN rather than a sub‑query.

JOINs are faster because MySQL does not need to materialize an intermediate result set.

Ensure the joined columns are indexed and have the same data type and character set.

SELECT A.id, A.name, B.id, B.name FROM A LEFT JOIN B ON A.id = B.id;

SELECT A.id, A.name, B.id, B.name FROM A RIGHT JOIN B ON B.id = A.id;

SELECT A.id, A.name, B.id, B.name FROM A INNER JOIN B ON A.id = B.id;

4. Replace Manual Temporary Tables with UNION

MySQL supports UNION (and UNION ALL) to combine multiple SELECT statements without creating explicit temporary tables. Use UNION ALL when duplicate rows are not a concern to avoid the extra sorting step.

5. Transactions

Wrap related statements in a transaction to guarantee atomicity, consistency, isolation, and durability (ACID). Use BEGIN (or START TRANSACTION), COMMIT, and ROLLBACK as needed.

BEGIN;
INSERT INTO salesinfo SET CustomerID = 14;
UPDATE inventory SET Quantity = 11 WHERE item = 'book';
COMMIT;

Transaction isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) can be set with SET TRANSACTION ISOLATION LEVEL …. Atomicity: all statements succeed or all are rolled back. Consistency: database rules remain intact before and after the transaction. Isolation: concurrent transactions do not interfere (supports various levels). Durability: committed changes survive crashes.

6. Use Foreign Keys

Define foreign keys to enforce referential integrity, e.g., linking customerinfo.customerid to salesinfo.customerid. Remember to use the InnoDB engine for foreign‑key support.

CREATE TABLE customerinfo (
  customerid INT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE salesinfo (
  salesid INT NOT NULL,
  customerid INT NOT NULL,
  PRIMARY KEY (customerid, salesid),
  FOREIGN KEY (customerid) REFERENCES customerinfo(customerid) ON DELETE CASCADE
) ENGINE=InnoDB;

7. Table Locking

When a transaction’s exclusive lock would cause contention, you can manually lock a table with LOCK TABLES … WRITE, perform the necessary SELECT / UPDATE operations, and then UNLOCK TABLES to avoid other sessions interfering.

8. Indexing

Indexes dramatically speed up lookups, especially for columns used in JOIN, WHERE, and ORDER BY clauses. Avoid indexing columns with many duplicate values (e.g., ENUM fields with low cardinality) and ensure the indexed column’s type matches the query’s type.

Full‑text indexes are supported only on MyISAM tables (MySQL 5.7 adds InnoDB full‑text support for Chinese).

9. Query‑Writing Best Practices

Avoid sub‑queries when a JOIN can be used; MySQL 5.6+ rewrites many sub‑queries into joins automatically.

Do not use functions on indexed columns (e.g., YEAR(date)) because MySQL will not use the index.

Replace multiple OR conditions with IN (...) for better index utilization.

Use a left‑anchored LIKE 'prefix%' pattern to allow index usage; avoid leading wildcards.

Limit result sets with LIMIT and avoid unnecessary ORDER BY when the order is irrelevant.

Ensure data types match (e.g., compare integer column with integer literal, not a string).

For GROUP BY, add ORDER BY NULL to suppress sorting when order is not needed.

Replace random row selection ( ORDER BY RAND()) with a range condition using RAND() and CEIL() to avoid full scans.

Batch INSERTs using a single statement with multiple value tuples instead of many individual INSERTs.