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
Top comments (0)