Virtual column in MySQL or how to work with dates in MySQL?

Question

MySQL

Hello. There is a table that has a datetime column that contains the date and time. The field is indexed.
And let's say I want to get data for certain numbers, and I designed a query with such a condition

WHERE DATE_FORMAT(date, "%Y-%m-%d") IN ("2018-06-19","2018-06-20","2018-06-26")

And everything hung ... The base is in production, it takes a long time to add a new field with the DATE type, there is a lot of data. Prompt as it is correct to construct request.
I haven’t tried the option with ranges (2018-06-19 00:00:00 - 2018-06-19 23:59:59) for each day, I think it’s a crutch, which, by anticipation, will also slow down.

Answer 1

The format of the condition "function(table_field) operator" in principle cannot use an index on this field. If there are no other selective indexes - guaranteed fullscan.
Condition format "table_field between ? and ?" - can use an index.
Findings?
3 ranges through or or, perhaps, 3 union all in each one range will be more efficient. I don't remember how adequately mysql now behaves with or.

Answer 2

between start of day and end of day and union all for each date.
no, it will not.

Answer 3

At a minimum, you can remove DATE_FORMAT (although it may only be suitable for date, not datetime), otherwise I don’t see any optimizations possible for selecting random dates in a bunch.
Index somehow else, or limit selections at the interface level (only by periods).
Can try to shove in elestik/sphinx.
mysql 8+ can add new columns quickly.
It's only useless to add date, if you can't figure it out with datetime, the awl will change.