Have you tried putting the yearmonth in 2 different columns, not sure if it
will be
any faster, but I think it will, due to being a lot of them repeated.
Check you bandwidth and see if it's enough for your web users to access this
query
remotely.
Peace
> 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,
>
>
>
>
>
>
