Boost MySQL Performance: Field Types, JOIN vs Subqueries, UNION, and Transactions

1. Choose the most suitable column types

Smaller column widths improve query speed. Define postal codes with CHAR(6) instead of CHAR(255) or VARCHAR. Use integer types like MEDIUMINT rather than BIGINT. Mark columns NOT NULL when possible to avoid NULL checks. For enumerated text such as provinces or gender, use ENUM, which MySQL treats as numeric values and processes faster.

2. Use JOINs instead of sub‑queries

Sub‑queries can be replaced by JOINs for better performance. Example of deleting customers without orders using a sub‑query:

DELETE FROM customerinfo
WHERE CustomerID NOT IN (
    SELECT CustomerID FROM salesinfo
);

The equivalent JOIN version avoids creating a temporary result set:

SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID = salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL;

When the salesinfo table has an index on CustomerID, the JOIN runs significantly faster.

3. Use UNION instead of manual temporary tables

MySQL supports UNION since version 4.0, allowing you to combine multiple SELECT statements into a single result set without creating temporary tables. All SELECT statements must return the same number of columns.

Example:

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product;

4. Use transactions to ensure atomicity

Wrap related statements in a transaction so that either all succeed or all fail, preserving data consistency. Use BEGIN to start, COMMIT to finish, and ROLLBACK to revert on error.

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

If any statement fails, execute ROLLBACK to restore the database to its state before BEGIN. Transactions also provide locking mechanisms that protect concurrent users from interfering with each other's operations.