Re: [sqlite] Covering Index?

On Tue, Jun 4, 2013 at 10:41 PM, Simon Slavin wrote: > > On 5 Jun 2013, at 3:16am, David de Regt wrote: > > > CREATE TABLE test (col1 text, col2 text, col3 text); > > CREATE INDEX tindex ON test (col1, col2, col3); > > > > explain query plan > > SELECT

Re: [sqlite] Covering Index?

On 5 Jun 2013, at 3:16am, David de Regt wrote: > CREATE TABLE test (col1 text, col2 text, col3 text); > CREATE INDEX tindex ON test (col1, col2, col3); > > explain query plan > SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c'; > > The above returns: > SEARCH TABLE test

Re: [sqlite] Covering Index?

> Quick question, SQLites, > CREATE TABLE test (col1 text, col2 text, col3 text); > CREATE INDEX tindex ON test (col1, col2, col3); > explain query plan > SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c'; > The above returns: > SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2

Re: [sqlite] Covering Index?

On Tue, Jun 4, 2013 at 10:30 PM, David de Regt wrote: > Okay, but, it's essentially doing the equivalent of a "table scan" over > the portion of the index where col1='a', so if col1='a' doesn't actually > end up narrowing down the resultset hugely, you're still better off

Re: [sqlite] Covering Index?

Okay, but, it's essentially doing the equivalent of a "table scan" over the portion of the index where col1='a', so if col1='a' doesn't actually end up narrowing down the resultset hugely, you're still better off with a properly ordered index, correct? (with YMMV disclaimers) -David

Re: [sqlite] Covering Index?

On Tue, Jun 4, 2013 at 10:16 PM, David de Regt wrote: > Quick question, SQLites, > > CREATE TABLE test (col1 text, col2 text, col3 text); > CREATE INDEX tindex ON test (col1, col2, col3); > > explain query plan > SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c'; > > The

[sqlite] Please send in SQLite database statistics

The "sqlite3_analyzer" program reads an SQLite database file and prints out a text summary of the sizes of the various tables and indices in that database file. The source code to sqlite3_analyzer is in the public SQLite source tree; you can type "make sqlite3_analyzer" or "make

[sqlite] Covering Index?

Quick question, SQLites, CREATE TABLE test (col1 text, col2 text, col3 text); CREATE INDEX tindex ON test (col1, col2, col3); explain query plan SELECT * FROM test WHERE col1 = 'a' AND col3 = 'c'; The above returns: SEARCH TABLE test USING COVERING INDEX tindex (col1=?) (~2 rows) Which of

Re: [sqlite] Limit of attached databases

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/06/13 08:14, Eleytherios Stamatogiannakis wrote: > We have tried with both views and VTs but SQLite does not create > automatic indexes on them at all. So right now, to be able to have > automatic indexes from SQLite's side we materialize all

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

On Tue, Jun 4, 2013 at 11:59 AM, Philip Goetz wrote: > On Tue, Jun 4, 2013 at 11:27 AM, Richard Hipp wrote: > > Try adding the command: > > > > .log stdout > > > > before running the CREATE INDEX and see what errors you get back. > > sqlite> .schema >

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

On Tue, Jun 4, 2013 at 11:27 AM, Richard Hipp wrote: > Try adding the command: > > .log stdout > > before running the CREATE INDEX and see what errors you get back. sqlite> .schema CREATE TABLE taxon (gi INTEGER, taxon INTEGER); sqlite> .log stdout sqlite> create index

Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file

On Tue, Jun 4, 2013 at 11:22 AM, Philip Goetz wrote: > "unable to open database file" errors are usually caused by incorrect > file protection settings or directory protection settings, but I don't > think this one is. > > This is a 5.0G database with one table that I

Re: [sqlite] Wiki out of date?

On Tue, Jun 4, 2013 at 11:07 AM, Philip Bennefall wrote: > Hello, > > I was looking at the following page on the wiki: > > http://www.sqlite.org/cvstrac/**wiki?p=**LibraryRoutineCalledOutOfSeque** > nce >

[sqlite] An "unable to open database file" error that has nothing to do with opening database file

"unable to open database file" errors are usually caused by incorrect file protection settings or directory protection settings, but I don't think this one is. This is a 5.0G database with one table that I created in Windows 7 64-bit with sqlite3 3.7.16.2 from Perl. I can connect to the DB, read

Re: [sqlite] Limit of attached databases

On 04/06/13 17:37, Simon Slavin wrote: On 4 Jun 2013, at 3:09pm, Eleytherios Stamatogiannakis wrote: Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read only* attached DBs? See section 11 of It's a 64-bit value, and

[sqlite] Wiki out of date?

Hello, I was looking at the following page on the wiki: http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence And it states that one of the causes for SQLITE_MISUSE being returned is: "Trying to use the same database connection at the same instant in time from two or more

Re: [sqlite] Limit of attached databases

Oops...make that an unsigned int. Change this declaration #if SQLITE_MAX_ATTACHED>30 typedef __uint128_t yDbMask; -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Black Sent: Tuesday, June 04, 2013 9:51 AM To:

Re: [sqlite] Limit of attached databases

Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and have a current enough gcc (I'm using 4.4.4 and this works on Linux and Windows) Looks like a fairly easy change in the code. Unless somebody already knows that this won't work? main() { __uint128_t i128;

Re: [sqlite] Limit of attached databases

On 4 Jun 2013, at 3:09pm, Eleytherios Stamatogiannakis wrote: > Is there any way to go beyond the SQLITE_MAX_ATTACHED limit for *read only* > attached DBs? See section 11 of It's a 64-bit value, and two bits are already taken up. You

[sqlite] Limit of attached databases

Hi, During our work on a distributed processing system (which uses SQLite shards), we have hit the SQLITE_MAX_ATTACHED limit of attached DBs. The way we use SQLite for distributed processing [*], is the following: - Each table is sharded into multiple SQLite DBs on different nodes of the

Re: [sqlite] Row_number?

On 6/4/2013 8:49 AM, Paxdo wrote: In fact, it is for purposes of pagination. For example, I have a web application that displays a list of customers. Each page of the list is 20 lines. It is sorted by city name. http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- Igor Tandetnik

Re: [sqlite] Row_number?

Thank you Clemens and Michael! In fact, it is for purposes of pagination. For example, I have a web application that displays a list of customers. Each page of the list is 20 lines. It is sorted by city name. If I need to display the customer N°45, I need to know its position in the list

Re: [sqlite] Row_number?

Or perhaps this is better since it is your example: sqlite> create table people(id,name); sqlite> insert into people values(5,'Chris'); sqlite> insert into people values(12,'Arthur'); sqlite> insert into people values(23,'Bill'); sqlite> insert into people values(34,'Ron'); sqlite> insert into

Re: [sqlite] Row_number?

Do you want rowid perhaps for a guaranteed one-to-one mapping to the row regardless of the query? Or are you looking for a repeatable one-up counter for the query results? select rowid,id,name from people ORDER BY name; If you want a one-up counter automagically you can create another table

Re: [sqlite] Row_number?

Paxdo wrote: > I need an equivalent to ROW_NUMBER. > > SELECT id, name FROM people ORDER BY name > > 12 Arthur > 23 Bill > 5 Chris > 34 Ron > 43 William > > The line number of the ID34 is 4. > But how to know with sqlite? Count how often you have called sqlite3_step() on the statement. What do

[sqlite] Row_number?

Hello, I have a problem and I do not find the solution with Sqlite. I need an equivalent to ROW_NUMBER. Here's the problem: SELECT id, name FROM people ORDER BY name On this, I would like to know the line number of the ID 34, how? example: SELECT id, name FROM people ORDER BY name Result: