On 5 Feb 2018, at 2:54pm, John Found <johnfo...@asm32.info> wrote:

> It is clear now. But should I define an index that contains all fields used 
> in the query?
> Something like:
>    create index idxPostsComplex on posts(threadid, userid, Content, postTime, 
> ReadCount);
> Actually I tried and the query uses this index without problems (and the 
> performance seems to be good). 

Since you have the ability to do timings, why not try it ?  Do things get much 
faster after creating that extra index ?  Is the SELECT one which is used a lot 
or is it used just once in your program, at a time where execution time is not 
a problem ?

> But what are the disadvantages of such approach? (except the bigger database 
> size, of course)

* Increase in database file size
* More time taken when inserting rows into that table (one extra index to 
* More time taken when changing data in any of those columns (one extra index 
to update)

This is the usual payoff in computers: if it takes less time to find the data 
you want it probably takes longer to assemble the data to start with.  You have 
to figure out which of the two operations is more time-critical.

sqlite-users mailing list

Reply via email to