Working in sqlite v3, I have a query that needs a calendar table, essentially to expand monthly bills into daily bills. Despite my careful addition of indices (and use of EXPLAIN QUERY PLAN), it's still too slow for my needs.
The query in question is essentially:
SELECT stuff
FROM (<subquery>) AS calendar
INNER JOIN ...
INNER JOIN ...
INNER JOIN ...
GROUP BY calendar.date
where <subquery> is a nifty calendar generation trick I picked up somewhere:
SELECT DATE('#{start_time.to_s(:db)}', (d4.digit * 10000 + d3.digit * 1000 + d2.digit * 100 + d1.digit * 10 + d0.digit) || ' DAYS') AS date
FROM digits AS d0
INNER JOIN digits AS d1
INNER JOIN digits AS d2
INNER JOIN digits AS d3
INNER JOIN digits AS d4
WHERE (d4.digit * 10000 + d3.digit * 1000 + d2.digit * 100 + d1.digit * 10 + d0.digit) < #{ndays}
ORDER BY date
(Yes, the digits table is pre-loaded with the digits 0...9. How it really works is left as an exercise to the reader!)
I know that the calendar subquery is fast (< 50 ms). But upon re-reading the sqlite docs, I understand that GROUP BY calendar.date in the outer query forces the generation of an index on calendar.date. Since the calendar is dynamically generated, it's not indexed.
So question, always with the questions:
- Is the lack of index on calendar.date likely to be a source of performance hit? (As I mentioned, I was careful in indexing the tables used in the outer query.)
- Will I be happier just creating a giant 20-year table and indexing it? Or is there a better way?
- I was pretty good at getting mysql to perform well. Are there sqlite optimization tricks (or gotchas) that I should know?
TIA...
