Mastering SQL Optimization: A Beginner’s Guide to Faster and More Efficient Queries

Introduction

Has it ever crossed your mind why some SQL queries run in a flash while others take forever?

I recently came across SQL Queries that yes, they retrieve the required data but the query have longer wait times before they execute. As a database admin it is your duty to ensure SQL Queries takes as less time as possible to execute and they are efficient. This in turn ensures faster retrieval of data while using less system resources.

What is SQL Optimization and why is it useful

SQL optimization is the process writing SQL queries that retrieves data quickly and efficiently. In large databases, unoptimized SQL queries often take long to execute and in return they use a lot of system resources. SQL optimization helps solve this problem by ensuring less execution time and less usage of system resources.

Techniques to ensure your SQL queries are optimized

Select only the columns you need

When writing SQL query ensure you select only the columns you need in your output instead of selecting all columns

Example:
Instead of

SELECT *
FROM table1

use

SELECT col1, col2, col3
FROM table1

Use LIMIT clause

LIMIT clause to restrict your SQL to return only the necessary rows you need instead of all rows

Example:
You need to get the top 10 most paid employees

Instead of

SELECT name, age, salary
FROM employees
ORDER BY salary DESC;

Do this

SELECT name, age, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

Use indexes

Use indexes in your database especially on tables you will use SELECT, JOIN, WHERE, ORDER BY. Although, avoid indexing every column, as this can increase storage and slow down INSERT, UPDATE, and DELETE
operations.

To create an index:

CREATE INDEX idx_customer_id 
ON orders (customer_id);

Avoid using functions on indexed columns

Using functions on indexed columns in the WHERE clause will slow down the query since it makes the database not use the index. Instead make sure your data in your columns are stored in the format you may need them stored. For example:
Instead of

SELECT id, name, age
FROM employee
WHERE LOWER(f_name) = 'james';

Ensure first name in the employee table is stored in lower case to avoid using a function in the indexed column f_name

Optimize JOINS in SQL

Another method of optimizing your SQL queries is by ensuring you Optimize join queries in SQL to get the fastest result possible.

• Use indexes: You should create indexes on columns used in join conditions
• Use INNER JOIN instead of other types of join: INNER JOIN is generally faster than OUTER JOIN because it only returns rows where there is a match in both tables.

Use appropriate Data Types

Ensure you use the correct data types for columns, and avoid using large data types for columns that has smaller data. For example, In the first name column don`t use VARCHAR(255) which is the maximum since nobody has a first name with over 200 characters instead use VARCHAR(50)

Example of Query Optimization

Instead of

SELECT *
FROM employee
LEFT JOIN  department ON employee.department_id =  department.department_id
WHERE UPPER(employee.last_name) = 'SMITH'
ORDER BY employeeage DESC;

Optimizing the SQL Query above

• Specify the columns you need instead of selecting all columns
• Avoid using LEFT Join since no employee that can be without a department instead use INNER JOIN
• Avoid functions on indexed column i.e UPPER on last_name
• instead index customer_id `

Conclusion

By following these best practices in large databases, SQL optimization can greatly improve performance of your application