Perhaps you could create a table with the results (e.g. columns
yearmonth, date, hasrecord=0|1) and create triggers for the main table
so that if the records change, the triggers change the value in the
result table.
Jakub
Tom Deblauwe wrote:
Hello,
I'm using SQLite 2.8.16, on an embedded system, and I have a question.
I have a database with one table, containing all my records. Each has a
field 'yearmonth' which contains for example '200508', and also a field
'date', which indicates the day of that month. I want now to make an
overview of a month and see which days contain a record. I've created a
combined index on 'yearmonth' and 'date'. My query to do this is:
'select distinct date from mytable where yearmonth = 200508;' This
takes about 50 seconds, which is really slow to show my users(it is
displayed on a webpage).
I noticed that this takes a long time, because I guess almost all
records are checked. My other idea was to do 31 queries(one for every
day), like this: 'select ID from mytable where yearmonth = 200508 and
date = 1 limit 1;'. This second method does not give me anymore speed
improvement.
for example:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ...
^ ^ ^ ^
the days marked with '^' contain records. To check with the second
method for example day 7, it takes 2.6 seconds. Day 4 takes less, and
from day 3 and lower, it is reacting immediately(like 40 ms). Day 8 and
higher takes about 1 second each.
When I do a random query om my database with select, and want to return
10 results for example, it takes 2.8 seconds. So getting 1 result, or
getting 10 results only differs about 0.2 seconds. Is there anything I
can do so things go faster? Maybe I should maintain a separate table
with the monthoverview, but if it is possible to do some other solution
it would be nicer ;-).
Thanks for your time,
kind regards,
Tom,
--
Jakub Adamek
Programmer
Telematix Software, a.s.
Karoliny Svetle 12, Praha 1
http://www.dynavix.com
Tel: +420 608 247197
Office: +420 224233470
