Just sharing some good SQL habbits I’m doing at my daily work.
1. Do EXPLAIN before developing SQL queries.
2. Add LIMIT before running DELETE or 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.)
3. Add 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.)
6. Add id, created_at and updated_at when adding new table. (That’s why Laravel has $primaryKey, CREATED_AT and UPDATED_AT as default, because it is highly reused and highly recommended. Don’t think I’m special, please stay foolish.)
7. Check columns’ index for WHERE, ORDER BY, 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.
11. Run SELECT query before UPDATE or 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.
14. Use utf8 or utf8mb64 code, to avoid distorted encoding, also make sure the index still works.
15. Use varchar rather than char, it saves disk space and higher query performance when search for short text.
16. Use BEGIN and 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.