Answer the question
In order to leave comments, you need to log in
Difference in query execution speed - can it be fixed?
There is a simple request. Debugged on a test server, then launched on a production server.
The difference in speed is monstrous (0.0009 sec. vs. 6.6190 sec.)
As I understand it, the reason is in the MySQL version (5.6 on the test server, 5.5 on the production server).
Well, the hoster could still roll up branded brakes from Intel to darn fashionable holes.
I can not change the version - shared. Are there any optimization opportunities here?
The volume is still ridiculous, but the speed is already to hell.
Request
SELECT client_id, SUM(cost - IFNULL(p_price, 0)) AS debt
FROM (
SELECT * FROM orders WHERE pay_till <= '2017-12-31'
) AS orders LEFT JOIN (
SELECT order_id, SUM(summa) as p_price FROM payments WHERE date <= '2017-12-31' GROUP BY order_id
) AS p ON p.order_id = orders.id
GROUP BY client_id HAVING ABS(debt) > 0.01
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 21056 Using temporary; Using filesort
1 PRIMARY <derived3> ref <auto_key0> <auto_key0> 4 orders.id 10 NULL
3 DERIVED payments index order_id order_id 4 NULL 17284 Using where
2 DERIVED orders ALL NULL NULL NULL NULL 21056 Using where
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 14606 Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11417
3 DERIVED orders ALL NULL NULL NULL NULL 21152 Using where
2 DERIVED payments index NULL order_id 4 NULL 16302 Using where
Answer the question
In order to leave comments, you need to log in
yes, rewrite the query correctly. Join orders for payments, then group and calculate underpayment/overpayment. write request, look the plan, look where what indexes do not suffice.
ps actually there will need an index on the pay_till field in orders and a composite index (order_id, date) in payments
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question