0

I am trying to delete the record from the specific date range .

So I have used the below query

delete from `table_name` where `date`<1580947200

I have used datetime as data type for the date column

It takes long time to execute and sometimes it gets stuck while executing this query.

Can anyone say how to apply index for this query

Martin
  • 22,212
  • 11
  • 70
  • 132
Shankar V
  • 13
  • 1

1 Answers1

0

Your issue is not indexing. Your issue is that your telling MySQL to numerically compare a non-numeric (DATETIME) column against a numeric value (Unix timestamp).

i have used datetime as datatype for date column

This means the column is a DATETIME column. But;

1580947200

looks to me like a Unix Timestamp value.

Unix Timestamp is not the same as a MySQL DateTime entity.

Therefore; you need to CAST these two types to the same for the comparison; To do this you can use the UNIX_TIMESTAMP() MySQL Function.

DELETE FROM `table_name` WHERE UNIX_TIMESTAMP(`date`) < 1580947200

If you do wish to add an index to the date column you can read this Q&A.

Martin
  • 22,212
  • 11
  • 70
  • 132