Как оптимизировать sql запрос (MySQL)?

Question

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

Запрос максимально простой, индексы на нужных полях есть. Выполняется 0.5с, довольно долго при условии, что на странице есть и другие запросы, которые так же долго выполняются.

SELECT `p`.`sum`, `p`.`type`, `p`.`visit` FROM `visits` AS `v`, `payments` AS `p` 
WHERE DATE(p.date) = '2021-10-04' AND `v`.`id` = `p`.`visit` AND `v`.`office` = '1'

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

1 SIMPLE v NULL ref PRIMARY,office office 4 const 277126 100.00 Using index
1 SIMPLE p NULL ref visit,type visit 4 vet_local.v.id 1 56.25 Using where

Как я понимаю проблематика, что первая таблица (visits), где я делаю выборку по office, даёт сразу много результирующих данных. Конечно есть выход в таблицу payments добавить поле office, но не хотелось бы добавлять избыточные данные.
Какие можете дать рекомендации?
Так же хочу уточнить у поля p.date индекса нет

Comments

[Ипатьев]

Столько вопросов, и все без ответа.

индексы на нужных полях есть

"на нужных" - это на каких?
И откуда вообще такая уверенность, что индексы на нужных полях стоят, если запрос приходится оптимизировать?

С чего вы вдруг решили что добавление в таблицу payments поля office хоть как-то поможет?

Скажите, а вот вам лично - если оставить в стороне всех остальных, которые перетопчутся - вот вам самому удобно всё время елозить скроллом туда-сюда, чтобы прочесть запрос?
Неужели так трудно добавить один перевод строки?

SELECT `p`.`sum`, `p`.`type`, `p`.`visit` FROM `visits` AS `v`, `payments` AS `p` 
WHERE DATE(p.date) = '2021-10-04' AND `v`.`id` = `p`.`visit` AND `v`.`office` = '1'

Answer 1

[Akina]

Не используйте картезианское произведение - нечитаемо. Используйте нормальный JOIN-синтаксис:

SELECT `p`.`sum`, `p`.`type`, `p`.`visit` 
FROM `visits` AS `v`
JOIN `payments` AS `p` ON `v`.`id` = `p`.`visit` 
WHERE DATE(p.date) = '2021-10-04' AND `v`.`office` = '1'

Соответственно для оптимизации запроса требуется:

1. избавиться от функции в условии отбора и конвертировать его в вид

SELECT `p`.`sum`, `p`.`type`, `p`.`visit` 
FROM `visits` AS `v`
JOIN `payments` AS `p` ON `v`.`id` = `p`.`visit` 
WHERE p.date >= '2021-10-04' 
  AND p.date < '2021-10-05' 
  AND `v`.`office` = '1'

2. Создать индексы:

2a. visits (office, id).
2b. payments (`date`, visit) и payments (visit, `date`), посмотреть какой из них используется, удалить неиспользуемый.

3. Если поле `v`.`office` имеет числовой тип, изменить условие отбора по этому полю на AND `v`.`office` = 1. В исходном виде, если поле числовое, то и поле, и значение приводятся к floating-point number, и только потом сравниваются.

Comments

[Ипатьев]

и поле, и значение приводятся к FLOAT, и только потом сравниваются - это не про mysql
ну во всяком случае это не такое приведение, которое не даёт использовать индекс

[Akina]

Роман Юрьевич Ипатьев,

это не про mysql

Правило преобразования типов при сравнении совершенно чётко и однозначно прописаны вот тут: MySQL 8.0 Reference Manual / Functions and Opera.... Если Вы не согласны с моей интерпретацией - укажите, пожалуйста, что и где мной упущено, и как будет выполняться сравнение по Вашему мнению.

[Ипатьев]

Akina, не возьмусь спорить на ниве параграфов документации, но чисто эмпирическое наблюдение. Если бы индекс не работал при таком сравнении, то 99% пхп приложений встали бы колом.

Answer 2

[Ипатьев]

проблематика в том, что первая таблица (visits), где я делаю выборку по office, даёт сразу много результирующих данных.

Не факт. БД mysql умеет исправлять самые грубые ошибки, и вполне может переписать запрос так, чтобы не payments джойнилась к visits, а наоборот - как и должно быть.

(1) индексы на нужных полях есть
(2) у поля p.date индекса нет

Я один здесь вижу взаимоисключающие параграфы?

В общем, добавить индекс на поле date
И поменять запрос, чтобы payments была первой, а вместо date() нужная дата выбиралась через BETWEEN