Почему 1000 запросов в БД выполняются быстрее чем 1?

Question

[khodos_dmitry]

Оптимизировал производительность одного сайта. Думал, что объединение тысяч запросов в один даст прирост производительности. Оказалось нет. Это повысило нагрузку на базу более чем в 10 раз.
Получается, что 1000 простых запросов выполняется быстрее, чем одни сложный?
Это нормально? Так должно быть?
Примеры запросов:
Было (на эту страницу 80% траффика):

$ex = explode(",",$row['id_categories']);
foreach ($ex as $x)
{
	$res2 = $db->query("SELECT * FROM categories WHERE id='$x'");
	$row2 = $res2->fetch_array();	
}

Стало:

$categories = $db->query("SELECT `url_category`, `rus_category` FROM categories WHERE id IN ({$row['id_categories']})")->fetch_all(MYSQLI_ASSOC);

Было (таких страниц 10-ки, они забирают остальные 20% трафика ):

$res = $db->query("SELECT * FROM products
						LEFT JOIN streets ON streets.id=products.id_street
						WHERE products.id='$p'");
	$row = $res->fetch_array();
	if ($row[14] != 0) $x += $row[14];
	if ($row[15] != 0) $y += $row[15];

	$ex = explode(",",$row['id_categories']);
	$z = array();
	foreach ($ex as $o)
	{
		$res2 = $db->query("SELECT * FROM categories WHERE id='$o'");
		$row2 = $res2->fetch_array();	
		array_push($z,$row2[1]);
	}	
	$cats = implode(", ",$z);
	
	$rating = 0;
	$w = 0;
	$star_block = "";
	$resx = $db->query("SELECT * FROM products_reviews WHERE type='$row[url_product]'");
}

Стало:

$query = "SELECT products.url_product, 
products.rus_product,
products.home,
products.phone,
products.cx,
products.cy,
streets.rus_street, 
streets.url_street, 
(SELECT GROUP_CONCAT(CONCAT('<a href=\"/$url_punkt/', `url_category`, '/\">', `rus_category`, '</a>')) FROM `categories` WHERE `id` IN (SELECT `category_id` FROM `category_product` WHERE `product_id` = `products`.`id`) GROUP BY '') AS categories, 
(SELECT AVG(`rating`) FROM products_reviews WHERE type = products.url_product) as rating
FROM products 
LEFT JOIN streets ON streets.id=products.id_street
WHERE products.id_punkt = $id_punkt 
AND `products`.`id` IN (SELECT `product_id` FROM `category_product` WHERE `category_id` = $id_category)";
$products = $db->query($query)->fetch_all(MYSQLI_ASSOC);

Comments

[Илья]

Ну вы хоть бы запросы показали. Не говоря уже о структуре таблиц, индексах

[Иван Иванов]

1000 запросов ты сделал или поиск одной строки из 1000-чи по лимиту в придачу

одним запросом

[Иван Иванов]

Илья,

индексах

+

[khodos_dmitry]

Добавил примеры кода

[vism]

khodos_dmitry, индексы стоят на
category_product.product_id
category_product.category_id
products.id_street
?
поля поиска тоже влияют, но сравнительно не должно влиять так.

[khodos_dmitry]

vism, стоят индексы.

[vism]

khodos_dmitry, а вобще если это прям супер частый запрос, то не помешало бы в индекс добавить и
products.id_punkt
products_reviews.type

[vism]

khodos_dmitry, тогда прочтите об EXPLAIN и делайте его.
Сходу не вижу особых проблем в запросе.
Этот запрос в стандартно настроенной системе должен работать намного быстрее, чем то, что было до этого
Ну и сам запрос не плох, если закрыть глаза на безопасность.

Может дело вобще кстати в пером запросе, который 80% генерит, щас гляну

[vism]

$categories = $db->query("SELECT `url_category`, `rus_category` FROM categories WHERE id IN ({$row['id_categories']})")->fetch_all(MYSQLI_ASSOC);

а откуда тут $row['id_categories'] если у вас категории уже в отдельной таблице.

[khodos_dmitry]

Я не все запросы привёл. Там 10-ки разных страниц с разными запросами. Сейчас посетила что в category_product нужно поставить индекс на каждое поле. Я поставил только primary на два поля.

[khodos_dmitry]

vism, я не удалял пока то поле. Сейчас нормализация и то поле осталось.

[vism]

khodos_dmitry, да там надо отдельный KEY индекс на каждое поле

Но вобще меня первый, где id_categories смущает.
Там тоже можно одним запросом сделать через Sub select или exist

[khodos_dmitry]

vism, я сделаю. Забыл просто)

[khodos_dmitry]

vism, с безопасностью там кстати все норм. Всё данные экранируюся.

[vism]

khodos_dmitry, вы ток напишите, помогло или нет, т.к. более ничего криминального не видно. Пишут, мол дело в кэше, но я очень сомневаюсь. Даже если бы раньше кэш работал, нагрузка на PHP была огромная, на все эти переборы.
Новые запросы с проставленными должны работать очень быстро. Может дело не в них ну или через EXPLAIN посмотреть эти запросы.

[khodos_dmitry]

vism, хорошо, постараюсь разобраться. Главное я понял, что такого быть не должно.

[khodos_dmitry]

vism,
действительно дело было в не поставленном индексе))

[vism]

khodos_dmitry, норм :)

Answer 1

[khodos_dmitry]

Дело было в не поставленных индексах на несколько полей.

Answer 2

[Rsa97]

SELECT `p`.`url_product`, `p`.`rus_product`, `p`.`home`, `p`.`phone`,
       `p`.`cx`, `p`.`cy`, `s`.`rus_street`, `s`.`url_street`,
       `c`.`categories`, `r`.`rating`
  FROM `products` AS `p`
  LEFT JOIN `streets` ON `s`.`id` = `p`.`id_street`
  LEFT JOIN (
    SELECT `p`.`id` AS `product_id`, GROUP_CONCAT(CONCAT('<a href=\"/$url_punkt/', `c`.`url_category`, '/\">', `rus_category`, '</a>')) AS `categories`
      FROM `products` AS `p`
      JOIN `categories` AS `c`  ON `c`.`product_id` = `p`.`id`
      WHERE `p`.`id_punkt` = :id_punkt
        AND `p`.`id` IN (
          SELECT `product_id`
            FROM `category_product`
            WHERE `category_id` = :id_category
        )
      GROUP BY `p`.`id`
  ) AS `c` ON `c`.`product_id` = `p`.`id`
  LEFT JOIN (
    SELECT `p`.`url_product` AS `url_product`, AVG(`pr`.`rating`) AS `rating`
      FROM `products` AS `p`
      JOIN `products_reviews` AS `pr`
        ON `pr`.`type` = `p`.`url_product`
      WHERE `p`.`id_punkt` = :id_punkt
        AND `p`.`id` IN (
          SELECT `product_id`
            FROM `category_product`
            WHERE `category_id` = :id_category
        ) 
      GROUP BY `p`.`url_product`
  ) AS `r` ON `r`.`url_product` = `p`.`url_product`
  WHERE `p`.`id_punkt` = :id_punkt 
    AND `p`.`id` IN (
      SELECT `product_id`
        FROM `category_product`
        WHERE `category_id` = :id_category
    )

Comments

[neol]

Вот честное слово в реально проекте я бы в 99% случаев предпочёл десяток примитивных запросов, которые выполняются 500 раз, чем один такой.

[khodos_dmitry]

Мощный запрос. Но чем он лучше, чем мой?
Мой проще для понимания.

[vism]

khodos_dmitry, это тот самый из 95%, думает если сделать всё таблицами и заджойнить, то будет быстрее.
Он же еще и писал уж никак не 5 минут, а больше )))

Answer 3

[Олег]

mysql query cache
В первом случае у Вас запросы вынимались из него.
во втором случае, запрос получается каждый раз разный (зависит от товаров и их порядка на странице)

Comments

[khodos_dmitry]

Может ли быть кэш, если там постоянно новые данные подставляются?
Допустим, в запросе:
SELECT * FROM categories WHERE id='$x'
переменная $x .
И так в каждом запросе.

[Олег]

У муськи может быть настроен внутренний кеш запросов.
Если текст запроса один в один и она видит, что в таблицах не было изменений, то данные отдаются из оперативной памяти. Это в упрощении для понимания.
Очень грубо тайминги
свыше 100 мс - был фулскан по таблице
1 - 10 мс - выбрано по индексу или ключу
менее 1 мс - вынут из кеша результат предыдущего выполнения