is it possible to using index in unix datetime colum in MySQL 5.7 when using function

Question

is it possible to using index in unix datetime colum in MySQL 5.7? I define my database table like this:

CREATE TABLE `report_envelope_app_list` (
 `created_time` bigint(20) NOT NULL,
 `user_id` bigint(20) NOT NULL
)

the created_time store unix timestamp,now this is my SQL:

 select max(user_id) as user_id
 from report_user
 group by user_id, date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d')

but now the colum created_time contains function and could not using the index, is it possbible to using index, I have to add a column to store date like %Y-%m-%d? I tried to add a generated column like this:

ALTER TABLE report_user 
ADD COLUMN statistic_date varchar(16) 
GENERATED ALWAYS AS date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') STORED;

but failed.

• Rick James: date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d') could go into a DATE column
• Rick James: Do you really want to both GROUP BY user_id _and` SELECT MAX(user_id)??

Answer 1

using generate column and created a index on it:

ALTER TABLE report_user 
ADD COLUMN statistic_date varchar(16) 
GENERATED ALWAYS AS (date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')) STORED;

then created index:

CREATE INDEX report_user_userid_staticdate_idx 
ON report_user(user_id,statistic_date);

• Akina: ... then use this generated column in the query instead of the expression.