Writing High‑Quality SQL: Practical MySQL Optimization Tips

Prefer Specific Columns Over SELECT *

Fetching only required fields saves resources and network bandwidth. Using SELECT * often prevents the use of covering indexes, causing a costly table‑row lookup.

Use LIMIT 1 When Only One Row Is Needed

Adding LIMIT 1 stops the scan after the first matching row, dramatically improving performance. If the column is already a unique index, the limit is unnecessary because the optimizer already avoids a full scan.

SELECT id, name FROM student WHERE name = '霸道' LIMIT 1;

Avoid OR in WHERE Clauses

OR can invalidate indexes, forcing a full table scan. Replace it with UNION ALL of two separate queries that each can use an index.

SELECT id, name FROM student WHERE userid = 1
UNION ALL
SELECT id, name FROM student WHERE age = 18;

Optimize LIMIT‑Based Pagination

Large offsets make MySQL scan and discard rows before returning the desired page. Three alternatives improve this:

Use the last retrieved primary‑key value as the offset: SELECT id, name FROM student WHERE id > 10000 LIMIT 10; Order by an indexed column and limit: SELECT id, name FROM student ORDER BY id LIMIT 10000,10; If business permits, cap the page number to keep offsets small.

Handle LIKE Patterns Wisely

Leading wildcards ( '%text') may still use an index if the query returns only indexed columns. Tests show:

EXPLAIN SELECT sid, sname, sage FROM test_student WHERE sname LIKE '%三';

When the pattern starts with a wildcard and the select list includes non‑indexed columns, MySQL falls back to a full scan.

Restrict Returned Columns in WHERE Conditions

Query only the needed rows to avoid unnecessary data transfer. Example:

Long userId = sqlMap.queryObject("SELECT userId FROM user WHERE userId='userId' AND isVip='1'");
boolean isVip = userId != null;

Avoid Built‑in Functions on Indexed Columns

Applying functions like DATE_ADD to indexed columns disables the index. Rewrite the condition so the column itself is compared:

SELECT userId, loginTime FROM loginuser WHERE loginTime >= DATE_ADD(NOW(), INTERVAL -7 DAY);

Do Not Use Expressions on Indexed Columns

Expressions such as sage-1 = 10 prevent index usage. Use the plain column comparison instead:

EXPLAIN SELECT sid, sname, sage FROM test_student WHERE sage = 11;

Prefer INNER JOIN; Keep LEFT JOIN Input Small

INNER JOIN returns only matching rows and often processes fewer rows than LEFT JOIN. If LEFT JOIN is required, filter the left table first to reduce its result set.

SELECT * FROM (SELECT * FROM tab1 WHERE id > 2) t1 LEFT JOIN tab2 t2 ON t1.size = t2.size;

Avoid != or <> Operators

These operators can cause the optimizer to abandon the index and perform a full scan.

EXPLAIN SELECT sid, sname, sage FROM test_student WHERE sage <> 11;

Follow the Left‑most Prefix Rule for Composite Indexes

For an index on (k1, k2, k3), queries must filter on k1 first to use the index. Otherwise the optimizer may ignore it.

CREATE INDEX group_index ON fruit(id, name, price);
EXPLAIN SELECT * FROM fruit WHERE id='1' AND city='北京';
EXPLAIN SELECT * FROM fruit WHERE name='苹果' AND city='北京';

Build Indexes on WHERE and ORDER BY Columns

Creating indexes on columns used in filtering and sorting reduces the chance of full table scans.

Batch Insert Large Volumes

When inserting massive data, use batch techniques (e.g., MyBatis‑Plus batch insert) to improve throughput.

Use Covering Indexes When Possible

A covering index lets MySQL satisfy a query using only the index, avoiding a table lookup.

CREATE TABLE test_users (
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  PRIMARY KEY (id)
);
CREATE INDEX idx_covering ON test_users (first_name, last_name);
SELECT first_name, last_name FROM test_users WHERE first_name = 'John';

Use DISTINCT Sparingly

DISTINCT on many columns forces the engine to compare rows, increasing CPU time. Limit its use to a few columns.

SELECT DISTINCT name FROM user;

Remove Redundant and Duplicate Indexes

Duplicate indexes increase maintenance overhead and confuse the optimizer.

-- Bad: two indexes on userId and (userId, age)
KEY 'idx_userId' (userId), KEY 'idx_userId_age' (userId, age);
-- Good: keep only the composite index
KEY 'idx_userId_age' (userId, age);

Optimize UPDATE/DELETE Statements

Avoid modifying or deleting massive rows in a single statement; split into batches to prevent high CPU usage and lock‑wait‑timeout errors.

Prefer Default Values Over NULL in WHERE

Replacing NULL checks with default values can enable index usage and clarify intent.

SELECT * FROM user WHERE age > 0;  -- assuming default age = 0

Limit the Number of Joined Tables

More than five joins increase compilation cost and reduce readability. Break large joins into smaller queries when possible.

Choose BETWEEN EXISTS and IN Appropriately

Use IN when the subquery result set is small; use EXISTS when the outer table is smaller. This minimizes the amount of data the engine must iterate.

SELECT * FROM A WHERE deptId IN (SELECT deptId FROM B);  -- B small
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.deptId = B.deptId);  -- A small

Prefer UNION ALL When Duplicates Are Impossible

UNION removes duplicates and sorts the result, which is unnecessary if duplicates cannot occur.

SELECT * FROM user WHERE userid=1 UNION ALL SELECT * FROM user WHERE age=10;

Keep the Number of Indexes Reasonable

More than five indexes per table can degrade insert/update performance. Retain only those that provide clear query benefits.

Use Numeric Types for Purely Numeric Data

Numeric columns are faster to compare and join than VARCHAR equivalents, and they consume less storage.

Avoid Indexes on Low‑Cardinality Columns

Columns with many repeated values (e.g., gender) often cause the optimizer to skip the index because a full scan is cheaper.

Limit Result Set Size Sent to Clients

Implement pagination to avoid returning excessive rows.

Use Table Aliases and Prefix Columns

Aliasing tables and prefixing column names improves query readability.

SELECT t1.id, t2.name FROM user AS t1 JOIN profile AS t2 ON t1.id = t2.user_id;

Prefer VARCHAR/NVARCHAR Over CHAR/NCHAR

Variable‑length strings use less storage and are faster to search when the field size is small.

Filter Unnecessary Rows Before GROUP BY

Apply WHERE conditions before grouping to reduce the amount of data the engine must aggregate.

SELECT job, AVG(salary) FROM employee WHERE job='president' OR job='manager' GROUP BY job;

Quote String Literals in WHERE

Omitting quotes on string columns can cause the optimizer to ignore indexes.

EXPLAIN SELECT * FROM test_student WHERE eid = '1';

Use EXPLAIN to Analyse Query Plans

Running EXPLAIN reveals whether indexes are used and helps identify bottlenecks.