Caution! Knowing those won’t make you a SQL ninja but save your time and make you better SQL programmer. From my experience and observing other programmers I have listed those most common mistakes.
0. Thinking Backup is unnecessary
Whenever you import data or do some operation by running SQL command and if it has any chance to modify or delete existing data. Never ignore to keep backup/export database.
Remeber if you lost $1000, you could make it again. However, you can not afford the cost of losing data. Imagine if it contains transaction history or subscription information.
If you already set daily or weekly based data backup, in future you would thank yourself for your decision.
1. Using root user or Giving all privilege to the user
Using root user to connect to the database is never a good practice.
Create a new user with only necessary permission. In most case dropping/creating/truncating a table is not required in business logic. Moreover, now most frameworks support soft delete which means table will contain data but have a column is_delete/deleted_at to determine if data is deleted or not. So DB user not even need DELETE permission. Just SELECT, INSERT, UPDATE is okay in most case.
2. No need to know SQL in depth
As all of the modern frameworks supports ORM (Object-Relational Mapping), many developers escape in-depth of SQL. Of course, ORM is a good practice. However, you need to understand SQL very well. Otherwise, it will feel like magic. If anything does not work as it should work, you will probably get stuck with it.
Also, performance or time complexity might not be satisfactory. In my experience, I have seen many beginner developers, instead of using COUNT, they have SELECTed all user/entities as an array and used array count function to get total user/amount. With a small amount of data, we won’t see any difference but imagine if we have 100k or more data it will take enormous time. I can even bet that some WordPress plugin still has this issue. We often get clients whose website was okay when they have a small amount of user, but as their site has grown, performance has decreased, and some functionality is not working.
And not using JOIN also effects time and space complexity.
SELECT * is not a good practice too. I would suggest only selecting the necessary column, and it would better space complexity.
3. Sorting by date
I have often seen this mistake that’s why I am adding it separately. You may say sorting by date is the easiest SQL task in the universe. Just add SORT BY date_column and the end of the query, and that’s it. Hold on imagine you have three data with (2018-1-31), (2018-3-31), (2018-11-31); you will find those months are sorted as 1, 11, 3. yes indeed, November came before March. To avoid this issue,
SELECT DATE_FORMAT(`date_column `, '%Y-%m-%d') as `date_column`
... SORT BY date_column
4. Depending too much on Bookish knowledge
I not telling that do not read a book. But not focus too much on bookish knowledge instead of practical experience. Database normalization is not effective every time. Sometime denormalization will give better performance overall. For optimal performance, the database is not the only one factor, application structure matters. Try to think out of the box.
Example, you have a bookstore, it has book’s table, author’s table. You have only stored author’s id in book’s table as know normalization rule and have author’s info in author’s table. Moreover, when you show book’s info (with author’s name) in-app or webpage, you use
JOIN ... on author_id = ...
However, instead of joining two table, selecting from only one table gives better performance (but data redundancy). You can add a column author_title. Do not just take my word; you can compare performance with a big dataset to see the difference.
Primary keys are automatically set as a clustered index that fastens queries. If you do not set a primary key, you won’t follow SQL standards and performance would suffer. As there always unique, but your table does not have any unique column, setting auto increment id is fine.
I have not covered SQL injection here, as every modern framework have protection against it and all user inputs.
Hope those help you and might be a lifesaver for someone.