Как увеличить скорость выполнения SQL запроса?

Question

[coderisimo]

имеется жуткий запрос :

SELECT
  p.*,
  product_shop.*,
  stock.out_of_stock,
  IFNULL(stock.quantity, 0)                                               AS quantity,
  MAX(product_attribute_shop.id_product_attribute)                        AS id_product_attribute,
  product_attribute_shop.minimal_quantity                                 AS product_attribute_minimal_quantity,
  pl.`description`,
  pl.`description_short`,
  pl.`available_now`,
  pl.video,
  pl.`available_later`,
  pl.`link_rewrite`,
  pl.`meta_description`,
  pl.`meta_keywords`,
  pl.`meta_title`,
  pl.`name`,
  MAX(image_shop.`id_image`)                                              AS id_image,
  il.`legend`,
  m.`name`                                                                AS manufacturer_name,
  cl.`name`                                                               AS category_default,
  DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 75 DAY)) > 0 AS new,
  product_shop.price                                                      AS orderprice

FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`
  INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
  LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product`)
  LEFT JOIN ps_product_attribute_shop product_attribute_shop
    ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND
        product_attribute_shop.`default_on` = 1)
  LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(
      `product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1)
  LEFT JOIN `ps_category_lang` cl
    ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1)
  LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1 AND pl.id_shop = 1)
  LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`)
  LEFT JOIN ps_image_shop image_shop
    ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover = 1)
  LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1)
  LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = 1 AND cp.`id_category` = 25 AND product_shop.`active` = 1 AND
      product_shop.`visibility` IN ("both", "catalog")
GROUP BY product_shop.id_product
ORDER BY `sale` DESC
LIMIT 0, 100;

план выполнения :

вот ссылка на большую картинку : https://dl.dropboxusercontent.com/u/19954007/asks/...

видно, что самый "тяжелый" - первый (Using temporary; Using filesort). Но уже там используется PRIMARY.
Можно ли как то все ускорить . Сейчас время выполнения 0.9253 сек.
куда копать.
Спасибо

ЗЫ Структура таблиц вот здесь - https://dl.dropboxusercontent.com/u/19954007/asks/...

Answer 1

[sim3x]

Сюда копай dev.mysql.com/doc/refman/5.7/en/explain.html

добавить индекс на
ps_category_product.id_category

Добавить индекс на
ps_product_shop.`active`
ps_product_shop.`visibility`

потом убрать и сравнить с индексом по (
product_shop.`id_shop` = 1 AND cp.`id_category` = 25 AND product_shop.`active` = 1 AND
product_shop.`visibility` IN ("both", "catalog"))

Comments

[coderisimo]

Ну дык , уже копаю. на картинке план из explain.

[sim3x]

coderisimo:
вместо скринов - сделай копипаст из консоли
добавь структуру таблиц из запроса

[coderisimo]

sim3x: здешний редактор ругает на размерчик.
не запрос , а просто монстр какой-то!
https://dl.dropboxusercontent.com/u/19954007/asks/...
EXPLAIN в простом тексте весьма коряв, хотя я его добавил. вот https://dl.dropboxusercontent.com/u/19954007/asks/... большой скрин.

[coderisimo]

я даже в более простом запросе в explain первой строкой вижу :

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort

Using temporary; Using filesort - ведь не гуд. хотя используется PRIMARY ....

[sim3x]

coderisimo: праймари у тебя будет использоваться если в запросе одновременно будут присутствовать id_product и id_shop

[coderisimo]

Эт я уже про другой запрос. тот слишком огромный для моих небольших мозгов. Вот эксплейн пишет в первой строчке:

1 SIMPLE c const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort

сам запрос-
SELECT p.stars, COUNT(DISTINCT p.id_product) nbr , m.name, psi.price_min, psi.price_max, avb.name as avbname FROM ps_product p INNER JOIN ps_product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
INNER JOIN ps_category_product cp ON (cp.id_product = p.id_product)
INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
c.id_category = 759
AND c.active = 1) LEFT JOIN `ps_stock_available` sa
ON (sa.id_product = p.id_product AND sa.id_shop = 1) LEFT JOIN `ps_manufacturer` m ON (m.id_manufacturer = p.id_manufacturer)
INNER JOIN `ps_layered_price_index` psi
ON (psi.id_product = p.id_product AND psi.id_currency = 1 AND psi.id_shop=1) LEFT JOIN `ps_availability` avb ON (avb.id_availability = p.id_availability)
WHERE p.`active` = 1 AND p.`visibility` IN ("both", "catalog")
GROUP BY p.stars ORDER BY p.stars DESC;

таблица :

CREATE TABLE IF NOT EXISTS `ps_category_product` (
`id_category` int(10) unsigned NOT NULL,
`id_product` int(10) unsigned NOT NULL,
`position` int(10) unsigned NOT NULL DEFAULT '0',
KEY `id_product` (`id_product`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

вот я и не понимаю. эта "с" она же ps_category_product . в ней всего 1600 записей, в ней есть первичный ключ и он используется. Так ПОЧЕМУ я имею для этого простого запроса "Using temporary; Using filesort"

[Walt Disney]

coderisimo: "Using temporary; Using filesort"
потому что для операций в которых больше половины индекса файлсорт обычно будет дешевле чем юзинг индекс

Answer 2

[Walt Disney]

Найдите способ добавить в WHERE условие которое ограничит множество product_shop.id_product тем которое сейчас входит в LIMIT.

Т.е. этот запрос валят строки
ORDER BY `sale` DESC
LIMIT 0, 100;
над ними и парьтесь.

Comments

[coderisimo]

Спасибо за ответ.
Задачка была декабрьская и уже не актуальна