Is faster one big SQL query or multiple small SQL queries ?

I need to optimize the SQL queries to make them faster. Is it better to run one big query or more small queries ?

Hi,

It depends on the specific case, whether is faster one big query or many small queries. More SQLs means more requests to the database, so for example when your query is based on one condition, it is better to use one bigger query:

SELECT product_id, product_name FROM products WHERE product_category IN (1,2,3);
than multiple small queries instead:

SELECT product_id, product_name FROM products WHERE product_category = 1;
SELECT product_id, product_name FROM products WHERE product_category = 2;
SELECT product_id, product_name FROM products WHERE product_category = 3;

But sometimes it can be better to have more queries. You can also try to speed up the existing queries, for example by using one query, which is reading the data from more tables with functions LEFT JOIN, RIGHT JOIN or INNER JOIN, instead of using multiple separated / nested queries, where each query is reading the data from only one table:

SELECT product_id, product_name FROM products AS p LEFT JOIN categories AS c ON p.product_category = c.category_id WHERE c.category_promotion = 1;

To speed up the SQL query, you have to use only necessary columns, for example:

SELECT product_id, product_name FROM products WHERE product_price < 100;
instead of reading all columns:

SELECT * FROM products WHERE product_price < 100;

In case of more complex operations, to speed up the processing, it can be good solution to create and use temporary tables with relevant data. Also the nested queries can be faster, than multiple separated queries:

INSERT INTO temporary_products (product_id, product_name, product_description, product_price, product_category, product_quantity, product_status) SELECT product_id, product_name, product_description, product_price, product_category, product_quantity, product_status FROM products WHERE product_category = 3;