Clean SQL: Should I reference dates that I could ask for in my database?-CodePudding

I have millions of rows in my database SQLite Studio. I would like to ask it which months I can have. When I do my request, it takes a long time (5 min, more than the half of time that it took to get my data for a month!)

--How I call for months BTW -- 
SELECT DISTINCT strftime('%Y-%m', time_UTC) AS month FROM transacts ORDER BY month ASC;

It could be really fast if I create a table with all available months but I hear a little voice in my head telling:

YOU HAVE NOT TO MAKE A DATE TABLE

But I don't remember if it's bad and why. It sound to me pretty well. What do you think about please?

EDIT : How to speed up a request SQL on dates avaiable

CodePudding user response：

You can create an index on the expression strftime('%Y-%m', time_UTC) to get better performance:

CREATE INDEX id_transacts_time ON transacts(strftime('%Y-%m', time_UTC));

Check in the demo the query plan that uses the index.