21 Essential SQL Habits to Boost Performance and Avoid Regrets

1. Run EXPLAIN after writing SQL (Performance Optimization)

Developers should habitually run EXPLAIN on their queries to check execution plans, especially to verify index usage.

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

2. Add LIMIT to DELETE or UPDATE statements (Regret Medicine)

When executing DELETE or UPDATE, always include a LIMIT to reduce risk.

<code>delete from euser where age > 30 limit 200;</code>

Benefits of using LIMIT :

Reduce cost of mistakes : If a typo occurs, only a limited number of rows are affected, making recovery easier.

Potentially higher efficiency : With LIMIT 1 , the engine can stop after the first match.

Avoid long transactions : Limiting rows prevents large write locks that could block other operations.

Prevent CPU overload : Deleting massive amounts without limits can saturate CPU.

3. Add comments to tables and columns (Elegant Standards)

Document every table and column with descriptive comments for easier maintenance.

Good example:

<code>CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key Id',
  `name` varchar(255) DEFAULT NULL COMMENT 'Account Name',
  `balance` int(11) DEFAULT NULL COMMENT 'Balance',
  `create_time` datetime NOT NULL COMMENT 'Creation Time',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 COMMENT='Account Table';</code>

Bad example:

<code>CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8;</code>

4. Keep SQL keyword case consistent and use indentation (Elegant Standards)

Good example:

<code>SELECT stu.name, SUM(stu.score)
FROM Student stu
WHERE stu.classNo = '1班'
GROUP BY stu.name;</code>

Bad example:

<code>SELECT stu.name, sum(stu.score) from Student stu WHERE stu.classNo = '1班' group by stu.name.</code>

Consistent keyword case and proper indentation make SQL more readable.

5. Specify column names in INSERT statements (Elegant Standards)

Bad example:

<code>insert into Student values ('666', '拾田螺的小男孩', '100');</code>

Good example:

<code>insert into Student(student_id, name, score) values ('666', '拾田螺的小男孩', '100');</code>

6. Test changes in a staging environment and document rollback plans (Regret Medicine)

Execute SQL changes in a test environment first to catch syntax errors.

Write detailed operation steps, especially when there are dependencies (e.g., modify schema then add data).

Provide a rollback plan and have the change reviewed before production.

7. Include primary key, create_time, and update_time fields in tables (Elegant Standards)

Bad example:

<code>CREATE TABLE `account` (
  `name` varchar(255) DEFAULT NULL COMMENT 'Account Name',
  `balance` int(11) DEFAULT NULL COMMENT 'Balance',
);</code>

Good example:

<code>CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key Id',
  `name` varchar(255) DEFAULT NULL COMMENT 'Account Name',
  `balance` int(11) DEFAULT NULL COMMENT 'Balance',
  `create_time` datetime NOT NULL COMMENT 'Creation Time',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 COMMENT='Account Table';</code>

Reasons:

A primary key gives the table a unique identity.

Creation and update timestamps aid auditing and tracking.

8. Verify indexes for columns used in WHERE, ORDER BY, GROUP BY (Performance Optimization)

Bad example:

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

Good example:

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

9. Backup data before modifying or deleting (Regret Medicine)

Always back up the data you intend to change or delete so you can recover from accidental operations.

10. Beware of implicit type conversion in WHERE clauses (Performance Optimization)

Bad example:

<code>// userid is a varchar column
select * from user where userid = 123;</code>

Good example:

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

Reason: Comparing a string column with a numeric literal forces MySQL to perform implicit conversion, which can invalidate indexes.

11. Define columns as NOT NULL when possible (Elegant Standards)

Space saving : NULL columns require an extra byte for the null flag.

Null handling : NULL values need careful handling in calculations and comparisons.

12. Verify with a SELECT before running UPDATE or DELETE (Regret Medicine)

Run a SELECT using the same WHERE clause first; once the result is confirmed, proceed with the UPDATE or DELETE.

13. Avoid SELECT *; specify needed columns (Performance Optimization)

Bad example:

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

Good example:

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

Benefits:

Reduces resource usage and network overhead.

Enables covering indexes, avoiding table lookups and improving query speed.

14. Use InnoDB storage engine for all tables (Elegant Standards)

InnoDB supports transactions, row‑level locking, and better recovery, making it suitable for most workloads.

15. Standardize on UTF‑8 charset for databases and tables (Elegant Standards)

Using UTF‑8 avoids garbled text and index issues caused by charset conversion. For emoji storage, use utf8mb4 .

16. Prefer VARCHAR over CHAR (Performance Optimization)

Bad example:

<code>`deptName` char(100) DEFAULT NULL COMMENT '部门名称';</code>

Good example:

<code>`deptName` varchar(100) DEFAULT NULL COMMENT '部门名称';</code>

Reason: Variable‑length fields save storage space.

17. Update column comments when their meaning changes (Elegant Standards)

Keep comments up‑to‑date, especially for enum‑type columns, to aid future maintenance.

18. Use BEGIN + COMMIT for command‑line data modifications (Regret Medicine)

Good example:

<code>begin;
update account set balance = 1000000 where name = '拾田螺的小男孩';
commit;</code>

Bad example:

<code>update account set balance = 1000000 where name = '拾田螺的小男孩';</code>

19. Follow naming conventions for indexes (Elegant Standards)

Primary key indexes: pk_<column> ; unique indexes: uk_<column> ; regular indexes: idx_<column> .

20. Avoid functions or expressions on indexed columns in WHERE clauses (Performance Optimization)

Bad example:

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

Good example:

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

Reason: Applying functions to indexed columns disables index usage.

21. Batch large updates or deletes (Regret Medicine)

Bad example:

<code>delete from account limit 100000;</code>

Good example:

<code>for each (200 times) {
  delete from account limit 500;
}</code>

Reasons:

Large operations can cause master‑slave replication lag.

They create huge transactions that block other queries.

Excessive data volume can saturate CPU.

References

Is adding LIMIT to DELETE a good habit?

Alibaba Development Manual