[sqlite] FTS3 Appropriate usage

So I'm building this web-site solution where high performance is a clear requirement. Pages content should not be static, but rather put together on the basis of supplied search criteria (basically tags); The page must include content for which associated tags match the supplied search criteria.

Re: [sqlite] FTS3 Appropriate usage

On 15 Apr 2010, at 8:21am, Jens wrote: > I'd appreciate any feedback you might have one this. Also, does anyone > have experience with sqlite+ft3 and high-availability solutions? Has > anyone done any benchmarking of fts3? For a start, FTS3 is a very specific solution to a very specific

Re: [sqlite] Index and GLOB

> > sqlite> explain query plan SELECT tb_file_key, basename, extension, > > path FROM tb_file WHERE basename GLOB 'a' AND extension GLOB 'b' AND > > path GLOB '*'; > > 0|0|TABLE tb_file WITH INDEX fullpath_idx > > > > > SQLite will not try to optimize a GLOB (to use an index) if it doesn't >

[sqlite] Please help test the latest query planner changes

We've been tweaking of the SQLite query planner in an effort to help joins run faster. But whenever you tweak the query planner, there is always a risk that some query plans might become significantly slower. Your help in identifying any performance regressions is greatly appreciated.

Re: [sqlite] Bug

On Thu, Apr 15, 2010 at 3:36 AM, Wiktor Adamski wrote: > SQLite version 3.6.23.1 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table t(a); > sqlite> insert into t values(1); > sqlite> insert into t values(2); > sqlite>

Re: [sqlite] Bug

Puneet, probably you are wrong. UNION and UNION ALL behave differently: SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE t (a); sqlite> INSERT INTO t VALUES (1); sqlite> INSERT INTO t VALUES (2); sqlite> SELECT * FROM t; 1 2

Re: [sqlite] Bug

On Apr 15, 2010, at 10:22 AM, P Kishor wrote: > On Thu, Apr 15, 2010 at 3:36 AM, Wiktor Adamski > wrote: >> SQLite version 3.6.23.1 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> create table t(a); >> sqlite> insert into t

Re: [sqlite] Index and GLOB

On Thu, Apr 15, 2010 at 9:51 AM, Mike Goins wrote: > > > sqlite> explain query plan SELECT tb_file_key, basename, extension, > > > path FROM tb_file WHERE basename GLOB 'a' AND extension GLOB 'b' AND > > > path GLOB '*'; > > > 0|0|TABLE tb_file WITH INDEX

Re: [sqlite] Index not used in simple look-up-value query

In the light of the work on query-plan optimizations currently being undertaken, I wonder if this issue could be looked at more closely. I can confirm the returning type doesn't affect the performance I'm seeing from the simple query. For example, returning an INT instead: SELECT ElementID

Re: [sqlite] Index not used in simple look-up-value query

On Apr 15, 2010, at 10:51 PM, wrote: > In the light of the work on query-plan optimizations currently being > undertaken, I wonder if this issue could be looked at more closely. > I can confirm the returning type doesn't affect the performance I'm > seeing from the

Re: [sqlite] Index not used in simple look-up-value query

Thanks for your prompt response Dan. I missed mentioning that in my tests, I've made sure both queries lead to a single row (value) being returned in all cases. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy

Re: [sqlite] Index not used in simple look-up-value query

> Thanks for your prompt response Dan. I missed mentioning that in my tests, > I've made sure both queries lead to a single row (value) being returned in > all cases. Also you forgot to mention how many rows MyDataTable and MyIntermediateTable have (from first email we know that MyTable

Re: [sqlite] Index not used in simple look-up-value query

Hi Pavel Re: how many rows MyDataTable and MyIntermediateTable have I had mentioned that their size is comparable to MyTable. More specifically: MyDataTable has 100K rows and MyIntermediateTable has 300K rows Their structure is as follow: CREATE TABLE [MyDataTable] ( [RecordID] INTEGER

Re: [sqlite] Reduce database file size

On Sun, 11 Apr 2010 14:09:21 -0400 Nikolaus Rath wrote: > Using rsync is unfortunately not possible, I'm stuck with HTTP and > FTP. > > I tried a few PPM compressors, but even though the compression ratio > is amazing, I'm not desperate enough to invest *that* much CPU time >

Re: [sqlite] FTS3 Appropriate usage

Thanks for your feedback. I gonna keep my data in my main RDBMS instead (which supports full-text searching as well). If it's too slow, i still have to option of setting up a database cluster or something. On Apr 15, 1:41 pm, Simon Slavin wrote: > On 15 Apr 2010, at 8:21am,