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

Question

[Петр Питерский]

Есть две простые таблицы, visits: id, title, office и payments: id, visit, type, date, sum.
То есть у одного приёма может быть несколько платежей.

Задача запросить определённые типы платежей за период, относящиеся к филиалу.

SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`, `visits` AS `v`
WHERE `p`.`date` >= '2022-05-01 00:00:00'
AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` IN('cash', 'cashless', 'advance')
AND `p`.`visit` = `v`.`id` 
AND `v`.`office` = 131

Запрос работает корректно, но вот скорость выполнения очень низкая (в таблице visits около 1 млн записей, в payments около 1.5 записей), естественно стоят индексы на date, type, office (jy внешний ключ для таблицы visits).

Запрос выполняется 6 секунду, что конечно не лезет ни в какие рамки. Причем в результатах запроса около 3000-400 записей.

Если предложения что можно изменить, сразу скажу не предлагать добавить в таблицу payments дополнительное поле office.

Explain даёт следующее:

id 	select_type 	table 	partitions 	type 	possible_keys 	key 	key_len 	ref 	rows 	filtered 	Extra 	
1 	SIMPLE 	v 	NULL 	ref 	PRIMARY,office 	office 	4 	const 	119846 	100.00 	Using index
1 	SIMPLE 	p 	NULL 	ref 	visit,date 	visit 	4 	      v.id 	1 	4.94 	Using where

Comments

[Akina]

Есть две простые таблицы, visits: id, title, office и payments: id, visit, type, date, sum.

Ваще ниачём.

Если спрашиваете за оптимизацию - выкладывайте полные CREATE TABLE обеих таблиц.

[Vitsliputsli]

Как выглядят таблицы? Почему мы обращаемся в v с единичным значением office и получам 100 тысяч id?! Эти id не id?

Answer 1

[Slava Rozhnev]

SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN  `visits` AS `v` ON  `p`.`visit` = `v`.`id` 
WHERE `p`.`date` >= '2022-05-01 00:00:00'
AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` IN('cash', 'cashless', 'advance')
AND `v`.`office` = 131

SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` IN('cash', 'cashless', 'advance')
AND  EXISTS (SELECT 1 FROM  `visits` `v` WHERE  `p`.`visit` = `v`.`id` AND  `v`.`office` = 131)

SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN  `visits` AS `v` ON  `p`.`visit` = `v`.`id` 
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
        AND `p`.`type`  = 'cash'
        AND `v`.`office` = 131
UNION
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN  `visits` AS `v` ON  `p`.`visit` = `v`.`id` 
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
        AND `p`.`type` =  'cashless'
        AND `v`.`office` = 131
UNION
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN  `visits` AS `v` ON  `p`.`visit` = `v`.`id` 
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
        AND `p`.`type`  =  'advance'
        AND `v`.`office` = 131

Comments

[mayton2019]

Не знаю какой будет план но UNION выглядит дороговато. Автор хочет аж 3000 строк.

[Slava Rozhnev]

Я просто ночью набросал несколько вариантов. Пусть автор потестит и отпишется

[Vitsliputsli]

Slava Rozhnev, UNION ALL будет лучше.

[Петр Питерский]

Попробовал запросы так же по скрости выполняются, последний, еще хуже

[Vitsliputsli]

Петр Питерский, последний запросы имел бы смысл, когда идем по индексу type.

Answer 2

[mayton2019]

6 секунд - нормальная себе скорость. Тем более что вы пользователю хотите аж 3000 строк показать.
Да он их читать будет целую неделю.