Re: [sqlite] DESC indexes not available after a VACUUM?
Thanks for addressing this, Richard. Preset the legacy_file_format pragma to the value of the primary database so that a VACUUM will not unknowingly alter the setting. Ticket #2804. http://www.sqlite.org/cvstrac/chngview?cn=4574 Could you please update the PRAGMA documentation to reflect the new preset behavior of "PRAGMA legacy_file_format;"? --- Joe Wilson <[EMAIL PROTECTED]> wrote: > When I compare the database bytes before and after the 2nd > process VACUUM these bytes differ: > > zero-basedvalue value > byte offset beforeafter my guess of what it is > 2nd VAC 2nd VAC > --- --- --- -- > 272 5 File change counter? > 432 5 Schema cookie? > 474 1 meta[1], file format? > > Doesn't byte offset 47 correspond to meta[1], the file format > of the schema layer? > > --- Joe Wilson <[EMAIL PROTECTED]> wrote: > > Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ... > > > > I'm confused by the explanation. > > Ignoring the PRAGMA for the moment, I was surprised that DESC > > indexes were not available after a reconnect and VACUUM, as shown > > in the example: > > > > $ rm -f foo.db > > $ ./sqlite3-3.5.3.bin foo.db > > SQLite version 3.5.3 > > Enter ".help" for instructions > > sqlite> PRAGMA legacy_file_format=OFF; > > sqlite> CREATE TABLE abc(a,b,c); > > sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc); > > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > > sqlite> vacuum; > > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > > sqlite> .q > > > > $ ./sqlite3-3.5.3.bin foo.db > > SQLite version 3.5.3 > > Enter ".help" for instructions > > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > > sqlite> vacuum; > > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > > 0|0|TABLE abc > > > > Notice that the DESC index is no longer available after the second > > process' VACUUM was run. > > > > If this isn't the file format being changed, then why isn't the DESC > > index being used? > > > > Is this by design? Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DESC indexes not available after a VACUUM?
When I compare the database bytes before and after the 2nd process VACUUM these bytes differ: zero-basedvalue value byte offset beforeafter my guess of what it is 2nd VAC 2nd VAC --- --- --- -- 272 5 File change counter? 432 5 Schema cookie? 474 1 meta[1], file format? Doesn't byte offset 47 correspond to meta[1], the file format of the schema layer? --- Joe Wilson <[EMAIL PROTECTED]> wrote: > Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ... > > I'm confused by the explanation. > Ignoring the PRAGMA for the moment, I was surprised that DESC > indexes were not available after a reconnect and VACUUM, as shown > in the example: > > $ rm -f foo.db > $ ./sqlite3-3.5.3.bin foo.db > SQLite version 3.5.3 > Enter ".help" for instructions > sqlite> PRAGMA legacy_file_format=OFF; > sqlite> CREATE TABLE abc(a,b,c); > sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc); > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > sqlite> vacuum; > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > sqlite> .q > > $ ./sqlite3-3.5.3.bin foo.db > SQLite version 3.5.3 > Enter ".help" for instructions > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > 0|0|TABLE abc WITH INDEX abc_i ORDER BY > sqlite> vacuum; > sqlite> explain query plan select * from abc order by b desc, c asc, a desc; > 0|0|TABLE abc > > Notice that the DESC index is no longer available after the second > process' VACUUM was run. > > If this isn't the file format being changed, then why isn't the DESC > index being used? > > Is this by design? Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DESC indexes not available after a VACUUM?
Regarding http://www.sqlite.org/cvstrac/tktview?tn=2804 ... I'm confused by the explanation. Ignoring the PRAGMA for the moment, I was surprised that DESC indexes were not available after a reconnect and VACUUM, as shown in the example: $ rm -f foo.db $ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions sqlite> PRAGMA legacy_file_format=OFF; sqlite> CREATE TABLE abc(a,b,c); sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc); sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc WITH INDEX abc_i ORDER BY sqlite> vacuum; sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc WITH INDEX abc_i ORDER BY sqlite> .q $ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc WITH INDEX abc_i ORDER BY sqlite> vacuum; sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc Notice that the DESC index is no longer available after the second process' VACUUM was run. If this isn't the file format being changed, then why isn't the DESC index being used? Is this by design? Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
