30 Proven SQL Optimization Tips to Boost Query Performance

Hello, I am San You. This article combines demo examples to present 30 practical SQL optimization suggestions, most derived from real‑world development experience.

1. Avoid using SELECT * and specify required columns

Bad example:

<code>select * from employee;</code>

Good example:

<code>select id, name from employee;</code>

Reason:

Fetch only needed fields to save resources and reduce network overhead.

SELECT * often prevents the use of covering indexes, causing extra table lookups.

2. Use LIMIT 1 when only one row (or the max/min row) is needed

Assume an employee table and we need the record of a person named "jay".

<code>CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code>

Bad example:

<code>select id, name from employee where name='jay';</code>

Good example:

<code>select id, name from employee where name='jay' limit 1;</code>

Reason:

Adding LIMIT 1 stops scanning after the first matching row, greatly improving efficiency.

If name is a unique index, LIMIT 1 is unnecessary because the optimizer already avoids a full table scan.

3. Avoid using OR in WHERE clauses

Given a user table with an index on userId :

<code>CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code>

Bad example:

<code>select * from user where userid=1 or age=18;</code>

Good example (using UNION ALL or separate queries):

<code>// using UNION ALL
select * from user where userid=1
union all
select * from user where age=18;

// or two separate statements
select * from user where userid=1;
select * from user where age=18;</code>

Reason:

Using OR can invalidate indexes, leading to full table scans.

When OR involves an unindexed column (e.g., age ), MySQL may first use the userId index, then perform a full table scan for the age condition, resulting in three steps: table scan + index scan + merge. If it had started with a full table scan, only one step would be needed.

4. Optimize pagination with large offsets

Standard pagination using LIMIT offset, count becomes inefficient when offset is large.

Bad example:

<code>select id, name, age from employee limit 10000, 10;</code>

Good example (use the last retrieved id as the offset):

<code>-- Return the maximum id from the previous query (keyset pagination)
select id, name from employee where id > 10000 limit 10;</code>

Reason:

MySQL must read and discard the first offset rows before returning the next count rows, which is costly for large offsets.

Keyset pagination (using the last id) skips the discard step and improves performance.

If business allows, limit the maximum page number because most users do not navigate far.

5. Optimize LIKE patterns

Placing the wildcard at the beginning prevents index usage.

Bad example:

<code>select userId, name from user where userId like '%123';</code>

Good example:

<code>select userId, name from user where userId like '123%';</code>

Reason:

Leading % disables the index.

Trailing % allows the index to be used.

6. Restrict result sets with precise WHERE conditions

Bad example (fetch all IDs then filter in application):

<code>List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = userIds.contains(userId);</code>

Good example (let the database decide):

<code>Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1'");
boolean isVip = userId != null;</code>

Reason:

Query only the data you need to avoid unnecessary data transfer and processing.

7. Avoid applying MySQL built‑in functions to indexed columns

Bad example:

<code>select userId, loginTime from loginuser where Date_Add(loginTime, Interval 7 DAY) >= now();</code>

Good example (use the function on the constant side):

<code>explain select userId, loginTime from loginuser where loginTime >= Date_Add(NOW(), INTERVAL -7 DAY);</code>

Reason:

Applying a function to an indexed column disables the index.

8. Do not perform arithmetic on columns in WHERE clauses

Bad example:

<code>select * from user where age-1 = 10;</code>

Good example:

<code>select * from user where age = 11;</code>

Reason:

Even if age is indexed, the expression prevents the optimizer from using the index.

9. Prefer INNER JOIN ; if using LEFT JOIN , keep the left table small

INNER JOIN : returns only rows that match in both tables. LEFT JOIN : returns all rows from the left table, with NULLs for non‑matching right rows. RIGHT JOIN : returns all rows from the right table.

When both sides satisfy the query, prefer INNER JOIN . If LEFT JOIN is required, filter the left side as early as possible.

Bad example:

<code>select * from tab1 t1 left join tab2 t2 on t1.size = t2.size where t1.id > 2;</code>

Good example (filter left table first):

<code>select * from (select * from tab1 where id > 2) t1 left join tab2 t2 on t1.size = t2.size;</code>

Reason:

INNER JOIN often yields fewer rows and better performance.

With LEFT JOIN , a smaller left result set reduces the amount of data processed.

10. Avoid != or <> operators in WHERE clauses

Bad example:

<code>select age, name from user where age &lt;&gt; 18;</code>

Good example (split into two range queries):

<code>// consider two separate queries
select age, name from user where age &lt; 18;
select age, name from user where age &gt; 18;</code>

Reason:

These operators often cause the optimizer to skip index usage.

11. Follow the left‑most prefix rule when using composite indexes

Table with composite index idx_userid_age (userId, age) :

<code>CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;</code>

Bad example (query only by age ):

<code>select * from user where age = 10;</code>

Good examples (use leftmost columns):

<code>// satisfies left‑most rule
select * from user where userid = 10 and age = 10;
// also valid
select * from user where userid = 10;</code>

Reason:

A composite index (k1,k2,k3) can be used as (k1), (k1,k2), or (k1,k2,k3); queries must start with the leftmost column.

Violating this rule often makes the index unusable.

12. Create indexes on columns used in WHERE and ORDER BY to avoid full scans

Bad example:

<code>select * from user where address='深圳' order by age;</code>

Good example (add a composite index):

<code>alter table user add index idx_address_age (address, age);</code>

13. Use batch inserts for large data loads

Bad example (single insert per row):

<code>for(User u : list){
  INSERT into user(name,age) values(#name#,#age#);
}</code>

Good example (batch of 500 rows):

<code>// one batch of 500 rows
insert into user(name,age) values
<foreach collection="list" item="item" separator=",">
  (#{item.name},#{item.age})
</foreach>;</code>

Reason:

Batch inserts dramatically improve performance and reduce execution time.

14. Use covering indexes when possible

A covering index allows the query to be satisfied entirely from the index without touching the table.

Bad example (LIKE with leading wildcard):

<code>// fuzzy query, index not used
select * from user where userid like '%123%';</code>

Good example (query only needed columns, index can cover):

<code>// id is primary key, so this becomes a covering index
select id, name from user where userid like '%123%';</code>

15. Use DISTINCT sparingly

DISTINCT removes duplicate rows. It is efficient when selecting few columns but costly with many columns.

Bad example:

<code>SELECT DISTINCT * from user;</code>

Good example:

<code>select DISTINCT name from user;</code>

Reason:

When many columns are involved, the engine must compare entire rows, increasing CPU and execution time.

16. Remove redundant or duplicate indexes

Bad example (both single‑column and composite indexes on userId ):

<code>KEY `idx_userId` (`userId`)
KEY `idx_userId_age` (`userId`,`age`)</code>

Good example (keep only the composite index, which already includes the single‑column prefix):

<code>// drop the redundant single‑column index
KEY `idx_userId_age` (`userId`,`age`)</code>

Reason:

Duplicate indexes increase maintenance overhead and confuse the optimizer.

17. Optimize large‑scale UPDATE/DELETE statements by batching

Bad example (delete millions of rows at once or loop row‑by‑row):

<code>// delete 100k+ rows in one statement
delete from user where id < 100000;
// or delete one row per iteration (very slow)
for(User user : list){
  delete from user;
}</code>

Good example (process in small batches):

<code>// batch delete, e.g., 500 rows per batch
delete user where id < 500;
delete product where id >= 500 and id < 1000;</code>

Reason:

Large deletions can cause lock‑wait‑timeout errors; batching avoids long locks and improves concurrency.

18. Prefer default values over NULL checks in WHERE

Bad example:

<code>select * from user where age is not null;</code>

Good example (use a numeric default, e.g., 0):

<code>select * from user where age > 0;</code>

Reason:

Using default values can allow index usage more reliably than IS NULL / IS NOT NULL , which sometimes forces a full scan depending on optimizer cost estimates.

19. Limit the number of tables joined in a single query

More than five tables increase compilation cost and reduce readability.

Splitting large joins into smaller queries improves maintainability.

Excessive joins often indicate poor schema design.

20. Choose between IN and EXISTS wisely

When the subquery returns a small result set, IN is efficient; when the outer table is smaller, EXISTS is preferable.

Example using IN :

<code>select * from A where deptId in (select deptId from B);</code>

Equivalent using EXISTS :

<code>select * from A where exists (select 1 from B where A.deptId = B.deptId);</code>

21. Replace UNION with UNION ALL when duplicates are impossible

Bad example:

<code>select * from user where userid=1
union
select * from user where age=10;</code>

Good example:

<code>select * from user where userid=1
union all
select * from user where age=10;</code>

Reason:

UNION always performs a distinct sort, which adds overhead; UNION ALL skips this step.

22. Keep the total number of indexes per table reasonable (generally ≤5)

Too many indexes speed up reads but slow inserts/updates because each write must maintain all indexes.

When the index count exceeds about five, evaluate which indexes are truly necessary.

23. Use numeric data types for numeric data

Bad example (store numeric ID as VARCHAR ):

<code>`king_id` varchar(20) NOT NULL COMMENT 'Guardian Id';</code>

Good example:

<code>`king_id` int(11) NOT NULL COMMENT 'Guardian Id';</code>

Reason:

Numeric columns are faster for comparisons, joins, and consume less storage.

24. Do not index columns with high duplicate density (e.g., gender)

Indexes on low‑cardinality columns often get ignored by the optimizer because a full scan is cheaper.

25. Avoid returning excessive data to the client

Bad example (fetch all rows for a year of watch history):

<code>select * from LivingInfo where watchId = useId and watchTime >= Date_sub(now(), Interval 1 Y);</code>

Good example (use pagination or limit the result set):

<code>-- pagination
select * from LivingInfo where watchId = useId and watchTime >= Date_sub(now(), Interval 1 Y) limit offset, pageSize;

-- if front‑end pagination, fetch only the first 200 rows
select * from LivingInfo where watchId = useId and watchTime >= Date_sub(now(), Interval 1 Y) limit 200;</code>

26. Use table aliases and qualify column names

Bad example:

<code>select * from A inner join B on A.deptId = B.deptId;</code>

Good example:

<code>select member.name, deptment.deptName from A member inner join B deptment on member.deptId = deptment.deptId;</code>

27. Prefer VARCHAR / NVARCHAR over CHAR / NCHAR

Bad example:

<code>`deptName` char(100) DEFAULT NULL COMMENT 'Department name';</code>

Good example:

<code>`deptName` varchar(100) DEFAULT NULL COMMENT 'Department name';</code>

Reason:

Variable‑length fields use less storage and improve query performance.

28. Filter unnecessary rows before GROUP BY to improve efficiency

Bad example:

<code>select job, avg(salary) from employee group by job having job='president' or job='management';</code>

Good example:

<code>select job, avg(salary) from employee where job='president' or job='management' group by job;</code>

29. Enclose string literals in quotes in WHERE clauses

Bad example (numeric comparison on a string column):

<code>select * from user where userid = 123;</code>

Good example (use quotes):

<code>select * from user where userid = '123';</code>

Reason:

Without quotes MySQL performs an implicit type conversion, which prevents index usage.

30. Use EXPLAIN to analyze query execution plans

Running EXPLAIN on your queries helps you see whether indexes are used and identify bottlenecks.

<code>explain select * from user where userid = 10086 or age = 18;</code>

By following these 30 tips, developers can write more efficient SQL, reduce resource consumption, and improve overall application performance.