Optimize SQL query to make it more faster - SQL query optimization

How can I optimize SQL queries to make them run more faster ? What are the best methods for SQL query optimization ?

Hi,

There are some general SQL query optimization tips, that make SQL queries run faster. Some optimization methods can speed up SQL queries a lot, other can give a minor improvements. To make your SQL queries faster, you can try the following tips:

1. Use only necessary columns:

SELECT user_name, user_email FROM users WHERE user_active = 1 ;
instead of using * to read all the columns

SELECT * FROM users WHERE user_active = 1 ;
The less data processed, the faster the query will run.

2. When working with large tables and doing some complex operations, consider to use temporary tables:

CREATE temporary_table_logs (
log_id INT(11) AUTO_INCREMENT,
log_user INT(11),
log_structure INT(11),
log_action INT(11),
log_date DATETIME );

INSERT INTO temporary_table_logs (log_user, log_structure, log_action, log_date) SELECT log_user, log_structure, log_action, log_date FROM logs WHERE log_user = 15 AND log_structure IN (6,7,8) AND log_action IN (2,3);
3. When working with mutliple tables and you don't expect too many results, use joins like LEFT JOIN, RIGHT JOIN or INNER JOIN instead of using separated queries.

SELECT user_name FROM users LEFT JOIN orders ON users.user_id = orders.user_id WHERE order_id = 1473;
4. When working with multiple related statements, consider whether to use nested query (subquery) or separated queries. Nested query may be faster in some cases. But sometimes when reading many rows with SELECT in nested query, you can get better overall performance with separated queries.

SELECT u.user_email, u.user_points, u.user_status FROM users AS u WHERE user_id IN (SELECT DISTINCT(o.user_id) FROM orders AS o WHERE o.order_amonut >= 50 AND o.order_delivery IN (1,2) AND o.order_date like '2021%' ) AND u.user_points > 0 AND u.user_active = 1;
If there are too many results from "orders" table, it can be more efficient to use separated queries.

5. Reduce the number of the same queries or queries with the same results if possible. Using of the same queries can occur for example in loops FOR, WHILE or FOREACH.

6. Use LIMIT when you don't need to get all the results.

7. Use EXISTS instead of COUNT if possible.

8. Use WHERE instead of HAVING if possible.

9. Reduce the number of IN conditions in query if possible.

10. Check if you are using single or double quotes in a correct way.

The correct way of using SQL related quotes can make overall processing a little faster.

Sometimes you can get faster SQL query execution, when you change the table engine. Depending on used SQL statements, it may be better to change table engine from MyISAM to InnoDB or from InnoDB to MyISAM.