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.
Best Answer
using generate column and created a index on it:
then created index: