How to avoid SQL queries inside loops
In this article I will try to show how to avoid one of the signs of the "decaying" code, namely SQL queries inside loops. The examples will be in plain PHP without using OOP. This will greatly facilitate understanding. Reading will take from 5 to 10 minutes.
Everything is very simple. Each request is a loss of time for "preparatory" and "final" operations for accessing the database. Suppose for each product there is a separate table for pictures, discounts and some options (for example, the choice of the color of the dress). That is, to obtain information about each product, you will need to fulfill 3 requests. Those. 300 requests for withdrawal of 100 products + request for a list of products. Total 301 requests for 100 products. And as a result, a significant decrease in the performance of your application. This can and should be avoided.
Below is a piece of code from OpenCart 3:
Consider a problem with two tables - Products and Prices. One product may have several prices of different types. The result must be obtained as an array:
')
Variant with queries inside loops:
So, in order to display 7 products we made 1 + 7 requests. How to optimize:
The same, but in plain language PHP:
So now, to select any number of products, you will need to complete only 2 requests. The work of such a script will have a tangible difference on samples of a large number of goods. This approach can be generalized. For example, avoid cycle access to external resources (file system, memcache, redis), if this is possible. And remember the principle of rationality in the decisions made.
Why should you avoid queries inside loops?
Everything is very simple. Each request is a loss of time for "preparatory" and "final" operations for accessing the database. Suppose for each product there is a separate table for pictures, discounts and some options (for example, the choice of the color of the dress). That is, to obtain information about each product, you will need to fulfill 3 requests. Those. 300 requests for withdrawal of 100 products + request for a list of products. Total 301 requests for 100 products. And as a result, a significant decrease in the performance of your application. This can and should be avoided.
Below is a piece of code from OpenCart 3:
public function getProducts($data = array()) { //..... $query = $this->db->query($sql); foreach ($query->rows as $result) { // for never get one more time with same product id if(!isset($product_data[$result['product_id']])){ $product_data[$result['product_id']] = $this->getProduct($result['product_id']); } } return $product_data; } /* $this->getProduct($result['product_id']) 1 . .. getProducts product_id- , "" . (). */ Task for optimization
Consider a problem with two tables - Products and Prices. One product may have several prices of different types. The result must be obtained as an array:
')
print_r($products) /* Array ( [1] => Array ( [product_id] => 1 [name] => 1 [prices] => Array ( [0] => Array ( [price_id] => 45 [product_id] => 1 [type] => 3 [price] => 95.00 ) [1] => Array ( [price_id] => 55 [product_id] => 1 [type] => 1 [price] => 90.00 ) [2] => Array ( [price_id] => 58 [product_id] => 1 [type] => 2 [price] => 90.00 ) ) ) ) */ Product table:
CREATE TABLE `product` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; INSERT INTO `product` (`product_id`, `name`) VALUES (1, ' 1'); INSERT INTO `product` (`product_id`, `name`) VALUES (2, ' 2'); INSERT INTO `product` (`product_id`, `name`) VALUES (3, ' 3'); INSERT INTO `product` (`product_id`, `name`) VALUES (4, ' 4'); INSERT INTO `product` (`product_id`, `name`) VALUES (5, ' 5'); INSERT INTO `product` (`product_id`, `name`) VALUES (6, ' 6'); INSERT INTO `product` (`product_id`, `name`) VALUES (7, ' 7'); INSERT INTO `product` (`product_id`, `name`) VALUES (8, ' 8'); INSERT INTO `product` (`product_id`, `name`) VALUES (9, ' 9'); INSERT INTO `product` (`product_id`, `name`) VALUES (10, ' 10'); Price Table:
CREATE TABLE `product_price` ( `price_id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL, `type` enum('1','2','3') NOT NULL, `price` decimal(10,2) NOT NULL, PRIMARY KEY (`price_id`) ) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8; INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (55, 1, '1', 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (58, 1, '2', 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (45, 1, '3', 95.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (56, 2, '1', 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (49, 2, '2', 45.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (42, 2, '3', 96.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (57, 3, '1', 23.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (47, 3, '2', 53.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (51, 3, '3', 12.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (43, 4, '1', 89.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (46, 4, '2', 4.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (52, 4, '3', 15.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (54, 5, '1', 43.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (41, 5, '2', 44.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (48, 5, '3', 34.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (44, 6, '1', 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (59, 6, '2', 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (60, 6, '3', 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (53, 7, '1', 87.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (61, 7, '2', 87.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (50, 7, '3', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (62, 8, '1', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (63, 8, '2', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (64, 8, '3', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (65, 9, '1', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (66, 9, '2', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (67, 9, '3', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (68, 10, '1', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (69, 10, '2', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (70, 10, '3', 77.00); Variant with queries inside loops:
<?php define('DB_HOST' , 'localhost'); define('DB_USER' , 'mysqluser'); define('DB_PASSWORD' , 'password'); define('DB_NAME' , 'habr'); $conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die('Can not connect to db'); mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn)); mysqli_query($conn , 'SET NAMES utf8'); // - 7 . $sql = 'select * from product'; $product_result = mysqli_query($conn , $sql); // $products = array(); while ($product = mysqli_fetch_assoc($product_result)) { // . $prices = array(); $sql = 'select * from product_price WHERE product_id = ' . (int) $product['product_id']; $price_result = mysqli_query($conn , $sql); while ($price = mysqli_fetch_assoc($price_result)) { $prices[] = $price; } // $product['prices'] = $prices; // $products[] = $product; } print_r($products); if ($conn) { mysqli_close($conn); } So, in order to display 7 products we made 1 + 7 requests. How to optimize:
- Choose products from the table. We place them into an array, but we will use product_id as keys;
- Choose all prices for the items found;
- Found prices "we shove" on the goods.
The same, but in plain language PHP:
<?php define('DB_HOST' , 'localhost'); define('DB_USER' , 'mysqluser'); define('DB_PASSWORD' , 'password'); define('DB_NAME' , 'habr'); $conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die('Can not connect to db'); mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn)); mysqli_query($conn , 'SET NAMES utf8'); // 3 $sql = 'select * from product LIMIT 3'; $product_result = mysqli_query($conn , $sql); // $products = array(); while ($product = mysqli_fetch_assoc($product_result)) { // $product['prices'] = array(); // . product_id $products[(int) $product['product_id']] = $product; } // , . if (count($products) > 0 ) { // product_id- . $product_ids = array_keys($products); // $sql = 'select * from product_price where product_id in (' . implode(',' , $product_ids ). ')'; $prices_result = mysqli_query($conn , $sql) or die(mysqli_error($conn)); while ($price = mysqli_fetch_assoc($prices_result)) { $products[(int) $price['product_id']]['prices'][] = $price; } } print_r($products); if ($conn) { mysqli_close($conn); } So now, to select any number of products, you will need to complete only 2 requests. The work of such a script will have a tangible difference on samples of a large number of goods. This approach can be generalized. For example, avoid cycle access to external resources (file system, memcache, redis), if this is possible. And remember the principle of rationality in the decisions made.
Source: https://habr.com/ru/post/350468/
All Articles