Aki's DBC Blog - SQL Tuning/Optimization

Optimizing SQL Queries

April 26th, 2014

This week's DBC projects focused on how to use SQL and design database schemas. Since this was the focus, I figure I write a little bit about SQL query tuning/optimization.

So any newbie developer will design databases and query them at one point or another. The big question here is what can be done to be more performant, or in other words, how can you tune your SQL statements to be able to quickly get the information you're looking for? This is really important when manipulating large sets of data. Below, I've made some excerpts for a few good tricks, but before we dive in...

Some SQL best practices to keep your statements aesthetically clean:

Use single case for all SQL verbs (i.e. SELECT, FROM)
Begin all SQL verbs on a new line
Separate all words with a single space
Right or left align verbs within the initial SQL verb

Ok, now that's out of the way, here are some good tips and tricks to tune your SQL statements.

1. Specific is always better

Instead of doing this:

SELECT * FROM users;

Do this:

SELECT id, first_name, last_name FROM users;

2. Use HAVING sparingly to filter only after you've selected your data set

Bad:

SELECT hobby, count(hobby)
FROM users
GROUP BY hobby
HAVING hobby!= 'Baseball' AND subject!= 'Tennis';

Good:

SELECT hobby, count(hobby)
FROM users
WHERE hobby != 'Baseball'
AND subject != 'Tennis'
GROUP BY hobby;

3. Minimize the number of subquery blocks

Bad:

SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND dept = 'Electronics';

Good:

SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) FROM employee_details)
AND dept = 'Electronics';

4. Avoid using conditions in the WHERE clause

Bad:

SELECT id, first_name, age
FROM student_details WHERE age != 10;

Good:

SELECT id, first_name, age
FROM student_details WHERE age > 10;

5. Store large binary objects in a file system, and add the file path in the database.

In summary, the tricks above are not by any means meant to be an all-inclusive list, but this is a great place to get started when thinking about creating a foundation for more performant SQL querying.