How to optimize sql query (MySQL)?

Question

MySQL

The request is as simple as possible, there are indexes on the required fields. It takes 0.5s, which is quite a long time, provided that there are other requests on the page that take the same time to complete.

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 gave the following:

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

As I understand the problem, that the first table (visits), where I make a selection by office, gives a lot of resulting data at once. Of course, there is a way to add the office field to the payments table, but I would not want to add redundant data.
What recommendations can you give?
I also want to clarify that the p.date field does not have an index

Answer 1

Don't use Cartesian work - unreadable. Use the normal JOIN syntax:

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'

Accordingly, to optimize the query, you need to:
1. get rid of the function in the selection condition and convert it to the form

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. Create indexes:
2a. visits (office, id).
2b. payments (`date`, visit)and payments (visit, `date`), see which one is in use, remove the unused one.
3. If the `v`.`office` field has a numeric type, change the selection condition for this field to AND `v`.`office` = 1. In its original form, if the field is numeric, then both the field and the value are converted to a floating-point number, and only then are compared.

Answer 2

The problem is that the first table (visits), where I make a selection by office, immediately gives a lot of resulting data.

Is not a fact. The mysql database is able to correct the most gross errors, and it may well rewrite the query so that payments are not joined to visits, but vice versa - as it should be.

(1) there are indexes on the required fields
(2) the p.date field does not have an index

Am I the only one seeing mutually exclusive paragraphs here?
In general, add an index to the date field
AND change the request so that payments are the first, and instead of date(), the desired date is selected via BETWEEN

Answer 3

I agree with the speakers, adding an index on Date helped, a small increase in execution speed, but the best increase was not using the Date function. Thanks worked.