Тормозит 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

[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.

Comments

[tnz]

Вроде бы ORDER BY не всегда делает сортировку через временную таблицу, например для случая, когда данные выбираются уже в отсортированном порядке или в порядке отсортированного индекса.
На счет индексов — так используются же. Везде PRIMARY, кроме зависимого подзапроса и основной таблицы px.

[tnz]

а касательно using where так это потому, что не все условия покрываются индексом. Приходится еще вручную дофильтровывать. Хотя не буду утверждать, конечно. Судя по таблице pp у нее в праймари индексе product_id и price_id, но все равно не ясно, зачем он using where дописывает.

Answer 4

[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 5

[tnz]

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

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

Answer 6

[AxisPod]

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