Как оптимизировать left join запрос?

Question

[stainer]

Имеется запрос, который выполняется очень долго из-за большого объема данных + JOIN`ов. Помогите пожалуйста его максимально оптимизировать с помощью временных таблиц, либо есть какие либо другие варианты?

SELECT distinct cd1.id as id,cd1.art as art,cd1.name as name,cd1.price_in as price_in,cd1.date_in as date_in,cd1.price_plan as price_plan,cd1.c_count as plus,cd2.c_count as minus,cd1.c_count-cd2.c_count as remain 
FROM cash_docs AS cd1 
LEFT JOIN (SELECT p_id, sum(c_count) as c_count FROM cash_docs WHERE c_show=1 AND doc_type=3 group by p_id) AS cd2 ON cd1.id=cd2.p_id
LEFT JOIN p_bikes_attrs AS cd3 ON cd1.art=cd3.art
WHERE cd1.c_show=1 AND cd1.doc_type=1
AND (cd1.c_count-cd2.c_count is null OR cd1.c_count-cd2.c_count > 0)
ORDER BY name

Comments

[stainer]

Лентюй, вот

[stainer]

Честно говоря, у меня не большие знания по sql, но вкратце:
Есть 2 таблицы: cash_docs - куда добавляются операции закупки и продажи товара (бухгалтерская) и p_bikes_attrs где хранится информация об артикулах товаров.
Этим запросом (насколько я понял) выбираются все товары на складе (где есть документ закупка, но нет продажи) + их артикул подцепляется из таблицы p_bikes_attrs.

Больше всего времени тратится на выполнение этого куска запроса:
LEFT JOIN p_bikes_attrs AS cd3 ON cd1.art=cd3.art

[stainer]

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

[Rsa97]

stainer, Как минимум, не хватает индекса по `p_bikes_attrs`.`art`.
Затем, использовано неиндексируемое выражение (cd1.c_count-cd2.c_count is null OR cd1.c_count-cd2.c_count > 0). Его тоже лучше преобразовать так, чтобы в левой части условий не было выражений. Можно попробовать вариант `cd1`.`c_count` > IFNULL(`cd2`.`c_count`, 0)

Answer 1

[Сергей П]

Данные из p_bikes_attrs в вашем запросе не используются, так что можете этот джойн выкинуть.
Нифига не понятно что у вас там задумывалось, но похоже на дерево.
Вложенный запрос выдаёт много записей, но условие джойна отсекает все, кроме одного. Это бессмысленно и неэффективно. Лучше разместить подзапрос в SELECT.
А чтобы отфильтровать по вашему условию, как я понял различающему ситуации отсутствия определённого вида потомков и определённого соотношения минуса и плюса...
Короче, вот. Писал наощупь, пробуйте

WITH unfiltered AS (
  SELECT 
    distinct 
    cd1.id          AS id,
    cd1.art         AS art,
    cd1.name        AS name,
    cd1.price_in    AS price_in,
    cd1.date_in     AS date_in,
    cd1.price_plan  AS price_plan,
    cd1.c_count     AS plus,
    (
      SELECT sum(c_count)
      FROM cash_docs
      WHERE c_show=1 AND doc_type=3 AND p_id = cd1.id
    ) AS minus
    --,cd2.c_count AS minus,
    --,cd1.c_count - cd2.c_count AS remain 
  FROM cash_docs AS cd1 
  WHERE cd1.c_show=1 AND cd1.doc_type=1
  --  AND (
  --    cd1.c_count - cd2.c_count is null
  --    OR cd1.c_count - cd2.c_count > 0
  --  )
  ORDER BY name
)

SELECT *, plus - minus AS remain
FROM unfiltered
WHERE minus is null OR plus - minus > 0

И форматируйте, пожалуйста, впредь запросы. Отформатированные они гораздо легче читаются и тем кто вам решит помочь не придётся тратить время еще и на форматирование.
А-то как-то странно. Вам, значит, лень отформатировать, а нам -- помогай и копайся в этой каше...