Re: [sqlite] What is the most efficient way to get theclosebynumbers?

"Simon Slavin" wrote ... > > On 21 Aug 2010, at 3:29am, Igor Tandetnik wrote: > >> ve3meo wrote: >>> If the number of VM instructions is a good inverse indicator of >>> efficiency, >>> then the second query with the index appears to be the most efficient. >>> I'm

Re: [sqlite] partial index?

Simon Slavin wrote: > http://www.sqlite.org/lang_createview.html > > This is the SQL standard way to reduce your view of a table to just > certain rows. If I understand your request, this feature should provide > exactly what you want. Appropriate indexes will be used when consulting >

Re: [sqlite] partial index?

Kees Nuyt wrote: > You could add a "deleted" column with value range (0,1) and > create an index on it if benchmarks show that makes it > faster. As a bonus it is easier to code and maintain than a > separate table with references and triggers. > > Alternatively, you can create an composite

Re: [sqlite] partial index?

Sorry, let me amend that: > The schema is roughly > > create table records(__recno INTEGER PRIMARY KEY, fileId, data); Forget the INTEGER PRIMARY KEY. My partial index would reference the _rowid_. I don't permit vacuums on the database so, if I'm not mistaken, this shouldn't be an issue.

Re: [sqlite] partial index?

Stephen Oberholtzer wrote: > I believe what he's getting at is this: {snip explanation} You exactly understand what I'm going for and my use case. Is there a better way to implement it in sql itself than what I outlined? I.e. create my own index table that points to the proper rows and keep it

Re: [sqlite] partial index?

Igor Tandetnik wrote: > > How would you find a row whose column X contained value Y if the > > "partial" index on column X specified that rows containing value Y > > in column X should never be returned? > > No one suggests partial index should be capable of hiding anything. The > idea is

Re: [sqlite] What is the most efficient way to get the close by numbers?

Peng Yu wrote: > I have the following code to search for neighboring positions > (distance <=10). But it is slow for large data set. I'm wondering what > is the most efficient query for such a search. Note that I don't > create an index, as I'm not sure what index to create on table A. I haven't

Re: [sqlite] What is the most efficient way to get the closebynumbers?

On 21 Aug 2010, at 3:29am, Igor Tandetnik wrote: > ve3meo wrote: >> If the number of VM instructions is a good inverse indicator of efficiency, >> then the second query with the index appears to be the most efficient. I'm >> sure somebody will point out if it is not.

Re: [sqlite] What is the most efficient way to get the closebynumbers?

ve3meo wrote: > If the number of VM instructions is a good inverse indicator of efficiency, > then the second query with the index appears to be the most efficient. I'm > sure somebody will point out if it is not. It is not. It may very well take fewer instructions to

Re: [sqlite] What is the most efficient way to get the close bynumbers?

Without an index, EXPLAIN returns the following number of virtual machine instructions for each query: 41 - original query: select * from A as A1, A as A2 where A1.name=A2.name and abs(A1.position - A2.position) <= 10 and A1.position != A2.position ; 39 - original query re-ordered: select *

Re: [sqlite] What is the most efficient way to get the close by numbers?

On 20 Aug 2010, at 11:54pm, Peng Yu wrote: > select * from A as A1, A as A2 where A1.name=A2.name and > abs(A1.position - A2.position) <= 10 and A1.position != A2.position; If you're doing this a lot, work out which chunks of 20 each point is in. You only need to compare a point with points

Re: [sqlite] What is the most efficient way to get the close by numbers?

If there is an index on (name, position) the a where like below might use it. A1.name=A2.name and A2.position between( A1.position - 10, A1.position + 10 ) On 8/20/2010 3:54 PM, Peng Yu wrote: > Hi, > > I have the following code to search for neighboring positions > (distance<=10). But it is

[sqlite] What is the most efficient way to get the close by numbers?

Hi, I have the following code to search for neighboring positions (distance <=10). But it is slow for large data set. I'm wondering what is the most efficient query for such a search. Note that I don't create an index, as I'm not sure what index to create on table A. $ cat main.sql

[sqlite] REPLACE syntax doesn't seem to work fts3 tables

Looks like using replace on a fts3 table doesn't seem to work: SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create virtual table fts using fts3(message); sqlite> insert into fts (docid, message) values (1,'bob'); sqlite> insert into fts

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

On 08/19/2010 01:27 PM, Taras Glek wrote: Hi, I really appreciate that sqlite got this feature to reduce fragmentation, but why not expose this as a pragma? In many cases it is not feasible to pass the chunk size via a C API. For example with a pragma I could do fragmentation testing via an

Re: [sqlite] errors running test suite (couldn't execute "testfixture")

After updating the tests run fine now. Thanks for the quick update! By the way, I usually avoid having "." in the PATH for security reasons. On Fri, Aug 20, 2010 at 05:30, Dan Kennedy wrote: > > On Aug 20, 2010, at 6:07 AM, Paweł Hajdan, Jr. wrote: > > > I updated to

Re: [sqlite] partial index?

On Fri, Aug 20, 2010 at 06:52:40AM -0700, Cory Nelson scratched on the wall: > On Thu, Aug 19, 2010 at 1:30 PM, Eric Smith wrote: > > Afaict sqlite doesn't support indices on subsets of rows in a table, ?? > > la http://en.wikipedia.org/wiki/Partial_index -- right? > > > > Any

Re: [sqlite] partial index?

On 8/20/10, Cory Nelson wrote: > On Fri, Aug 20, 2010 at 7:39 AM, Jim Wilcoxson wrote: >> ... >> The best I could come up with is a separate table. The problem is, >> indexing the SHA1 normally means there is a copy in the row and a copy >> in the index.

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

On Thu, Aug 19, 2010 at 06:46:07PM -0400, Jim Wilcoxson scratched on the wall: > On 8/19/10, Simon Slavin wrote: > > > > On 19 Aug 2010, at 9:27pm, Taras Glek wrote: > > > >> I really appreciate that sqlite got this feature to reduce > >> fragmentation, but why not expose

Re: [sqlite] partial index?

On Fri, Aug 20, 2010 at 7:39 AM, Jim Wilcoxson wrote: > ... > The best I could come up with is a separate table. The problem is, > indexing the SHA1 normally means there is a copy in the row and a copy > in the index. Using a separate table, which still has to be indexed, >

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

On Thu, Aug 19, 2010 at 10:56:58PM +0100, Simon Slavin scratched on the wall: > It might be worth noting that fragmentation is normally seen as an issue > only under Windows which is very sensitive to it however. Most systems are sensitive to fragmentation. It just happens that many other

[sqlite] Help with database corruption?

Hello, is there anybody willing to help analyze corrupted database for possible bug in SQLite? It is a database file taken from one of our test machines and it is only few days old at most. The database file was only ever accessed with SQLite 3.7.0.1. It has page size of 1024, WAL mode,

Re: [sqlite] partial index?

On 8/20/10, Cory Nelson wrote: > +1 for this feature request. They've got a very specific and fairly > rare use case, but when opportunity strikes partial indexes are much > more convenient, straightforward, and efficient than the alternative. > > - If a table has

Re: [sqlite] partial index?

On Thu, Aug 19, 2010 at 1:30 PM, Eric Smith wrote: > Afaict sqlite doesn't support indices on subsets of rows in a table, Ю > la http://en.wikipedia.org/wiki/Partial_index -- right? > > Any plans to implement that? +1 for this feature request. They've got a very specific and

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

> If you make PRAGMA freelist_count writable as you suggest, I would > expect the database to reserve space once, and not use a larger pice of > the disk every time ist has to be expanded in the future. > > Martin To throw in my $0.02, I would suggest a *different* name for the pragma, something

Re: [sqlite] errors running test suite (couldn't execute "testfixture")

On Aug 20, 2010, at 6:07 AM, Paweł Hajdan, Jr. wrote: > I updated to latest fossil version, ran make distclean, ./configure, > make, > make test and got this: Thanks for this report. The test code was assuming that "." was in your PATH variable. Fixed now. > What should I do to make it pass?

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

Am 20.08.2010 13:38, schrieb Max Vlasov: >> In my case (which is certainly not typical), a (several GB) large >> database is built up in several batches, one table at a time, while in >> parallel many intermediate files on the disk are created. This resulted >> in a very fragmented database

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

On 20 Aug 2010, at 7:58am, Martin Engelschalk wrote: > Under Windows, the insert speed did not change measurably, but the speed > of the later selects increased by about 15-20%. Also, my customer was > happy. Okay, this is real-world data. In that case there may be some point to

Re: [sqlite] partial index?

Tim Romano wrote: > Igor, > Here's the example where a partial index can "hide" rows. > > From the wikipedia article cited by the OP: > > > It is not necessary that the condition be the same as the index criterion; > Stonebraker's paper below presents a number of

Re: [sqlite] partial index?

On Fri, Aug 20, 2010 at 1:47 PM, Tim Romano wrote: > Igor, > Here's the example where a partial index can "hide" rows. > > From the wikipedia article cited by the OP: > > > It is not necessary that the condition be the same as the index criterion; > Stonebraker's paper

Re: [sqlite] partial index?

Igor, Here's the example where a partial index can "hide" rows. >From the wikipedia article cited by the OP: It is not necessary that the condition be the same as the index criterion; Stonebraker's paper below presents a number of examples with indexes similar to the following: create index

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

> In my case (which is certainly not typical), a (several GB) large > database is built up in several batches, one table at a time, while in > parallel many intermediate files on the disk are created. This resulted > in a very fragmented database file. After that, also several times, the > data is

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

On Thu, Aug 19, 2010 at 2:56 PM, Simon Slavin wrote: > It might be worth noting that fragmentation is normally seen as an issue only > under Windows which is very sensitive to it however.  Other operating systems > use different ways of handling disk access, however, real

Re: [sqlite] Sizeof tables

Am 18.08.2010 16:09, schrieb Max Vlasov: > On Tue, Aug 17, 2010 at 4:28 PM, Lukas Haase wrote: > >> Hi, >> >> My sqlite database is about 65 MB. The data is split into serval tables. >> >> Is there a way to enumerate the space requirements for each table so >> that I can see

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

Am 19.08.2010 23:56, schrieb Simon Slavin: > On 19 Aug 2010, at 9:27pm, Taras Glek wrote: > >> I really appreciate that sqlite got this feature to reduce >> fragmentation, but why not expose this as a pragma? > Do you have figures which suggest that reducing fragmentation leads to any >

Re: [sqlite] partial index?

> Tim Romano wrote: >> How would you find a row whose column X contained value Y if the >> "partial" >> index on column X specified that rows containing value Y in column X >> should >> never be returned? > > No one suggests partial index should be capable of hiding