Re: [sqlite] Covering Index?

st, where constraints and order by clause. Write performance is negatively impacted by having too many indexes. -Ursprüngliche Nachricht- Von: David de Regt [mailto:dav...@mylollc.com] Gesendet: Mittwoch, 05. Juni 2013 04:30 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Cove

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?

.org] On Behalf Of Richard Hipp > Sent: Tuesday, June 4, 2013 7:27 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Covering Index? > > On Tue, Jun 4, 2013 at 10:16 PM, David de Regt <dav...@mylollc.com> wrote: > > > Quick question, SQLites, > > &

Re: [sqlite] Covering Index?

s) -David -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, June 4, 2013 7:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Covering Index? On Tue, Jun 4, 2013 at 10:16 PM, David de

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] 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] Covering index scan optimization

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 15/09/12 18:23, Simon Slavin wrote: > PRAGMA lint_mode=ON I'm sure there is some ideal to preserve the "Lite" part of the name and a lint mode would be quite intrusive. I can imagine a separate compilation mode (eg another #define), or some

Re: [sqlite] Covering index scan optimization

On 16 Sep 2012, at 2:16am, Roger Binns wrote: > It would be *really* helpful if there was some way to put SQLite into a > mode by which developers using it can test and improve their own apps. If > their app worked in that lint/test mode they would know that the chances

Re: [sqlite] Covering index scan optimization

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 15/09/12 11:57, Richard Hipp wrote: > ... and that this change merely exposes their brokenness. I won't > dispute that. Nevertheless, with this changes, those applications will > stop working. There is the lint mode request:

Re: [sqlite] Covering index scan optimization

On 15 Sep 2012, at 7:57pm, Richard Hipp wrote: > So my thinking now is that this optimization should not be merged to trunk > unless it is first disabled by default and only enabled by a compile-time > or start-time option. How about for SQLite4 ? Simon.

Re: [sqlite] Covering index scan optimization

On 15/09/12 21:57, Richard Hipp wrote: So my thinking now is that this optimization should not be merged to trunk unless it is first disabled by default and only enabled by a compile-time or start-time option. IMHO, a pragma that enables/disables it persistently on a DB would be ideal. Many

Re: [sqlite] Covering index scan optimization

A more complete implementation of fullscan-by-covering-index can be seen here: http://www.sqlite.org/src/info/cfaa7bc128 Many of the changes were the addition of an "ORDER BY rowid" clause to test queries in the test suite. For the previous 12 years, SQLite has always returned content in

Re: [sqlite] Covering index scan optimization

I have a question. Will the covering index scan optimization also cover the automatically created indexes? So lets say that for a certain part of a query, the optimizer decides to create an automatic index. Will the same index be chosen for doing a scan (instead of a full table scan) for

Re: [sqlite] Covering index scan optimization

On 15/09/12 17:03, Simon Slavin wrote: On 15 Sep 2012, at 12:08pm, Elefterios Stamatogiannakis wrote: What i would really like to have in SQLite concerning OLAP, would be bigger pages, You can set pagesize for a new database using a PRAGMA:

Re: [sqlite] Covering index scan optimization

Simon Slavin wrote: > On 15 Sep 2012, at 12:08pm, Elefterios Stamatogiannakis > wrote: > >> What i would really like to have in SQLite concerning OLAP, would be bigger >> pages, > > You can set pagesize for a new database using a PRAGMA: > >

Re: [sqlite] Covering index scan optimization

On Fri, Sep 14, 2012 at 5:24 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > >> Is there a chance that the change will go into SQLite mainline? > > > > Not without a copyright release. > > And it may require more especially if you are an

Re: [sqlite] Covering index scan optimization

On 15 Sep 2012, at 12:08pm, Elefterios Stamatogiannakis wrote: > What i would really like to have in SQLite concerning OLAP, would be bigger > pages, You can set pagesize for a new database using a PRAGMA: The maximum

Re: [sqlite] Covering index scan optimization

On 14/09/12 22:56, Clemens Ladisch wrote: But do you have any numbers (which would help deciding whether to accept this patch)? I've run two queries on two different DBs: - DB1: Size 414M, Count of recs 2999671, the table has 17 Cols - DB2: Size 1.4G, Count of recs 1975986, the table has

Re: [sqlite] Covering index scan optimization

On 14/09/12 22:56, Clemens Ladisch wrote: Elefterios Stamatogiannakis wrote: On 13/09/12 23:02, Clemens Ladisch wrote: For my main workload (OLAP) this can make an enormous difference! OLAP isn't quite the typical SQLite use case. But do you have any numbers (which would help deciding

Re: [sqlite] Covering index scan optimization

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 >> Is there a chance that the change will go into SQLite mainline? > > Not without a copyright release. And it may require more especially if you are an employee. See the bottom section of http://www.sqlite.org/copyright.html And of course it is

Re: [sqlite] Covering index scan optimization

Elefterios Stamatogiannakis wrote: > On 13/09/12 23:02, Clemens Ladisch wrote: >> Eleytherios Stamatogiannakis wrote: >>> Is there a reason for SQLite to not use a covering index for scans? >> >> The query optimizer does not allow indexes that are not needed for some >> DISTINCT, WHERE, or ORDER

Re: [sqlite] Covering index scan optimization

On 13/09/12 23:02, Clemens Ladisch wrote: Eleytherios Stamatogiannakis wrote: It seems to me that using a covering index scan would always be faster in both cases (fewer disk page reads). Yes, if the index has fewer columns than the table. In my experience, the most frequent case is for an

Re: [sqlite] Covering index scan optimization

Eleytherios Stamatogiannakis wrote: > create table t (c1,c2, c3, c4); > create index idxtc1 on t(c1); > > explain query plan select c1 from t; > SCAN TABLE t (~100 rows) > > explain query plan select c1 from t order by c1; > SCAN TABLE t USING COVERING INDEX idxtc1 (~100 rows)

[sqlite] Covering index scan optimization

Hello, I've just wanted to ask about using covering indexes for scans. A very rudimentary test: create table t (c1,c2, c3, c4); create index idxtc1 on t(c1); The simple "select" scans the full table: explain query plan select c1 from t; SCAN TABLE t (~100 rows) A select with a