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

Question

[linux2000]

Есть 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

Comments

[EugeneOZ]

Не микро-инстанс ли амазона?

Answer 1

[Алексей Скахин]

А без подзапроса не тормозит? Вот мне кажется в нем причина, для каждой выбранной записи приходится делать перебор другой таблицы.

Answer 2

[no1]

Избегайте сложных запросов. По логике у 1 продукта 1 фото и 1 цена, значит информацию о первом фото проще хранить в описании продукта и обновлять её при обновлении фото, так же как и информацию о цене.

В идеале по id продукта вы должны получать всю необходимую информацию.

Answer 3

[Ivan Komarov]

а попробуйте так, пишу без проверки могут быть ошибки (но смысл надеюсь понятен):

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 уникальна.

Comments

[tnz]

Вы, видимо, пропустили products p указать во FROM

Answer 4

[tnz]

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

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

Answer 5

[AxisPod]

А хранилище InnoDB и у сервера мало памяти?