Hello, today I want to talk about MySQL. This discussion requires some understanding of MySQL. Let’s discuss the optimization of NESTED SELECT statements.
Consider this MySQL statement:
SELECT name, (SELECT count(carts.product_id) FROM carts WHERE carts.product_id = products.id AND cart.date=’2015-01-01’) as count FROM products;
I have a table for orders and carts and I want to find how many of each product were sold on specific date. So far, the work is pretty simple.
The SELECT statement will run and find a product_name, then it will run the NESTED SELECT statement to count the amount of this product sold on a specific date. This means the NESTED SELECT statement will run once for each product I have. If I have a lot of products and a lot of these products were sold, the SELECT statement becomes very expensive.
Let’s now modify the statement a little:
SELECT products.name, temp.count FROM (SELECT carts.product_id, count(cart.product_id) as count FROM carts WHERE carts.date = ’2015-01-01’ GROUP BY carts.product_id) as temp INNER JOIN products ON(products.id = temp.product_id);
This SELECT statement is a little more complex, but it does the same thing as the one above.
This statement selects product name and count. Column product name is from the products table. Column count is extracted from a Virtual table, which is created with a select statement. This virtual table is created once and saved as a temporary table, containing all of the product ids and product counts. After the temp table is created, the statement joins the carts table to get the name of the product.
This statement does not have a recurring SELECT statement, which, in the first statement, ran for every row. With this new statement, we greatly reduce the resources required, which is great when working in a large database.
The use of Virtual tables is an efficient way to decrease the quantity of used resources, especially if the work requires increasing the date range to a whole year, or a decade.
Thank you very much for reading.
Have a fantastic day.
