Re: [sqlite] Increasing performance of query

Gabriele Lanaro wrote: > Are there any tips to make sure that the cache is used properly? The default is 2000 pages (8 MB with the default page size), and in most cases should be increased. But what works best in your specific case depends on the hardware, the OS, the software, the database

Re: [sqlite] Increasing performance of query

Also, I have question about the cache_size pragma. If I run the query (this is regarding the count query), without first running ANALYZE on the database, I can see that the times are affected by the cache_size pragma as follows: cache_size = 0 timing = 3.7 s cache_size = -2000 (2000 kb) timing =

Re: [sqlite] Increasing performance of query

Thanks for your answers! First of all, my apologies with ANALYZE I meant that it increased performance (decreased query time) by orders of magnitude. Before ANALYZE the query was in the order of seconds (about 2 seconds). After ANALYZE the query was in the order of milliseconds. I tried VACUUM

Re: [sqlite] Increasing performance of query

On 2017/04/19 8:50 PM, Gabriele Lanaro wrote: Dear SQLite communiy, I’m trying to increase the performance of a query to its maximum possible speed. The schema is roughly constituted of two tables, one is a full text index, fts5_table, while the other is called datatable. The query in

Re: [sqlite] Increasing performance of query

On 19 Apr 2017, at 7:50pm, Gabriele Lanaro wrote: > *SELECT* count(datatable.id) *FROM* fts5_table, datatable *WHERE* > fts5_table *MATCH* ‘term’ *AND* datatable.id = fts5_column.rowid If datatable.id is never NULL, then "count(*)" should do the same thing and be