During one of my interviews I was asked whether I knew how to make SQL queries more efficient. At that point in time I had barely used SQL and had to ask the interviewer to move on to another subject.
But since I have started to use SQL in the DSSG Program I’ve started to deal with SQL queries that can possibly take a while to run. And I started to wonder how to make SQL queries more efficient and I found this article.
The following is a quick summary of link.
Tips to Speed Up Queries
- Try to restrict the queries result set by using the WHERE clause.
This one seems obvious.
- Try to restrict the queries result st by returning only the particular columns from the table, not all the table’s columns.
Another one that seems obvious.
- If you need to return the total table’s row count, use an alternative instead of
SELECT COUNT(*)statement.
This one is interesting because I didn’t know there were alternatives, I usually do run the SELECT COUNT(*) statement! I will update how to get the table count in Postgres soon.
- Try to avoid the HAVING clause whenever possible.
This is an interesting one, I wouldn’t think the HAVING clause would be that computationally expensive, but a good point is made that if it’s possible to filter out unneeded values with a WHERE clause, that should be used.
- Use SELECT statements with LIMIT keyword if you need to return only the first n rows.
Makes sense, I do this quite often when I am testing to make sure my query is running as expected. Usually used to run join on a subset before running it on the whole data set.
- Try to use UNION ALL statement stead of UNION.
UNION ALL does not look for duplicates while UNION does look for duplicates whether they exist or not.
Something I’ve learned about recently is Postgres’ EXPLAIN and ANALYZE. More information is to be added soon.
I will add more SQL efficiency tips as I come across them!