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 update) * 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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
