Let’s discuss some tips and tricks to avoid major mistakes in SQL
Never use Select *
- select *, gets the data from all the columns, which increases the latency and is expensive for huge data.
- Instead, get only the required fields, which limits the size of each record
Use EXISTS() Instead of COUNT()
- If you want to know whether a record/result exists or not, it is better to use exists(), rather than count()
- count(), will browse the entire table to get you the number of records.
- Whereas, exists(), will get back to you, when it finds the first record for the query, saving you time and computing.
Functions on indexed columns is useless
- Using functions on indexed columns, while querying, will make the indexes remain useless.
- In order to use indexes, we need to avoid adding functions on the indexed columns.
LIMIT Statements
- Most common used pagination is LIMIT, OFFSET, which is not optimal.
- It is fast for smaller and immediate sets like “LIMIT 0, 10”
- But when OFFSET is changed to 1000000, it takes too long, since database doesn’t know where 1000000th record exists, it starts from scratch till it finds 1000000th row.
- Better approach would be to add another filter, mostly indexed column, like below
Use GROUP BY Instead of DISTINCT
- The distinct is an expensive operation, and doesn’t use indexes if available.
- Faster and easier way to do the same is to use group by.
In Conclusion…
I haven’t covered many major SQL pitfalls. But these are some of the top mistakes, even made by experienced developers. Now you get to avoid them 😁
That’s it! Please let me know about your views and comment below for any clarifications.
If you found value in reading this, please consider sharing it with your friends and also on social media 🙏
Also, to be notified about my upcoming articles, subscribe to my newsletter below (I’ll not spam you 😂)
Blog of Codes | Rahul | Substack
Articles about cloud architecture and programming. Click to read Blog of Codes, by Rahul, a Substack publication. Launched a month ago.
blogofcodes.substack.com
Top comments (0)