sqlsqlitedateindexingquery-optimization

How do I speed up this strftime('%Y-%m', time_UTC) query?

This query takes a long time:

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

How do I speed up this query for dates available?

Solution

  • 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.