Querying DISTINCT SAME DAY from DATETIME in MySQL Without using DATE()-CodePudding

I have an SQL code like this:

SELECT DISTINCT DATE(intended_schedule)
FROM tbl_schedules 
WHERE customer_id = :customer_id
GROUP BY DATE(intended_schedule)

it is working.. but it is VERY SLOW.. I need an alternative to selecting DISTINCT SAME DAY from a DATETIME in MySQL

CodePudding user response：

can you try to add another virtual column?

alter table tbl_schedules add column `one_date` date generated always as (date(intended_schedule)) VIRTUAL;
select distinct one_date from tbl_schedules

time cost result, I tried on one of my tables :

select distinct one_date from xxx;/*38.2 ms*/

select distinct date(begin_time) from xxx group by date(begin_time); /*66.8ms*/

CodePudding user response：

What does VERY SLOW mean? Seconds, minutes, what?

The query:

SELECT DISTINCT DATE(intended_schedule)
FROM tbl_schedules 
WHERE customer_id = :customer_id

is enough. The GROUP BY is not needed here as the query contains no aggragate functions and DISTICT already sorts out the duplicate dates.

To see what is happening with the query you can use EXPLAIN:

EXPLAIN
SELECT DISTINCT DATE(intended_schedule)
FROM tbl_schedules 
WHERE customer_id = :customer_id