A
A
Adamos2018-03-19 17:16:06
MySQL
Adamos, 2018-03-19 17:16:06

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

EXPLAIN on test server
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

EXPLAIN on production server
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

1 answer(s)
L
Lazy @BojackHorseman MySQL, 2018-03-19
@Adamos

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 question

Ask a Question

731 491 924 answers to any question