mySql Index for timestamp date

Question

I have a timestamp column and I have to filter by year. My db has about 10000 rows table.

This is my low query:

SELECT * FROM my_table WHERE DATE_FORMAT(my_timestamap_data, "%Y") = "2018"

how can I improve my performance? The best way is create an index?

thank you!

If you want to know if an index will improve performance, then create one and see — RiggsFolly
– RiggsFolly, Commented Feb 25, 2019 at 9:12
try SELECT * FROM my_table WHERE YEAR(my_timestamap_data) = 2018 — Devsi Odedra
– Devsi Odedra, Commented Feb 25, 2019 at 9:13

danblack · Accepted Answer · 2019-02-25 09:13:51Z

1

Simple index on my_timestamap_data:

And then query:

SELECT *
FROM my_table
WHERE my_timestamap >= '2018-01-01' and mytimestamap < 2019-01-01

answered Feb 25, 2019 at 9:13

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1