ON MySQL 5.7, generated columns are not allowed with non-deterministic function.

The query that I need to optimize is executing on a bulky table.

Where clause is causing a full scan on the table, need to optimize that.

The column is DateTime type, we have to use the unix_timestamp function as per application

WHERE UNIX_TIMESTAMP(Last_Used_Time) > 1540874005

I am trying to convert UNIX_TIMESTAMP(Last_Used_Time) into a generated column but throws an error disallowed function used in the expression.

Any workaround to allow MySQL to use generated/virtual column with unix_timestamp function.

  • You are barking up a specific tree. Let's back up and see if there is another way to solve the problem. Please provide SHOW CREATE TABLE and the query that is so slow.
    – Rick James
    Oct 31 '18 at 5:24
  • The column is DateTime type If so why you use timezone-dependent function? If you know datetime you want to compare with - why you do not compare with datetime literal immediately?
    – Akina
    Oct 31 '18 at 5:24

Don't convert the column's value, convert your criterium:

WHERE Last_Used_Time > from_unixtime(1540874005)
  • That's brilliant, thanks for the suggestion. Oct 31 '18 at 5:31
  • Hi Gerard, I tried as per your suggestion, query execution time reduces from 4 min to 4 seconds, so query seem optimized, but on comparing records, I am finding the records obtained from before and after query vary slightly. Few records are not matching, not sure why so, any reason that you are aware of? Oct 31 '18 at 10:20
  • Please show last_used_time from a number of matching and no longer matching records (in your question please). Oct 31 '18 at 14:47
  • Hey Gerard, figured out the issue was, misunderstanding of data from our end. Anyways Thanks for your support. Nov 1 '18 at 5:24
  • It's been a pleasure. Nov 1 '18 at 5:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.