@linux2000

Тормозит mysql запрос

Есть mysql запрос:

SELECT p.`id`, p.`name`, p.`category_id`, v.`name` AS vendor, p.`price_ya`, pp.`value` AS price, pp.`fix_currency_id`, (SELECT ph.`id` FROM `products_photos` AS ph WHERE ph.`product_id`=p.`id` ORDER BY ph.`pos` ASC LIMIT 1) AS photo, px.`status`
FROM `products_prices` AS pp, `xml_products` AS px, `products` AS p LEFT JOIN `vendors` AS v ON p.`vendor_id`=v.`id`
WHERE pp.`price_id`='2' AND p.`id`=pp.`product_id` AND p.`id`=px.`product_id` AND px.`site_id`='2'

При запуске его через phpmyadmin он выполняется мигом. При запуске на виртуальном сервере из скрипта тоже работает шустро. Но купив пару дней назад выделенный сервер, этот запрос в скрипте который на виртуалке выполнялся в лёт, здесь грузит MySQL сервер на 100% и выполняется по 50 и более секунд.

EXPLAIN запроса такой:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY px ALL PRIMARY NULL NULL NULL 4660 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 xpert_cp.px.product_id 1
1 PRIMARY v eq_ref PRIMARY PRIMARY 3 xpert_cp.p.vendor_id 1
1 PRIMARY pp eq_ref PRIMARY PRIMARY 6 xpert_cp.p.id,const 1 Using where
2 DEPENDENT SUBQUERY ph index product_id pos 1 NULL 1 Using where
  • Вопрос задан
  • 3383 просмотра
Пригласить эксперта
Ответы на вопрос 6
@pihel
Sql, Oracle, pl/sql, BI, ETL, php, olap
А без подзапроса не тормозит? Вот мне кажется в нем причина, для каждой выбранной записи приходится делать перебор другой таблицы.
Ответ написан
Комментировать
no1
@no1
Избегайте сложных запросов. По логике у 1 продукта 1 фото и 1 цена, значит информацию о первом фото проще хранить в описании продукта и обновлять её при обновлении фото, так же как и информацию о цене.

В идеале по id продукта вы должны получать всю необходимую информацию.
Ответ написан
Комментировать
dxArtem
@dxArtem
Я так понимаю под запрос сделан, чтоб вытащить первую фотку? Дело в том что ORDER BY всегда делает tmp таблицы для сортировки, хотя в EXPLAIN я в данном случае не вижу.

Попробуйте так:
SELECT 
  p.`id`,
  p.`name`,
  p.`category_id`,
  v.`name` AS vendor,
  p.`price_ya`,
  pp.`value` AS price,
  pp.`fix_currency_id`,
  ph.`id` AS photo,
  px.`status` 
FROM
  `products_prices` AS pp,
  `xml_products` AS px,
  `products` AS p 
  LEFT JOIN `vendors` AS v 
    ON p.`vendor_id` = v.`id` 
  LEFT JOIN `products_photos` ph
    ON ph.`product_id` = p.`id`
WHERE pp.`price_id` = '2' 
  AND p.`id` = pp.`product_id` 
  AND p.`id` = px.`product_id` 
  AND px.`site_id` = '2' 
GROUP BY p.`id`

Тут GROUP By сгрупирует, а в left join выйдет первый id, если не ошибаюсь. Сложно без базы анализировать.

Еще нужно учитывать версии mysql, в каждой версии используются по разному индексы, который я кстати вообще не вижу, везде using where.
Ответ написан
FreeTibet
@FreeTibet
dharma supplier
а попробуйте так, пишу без проверки могут быть ошибки (но смысл надеюсь понятен):

SELECT 
  p.`id`, 
  p.`name`, 
  p.`category_id`, 
  v.`name` AS vendor, 
  p.`price_ya`, 
  pp.`value` AS price, 
  pp.`fix_currency_id`,
  ph.`id` AS `photo`,
  px.`status`
FROM 
  `products_prices` AS pp, 
  `xml_products` AS px
LEFT JOIN (
  SELECT 
    sph.`product_id`, 
    MIN(sph.`pos`) AS min_pos
  FROM `products_photos` AS sph
  GROUP BY sph.`product_id`
) AS tph
  ON tph.`product_id` = p.`id`
LEFT JOIN `products_photos` AS ph 
  ON ph.`product_id` = tph .`product_id` 
  AND ph.`pos` = tph.`min_pos`
LEFT JOIN `vendors` AS v 
  ON p.`vendor_id`=v.`id` 
WHERE 
  pp.`price_id`='2' 
  AND p.`id`=pp.`product_id` 
  AND p.`id`=px.`product_id` 
  AND px.`site_id`='2'


P.S. подразумевается что связка product_id+pos уникальна.
Ответ написан
@tnz
На сколько я помню, в случае зависимого подзапроса он будет выполняться каждый раз на каждую найденную запись основного запроса. Основной тоже тяжелый, mysql почему-то решил начинать разбор с таблицы px, видимо считает, что по ней получится самый эффективный фильтр, но непонятно тогда, почему предлагает использовать праймари индекс (кстати, какой он?), но не использует — недостаточная фильтрация?
Предлагаю попробовать избавиться от подзапроса в таком виде, провести analyze таблиц и посмотреть что выдаст explain в этом случае. Посмотрите сколько фактически записей остается по каждой таблице отдельно после применения нужных фильтров. Возможно оптимизатор ошибается в порядке выполнения запросов и нужно ему прямо указать в какой последовательности это нужно делать. Для меня тут загадка, почему он все-таки начинает с px и пробегает ее всю — примерно 4660 записей. В других таблицах больше что ли после фильтров?

Посмотрите параметры сервера, прогоните какой-нить утилиткой-тюнером, например mysqltuner.pl, поправьте всякие буферы. Еще возможно, что у вас до этого был сервер с нагретым кешем, а на инстансе чистый как стекло.
Ответ написан
Комментировать
AxisPod
@AxisPod
А хранилище InnoDB и у сервера мало памяти?
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы