SELECT D2_.distributor_id AS col_0_0_,
D2_.name AS col_1_0_,
L3_.registration_id AS col_2_0_,
L3_.name AS col_3_0_,
product0_.ean AS col_4_0_,
S4_.duration AS col_5_0_,
product0_.mban AS col_6_0_,
product0_.title2 AS col_7_0_,
SUM(S1_.amount) AS col_8_0_,
SUM(S1_.amount_corrected) AS col_9_0_
FROM Q_P product0_
CROSS JOIN Q_S S1_
CROSS JOIN Q_D D2_
CROSS JOIN Q_L L3_
CROSS JOIN Q_S2 S4_
WHERE S1_.specification_type = ‘PERSONAL’
AND S1_.distributor_id = D2_.distributor_id
AND S1_.ean = product0_.ean
AND S1_.registration_id = L3_.registration_id
AND S1_.distributor_id = S4_.distributor_id
AND S1_.distributor_credit_id = S4_.distributor_credit_id
AND S1_.distributor_sub_credit_id = S4_.distributor_sub_credit_id
GROUP BY D2_.distributor_id,
D2_.name,
L3_.registration_id,
L3_.name,
product0_.ean,
S4_.duration,
product0_.mban,
product0_.title2
ORDER BY D2_.name DESC
If I remove the Group by (and Order by) and the two SUM’s the query executes in 0.03 secs. With the group by the query is using a temporary table and I think that’s is causing the slowness. I did tweak the the tmp_table_size and max_heap_table_size in order to reduce the creation of tmp tables to disk.
It did improve the performance a little bit. Now I’m trying to device an alternative query. Is this equivalent to the original query?
SELECT D2_.distributor_id AS col_0_0_,
D2_.name AS col_1_0_,
L3_.registration_id AS col_2_0_,
L3_.name AS col_3_0_,
product0_.ean AS col_4_0_,
S4_.duration AS col_5_0_,
product0_.mban AS col_6_0_,
product0_.title2 AS col_7_0_,
S1_.sum1 AS col_8_0_,
S1_.sum2 AS col_9_0_
FROM Q_P product0_
JOIN (select S1_.registration_id,
S1_.distributor_id,
S1_.distributor_id,
S1_.distributor_credit_id,
S1_.distributor_sub_credit_id,
S1_.ean,
sum(S1_.amount AS col_8_0_) as sum1 ,
sum(S1_.amount_corrected) as sum2
from db_kl.Q_S S1_
where S1_.specification_type = ‘PERSONAL’
group by S1_.registration_id,
S1_.distributor_id,
S1_.distributor_id,
S1_.distributor_credit_id,
S1_.distributor_sub_credit_id ) as S1_ on S1_.ean=product0_.ean
JOIN db_kl.Q_D D2_ on (S1_.distributor_id = D2_.distributor_id )
JOIN db_kl.Q_L L3_ on (S1_.registration_id = L3_.registration_id)
JOIN db_kl.Q_S2 S4_ on (S1_.distributor_id = S4_.distributor_id
AND S1_.distributor_credit_id = S4_.distributor_credit_id
AND S1_.distributor_sub_credit_id = S4_.distributor_sub_credit_id)
Thanks in advance.
regards,
Ivan