SQL Optimization

As we have seen with Ruby, there are many ways to go about solving the same problem to get the same output. But some of those ways are more efficient than others. The same is true for SQL. Below are a couple examples of some ways to optimize queries for SQL.

1.Refer to actual column names rather than using the * operator.

Initial Query:

		SELECT * FROM account_info;
		

Optimized Query:

		SELECT id, first_name, last_name, phone_number, address
		FROM account_info;
		

2. When selecting for subqueries in the main queries, try to select for them all at once.

Initial Query:

		SELECT name
		FROM student
		WHERE grade = (SELECT MAX(grade) FROM student_info)
		AND age = (SELECT MAX(age) FROM student_info);
		

Optimized Query:

		SELECT name
		FROM student
		WHERE (grade, age) = (SELECT MAX(grade), MAX(age)
		FROM student_info);
		

3. Limit the use of IN, as it has the slowest performance. When possible, use EXISTS.

4. Use the word HAVING only when necessary. It does not change the selected items from the query, but acts as a filter.

There are many more ways to optimize SQL queries. For further resources and more examples, see this link.