How to optimize left join query?

Question

MySQL

There is a query that takes a very long time due to a large amount of data + JOINs. Please help to optimize it as much as possible using temporary tables, or are there any other options?

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

Answer 1

The data from p_bikes_attrs is not used in your request, so you can throw out this join.
Nifiga is not clear what you have in mind there, but it looks like a tree.
The nested query produces many records, but the join condition cuts off all but one. It's pointless and inefficient. It is better to place a subquery in SELECT.
And in order to filter according to your condition, as I understand it, distinguishing situations of the absence of a certain type of descendants and a certain ratio of minus and plus ...
In short, here. I wrote by touch, try

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

And format, please, henceforth requests. Formatted, they are much easier to read and those who decide to help you will not have to spend time on formatting.
And somehow strange. It means that you are too lazy to format it, but for us - help and delve into this mess ...