9 Essential MySQL Optimization Techniques to Boost Performance

1. Choose the most appropriate column attributes

Define column widths as small as possible; for example, use CHAR(6) for postal codes instead of CHAR(255), and prefer MEDIUMINT over BIGINT when the range permits.

2. Set fields to NOT NULL whenever possible

Mark columns as NOT NULL to avoid extra NULL checks during queries. Enumerated types ( ENUM) can replace textual fields like "province" or "gender" because MySQL treats them as numeric values, which are faster to compare.

3. Use JOINs instead of sub‑queries

JOIN operations are generally faster than sub‑queries because they avoid creating temporary tables. For example, deleting customers without orders can be done efficiently with a JOIN when the CustomerID column is indexed.

Example JOIN query:

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

4. Replace manual temporary tables with UNION

MySQL supports UNION (or UNION ALL when duplicate rows are acceptable) to combine multiple SELECT statements without creating explicit temporary tables. All SELECTs must return the same number of columns.

5. Use transactions to ensure data consistency

Wrap related statements between BEGIN (or START TRANSACTION) and COMMIT. If any statement fails, ROLLBACK restores the database to its previous state, preserving ACID properties.

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

Transactions also provide locking to prevent concurrent interference.

6. Define foreign keys for referential integrity

Use InnoDB tables with FOREIGN KEY constraints to ensure related records exist. Example:

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

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

7. Lock tables when necessary

For high‑contention scenarios, explicit LOCK TABLES … WRITE can protect a set of operations, ensuring no other session modifies the locked tables until UNLOCK TABLES is issued.

8. Use indexes wisely

Create indexes on columns used in JOIN, WHERE, and ORDER BY clauses. Avoid indexing columns with many duplicate values (e.g., ENUM fields with low cardinality) as they may degrade performance.

Full‑text indexes are available for MyISAM tables (MySQL 3.23.23+), while InnoDB supports them from MySQL 5.6 onward.

9. Additional query‑optimization tips

Avoid sub‑queries : Rewrite as JOINs when possible.

Replace functions in WHERE clauses : Use range conditions instead of YEAR(date). SELECT * FROM t WHERE date >= '2016-01-01'; Use IN instead of multiple ORs : SELECT * FROM t WHERE LOC_IN IN (10,20,30); Optimize LIKE patterns : Leading wildcards prevent index usage; use prefix matching when possible. SELECT * FROM t WHERE name LIKE 'de%'; Limit result sets with LIMIT to avoid scanning unnecessary rows.

Ensure type consistency in comparisons to allow index usage. SELECT * FROM t WHERE id = 19; Disable unnecessary ORDER BY when counting rows.

SELECT COUNT(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

Batch INSERTs to reduce round‑trips:

INSERT INTO t (id, name) VALUES (1,'Bea'), (2,'Belle'), (3,'Bernice');

By applying these practices, MySQL queries become faster, more reliable, and easier to maintain.