Just sharing some good SQL habbits I’m doing at my daily work.
EXPLAIN before developing SQL queries.
LIMIT before running
UPDATE. (We recently had a PROD issue when someone added a
DELETE statement in migration script, then server exhausted due to deleting too many rows.)
COMMENT while writing or updating any DDL. (We had a long discussion on the meaning of a 10-year old column)
4. Add a rollback plan before running any query.
5. Write a well-explained description about each running step. (We had a poor dude deploy code at mid-night, but have no idea how to deploy a SQL, and have no way to rollback.)
updated_at when adding new table. (That’s why Laravel has
UPDATED_AT as default, because it is highly reused and highly recommended. Don’t think I’m special, please stay foolish.)
7. Check columns’
GROUP BY statements. Especially multi-table reference column.
8. Backup, Backup, Backup, before any update or deletion.
9. Avoid implicit type conversion. Especially,
index won’t work on imcompatible type.
10. Try to avoid
NULL as possible.
NOT NULL saves more disk space, and no need to worry about
null value in the code base.
SELECT query before
DELETE. Make sure changing the right thing.
12. Try to avoid use
SELECT *. Use
SELECT <column1>, <column2> ... instead as possible.
13. Try to use
Innodb because it supports transaction, support row-level lock, better recovery mechanism, and better high-transaction and concurrency performance.
utf8mb64 code, to avoid distorted encoding, also make sure the index still works.
varchar rather than
char, it saves disk space and higher query performance when search for short text.
COMMIT while updating anything, in case partially executed.
17. Use standard naming convention:
pk as primary key’s name.
fk as foreign key’s name.
uk as unique key’s name.
idx_ as prefix of any index.
18. Do not execute any function or calculation in
WHERE statement, because index will not work.
19. Consider about batch query if dealing with huge amount of data.