Re: [sqlite] aggregate by break in sequence

[Igor Tandetnik]

On 8/16/2011 4:58 PM, Anantha Prasad wrote:
> The data is sorted by Longitude and then by Distance. Whenever there are
> repeated values of Distance within a Longitude, I want the Sum and Count to
> be added and divided

select Longitude, Distance, sum(SumColz) / sum(CountColz) as AvgColz
from my_table group by Longitude, Distance;

If you want to replace the original table with this processed data, you 
can do it in several steps:

1) Create a new table to hold the result.
2) Use INSERT INTO new_table  SELECT ... statement to save the result of 
the aforementioned select.
3) Drop the old table.
4) Use ALTER TABLE statement to rename the new table to the old name.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select distinct

[Simon Slavin]

On 16 Aug 2011, at 10:05pm, Yonnas Beyene wrote:

> SELECT distinct Table1.Column1, Table2.Column2,  Table1.Column2
> FROM ( Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column1)
> WHERE ( Table1.PrimaryKeyID =?) And ( Table1.Column1 IS NOT NULL)
> 
> The explain query plan looks like,
> 
> 0 0 0 SEARCH TABLE Table1 USING AUTOMATIC COVERING INDEX (PrimaryKeyID=?)
> (~7 rows)
> 0 1 1 SEARCH TABLE Table2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0 0 0 USE TEMP B-TREE FOR DISTINCT
> 
> without the distinct this query runs very fast.  would adding an index on
> all columns used in the select will speed this query?

I don't know what indexes you already have on those tables, but you should 
definitely be able to find an index on each table that makes that SELECT run 
faster.  I would try something like

CREATE INDEX T1PKIC1C2 on Table1 (PrimaryKeyID, Column1, Column2)
CREATE INDEX T2C1C2 on Table2 (Column1, Column2)

and see if they help.  If so, you might be able to find better or worse ones.  
If they make no difference at all, please post again.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate by break in sequence

[Simon Slavin]

On 16 Aug 2011, at 9:58pm, Anantha Prasad wrote:

> The data is sorted by Longitude and then by Distance. Whenever there are
> repeated values of Distance within a Longitude, I want the Sum and Count to
> be added and divided - for example,
> Here is the table:
> Longitude Distance  AvgColz  SumColz CountColz
> 71.0  1050.   1002
> 71.0  1092.8571   2600   28
> 71.0  2083.8710   5200   62
> 71.0  2082.6389   11900  144
> 71.0  3062.0425   2916   47
> 71.0  3081.7119   9642   118
> 71.0  4017.8723   84047
> 71.0  4049.8868   2644   53
> 71.0  508.581436943
> 71.0  605.023821142

This is manipulation of data, and the way you want it done is not going to be 
the way the next programmer is going to want it done.  Whatever kind of 
averaging or interpolation you want done, do it in your programming language, 
either by pre-scanning your database and replacing what you find, or by writing 
your own sum and count routines which understand what you want done.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] select distinct

[Yonnas Beyene]

In the process of converting database from sql express to sqlite, I have the
following query that runs very slow on sqlite compared to sql server:

SELECT distinct Table1.Column1, Table2.Column2,  Table1.Column2
FROM ( Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column1)
WHERE ( Table1.PrimaryKeyID =?) And ( Table1.Column1 IS NOT NULL)

The explain query plan looks like,

0 0 0 SEARCH TABLE Table1 USING AUTOMATIC COVERING INDEX (PrimaryKeyID=?)
(~7 rows)
0 1 1 SEARCH TABLE Table2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0 0 0 USE TEMP B-TREE FOR DISTINCT

without the distinct this query runs very fast.  would adding an index on
all columns used in the select will speed this query?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate by break in sequence

[Anantha Prasad]

Thanks much for the responses - they were helpful but was not quite what i
wanted. Perhaps I was not clear. So, here goes...

The data is sorted by Longitude and then by Distance. Whenever there are
repeated values of Distance within a Longitude, I want the Sum and Count to
be added and divided - for example,
Here is the table:
Longitude Distance  AvgColz  SumColz CountColz
71.0  1050.   1002
71.0  1092.8571   2600   28
71.0  2083.8710   5200   62
71.0  2082.6389   11900  144
71.0  3062.0425   2916   47
71.0  3081.7119   9642   118
71.0  4017.8723   84047
71.0  4049.8868   2644   53
71.0  508.581436943
71.0  605.023821142
71.0  702.930212643
71.0  802.170789 41
71.0  901.850074 40
71.0  100   1.575063 40

Notice that the Distance is repeated for the Longitude...I want the repeated
Distance rows to be replaced by a weighted average of SumColz and CountColz.

So, the output should be:

Longitude Distance AvgColz
71.0   10   90.00
71.0   20   83.01
71.0   30   76.11
71.0   40   34.84
71.0   50   8.58
71.0   60   5.02
71.0   70   2.93
71.0   80   2.17
71.0   90   1.85
71.0   100  1.57


Example the 90.00 in the first row above is (100+2600)/(2+28) and so
on...there could be multiple repeats in Distance although I have shown only
two here...

Thanks much!!
Prasad



On Tue, Aug 16, 2011 at 12:35 PM, Pavel Ivanov  wrote:

> I think it will have better performance if you do that in your
> programming language. But if you insist on SQL it would look like
> this:
>
> update table_name set IVmean =
> (select sum(IVsum)/sum(IVcount) from table_name t
> where t.Longitude = table_name.Longitude
> and t.Distance = table_name.Distance);
>
> I intentionally made all rows to be updated because adding WHERE
> condition will most probably only slowdown the update.
>
>
> Pavel
>
>
> On Tue, Aug 16, 2011 at 12:05 PM, Anantha Prasad  wrote:
> > Wanted to know if Sqlite can do simple math when there is a break in
> > sequence in the data. For example the foll. table is sorted by Longitude
> and
> > then Distance:
> >
> > Id  Longitude Distance IVmean  IVsum IVcount
> > 42 71.0 10  10.5000   221
> > 43 71.0 10   29.4286  28  824
> > 44 71.0 20   9.467762  587
> > 45 71.0 20   11.6667  1441680
> > 46 71.0 30   3.553247  167
> > 47 71.0 40   4.573249   67
> >
> > I want to compute replace the IVmean for each repeated Distance by
> > IVsum/IVcount - for repeated Distance - for example,
> >
> > Record 42 and 44 should be replaced by
> > 71.0  10  (2+28)/(21+824)
> > Record 44 and 45 should be replaced by
> > 71.0   20 (62+144)/(587+1680)
> >
> > Is this possible to do this in Sqlite.
> >
> > Thanks much.
> > Pras
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search the archives for [MEMO]?

[Dave Jeremy]

On 8/16/2011 12:12 PM, Luuk wrote:
> On 16-08-2011 20:15, Dave Jeremy wrote:
>> Hi,
>>
>> New to SQLite3 and am I using Delphi-5 and Zeos.
>>
>> I created a database using the Text DataType but it shows up in a TDBGrid as
>> [memo].
>>
>> I have read that I need to change the DataType to VarChar without a length
>> designation and the text will show correctly.
>>
>> I am sure this has been asked a thousand times already so how can I search
>> the archives?
>>
>> Dave
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> see:
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg32024.html

Thanks

Dave

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search the archives for [MEMO]?

[Luuk]

On 16-08-2011 20:15, Dave Jeremy wrote:
> Hi,
> 
> New to SQLite3 and am I using Delphi-5 and Zeos.
> 
> I created a database using the Text DataType but it shows up in a TDBGrid as
> [memo].
> 
> I have read that I need to change the DataType to VarChar without a length
> designation and the text will show correctly.
> 
> I am sure this has been asked a thousand times already so how can I search
> the archives?
> 
> Dave
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

see:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg32024.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to search the archives for [MEMO]?

[Dave Jeremy]

Hi,

New to SQLite3 and am I using Delphi-5 and Zeos.

I created a database using the Text DataType but it shows up in a TDBGrid as
[memo].

I have read that I need to change the DataType to VarChar without a length
designation and the text will show correctly.

I am sure this has been asked a thousand times already so how can I search
the archives?

Dave
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate by break in sequence

[Pavel Ivanov]

I think it will have better performance if you do that in your
programming language. But if you insist on SQL it would look like
this:

update table_name set IVmean =
(select sum(IVsum)/sum(IVcount) from table_name t
where t.Longitude = table_name.Longitude
and t.Distance = table_name.Distance);

I intentionally made all rows to be updated because adding WHERE
condition will most probably only slowdown the update.


Pavel


On Tue, Aug 16, 2011 at 12:05 PM, Anantha Prasad  wrote:
> Wanted to know if Sqlite can do simple math when there is a break in
> sequence in the data. For example the foll. table is sorted by Longitude and
> then Distance:
>
> Id  Longitude Distance IVmean  IVsum IVcount
> 42 71.0         10          10.5000   2        21
> 43 71.0         10           29.4286  28      824
> 44 71.0         20           9.4677    62      587
> 45 71.0         20           11.6667  144    1680
> 46 71.0         30           3.5532    47      167
> 47 71.0         40           4.5732    49       67
>
> I want to compute replace the IVmean for each repeated Distance by
> IVsum/IVcount - for repeated Distance - for example,
>
> Record 42 and 44 should be replaced by
> 71.0  10  (2+28)/(21+824)
> Record 44 and 45 should be replaced by
> 71.0   20 (62+144)/(587+1680)
>
> Is this possible to do this in Sqlite.
>
> Thanks much.
> Pras
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] aggregate by break in sequence

[Jim Morris]

You can't replace multiple rows in a single insert/update/delete statement.
You might consider copying the duplicates to a temp table, delete them 
from the old then use a select on the temp table to generate the new 
rows for the old table.  The select portion would be something like
select longitude, distance, SUM(IVSUM)/SUM(IVCount), SUM(IVSUM), 
SUM(IVCount) FROM  GROUP By longitude, Distance.

On 8/16/2011 9:05 AM, Anantha Prasad wrote:
> Wanted to know if Sqlite can do simple math when there is a break in
> sequence in the data. For example the foll. table is sorted by Longitude and
> then Distance:
>
> Id  Longitude Distance IVmean  IVsum IVcount
> 42 71.0 10  10.5000   221
> 43 71.0 10   29.4286  28  824
> 44 71.0 20   9.467762  587
> 45 71.0 20   11.6667  1441680
> 46 71.0 30   3.553247  167
> 47 71.0 40   4.573249   67
>
> I want to compute replace the IVmean for each repeated Distance by
> IVsum/IVcount - for repeated Distance - for example,
>
> Record 42 and 44 should be replaced by
> 71.0  10  (2+28)/(21+824)
> Record 44 and 45 should be replaced by
> 71.0   20 (62+144)/(587+1680)
>
> Is this possible to do this in Sqlite.
>
> Thanks much.
> Pras
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] aggregate by break in sequence

[Anantha Prasad]

Wanted to know if Sqlite can do simple math when there is a break in
sequence in the data. For example the foll. table is sorted by Longitude and
then Distance:

Id  Longitude Distance IVmean  IVsum IVcount
42 71.0 10  10.5000   221
43 71.0 10   29.4286  28  824
44 71.0 20   9.467762  587
45 71.0 20   11.6667  1441680
46 71.0 30   3.553247  167
47 71.0 40   4.573249   67

I want to compute replace the IVmean for each repeated Distance by
IVsum/IVcount - for repeated Distance - for example,

Record 42 and 44 should be replaced by
71.0  10  (2+28)/(21+824)
Record 44 and 45 should be replaced by
71.0   20 (62+144)/(587+1680)

Is this possible to do this in Sqlite.

Thanks much.
Pras
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improving the query optimizer

[Simon Slavin]

On 16 Aug 2011, at 1:54pm, Filip Navara wrote:

> On Tue, Aug 16, 2011 at 2:24 PM, Simon Slavin  wrote:
>> 
>> On 16 Aug 2011, at 9:39am, Filip Navara wrote:
>> 
>>> Full-table scan is done even if an index exists that covers all the
>>> data required by the query.
>> 
>> That's a little strange.
>> 
>> Please execute the SQL command ANALYZE on that database.  Then do your 
>> 'EXPLAIN' tests again.  It won't fix the problem but it'll tell us something 
>> useful.
>> 
>> http://www.sqlite.org/lang_analyze.html
> 
> It doesn't fix anything. It's not even possible to force the index
> usage using INDEXED BY.

You know, I wasn't thinking.  I thought you were doing a query which selected 
on a,d or used them in an ORDER BY clause.  I can see now why SQLite would not 
consider optimizing by using your index.  However, I can also see your point 
that using such optimization would save considerable time.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.7.8

[Filip Navara]

On Tue, Aug 16, 2011 at 2:19 PM, Richard Hipp  wrote:
> On Tue, Aug 16, 2011 at 5:37 AM, Filip Navara wrote:
>
>> Is version 3.7.8 scheduled for September 28 (according to changes.in)
>> or August 28 (bi-monthly schedule)?
>>
>> Current target release date is 2011-09-28.  But that might change.
> Meanwhile, the trunk is stable if you want to use it.

I guess we will use that then. We are specifically interested in the
changes from the merge sort branch to speed up our next database
schema update.

Thanks,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improving the query optimizer

[Filip Navara]

On Tue, Aug 16, 2011 at 2:54 PM, Filip Navara  wrote:
> On Tue, Aug 16, 2011 at 2:24 PM, Simon Slavin  wrote:
>>
>> On 16 Aug 2011, at 9:39am, Filip Navara wrote:
>>
>>> create table a (a, b, c, d);
>>> create index aIdx on a (a, d);
>>>
>>> Now the data in columns "b" and "c" can be pretty large. Let's make
>>> the following query:
>>>
>>> select a, d from a;
>>>
>>> Full-table scan is done even if an index exists that covers all the
>>> data required by the query.
>>
>> That's a little strange.
>>
>> Please execute the SQL command ANALYZE on that database.  Then do your 
>> 'EXPLAIN' tests again.  It won't fix the problem but it'll tell us something 
>> useful.
>>
>> http://www.sqlite.org/lang_analyze.html
>
> It doesn't fix anything. It's not even possible to force the index
> usage using INDEXED BY.
>

I forgot to attach the data, sorry. These are the real queries on the
actual database data.

attach 'mail_index.dat' as mail_index;
attach 'folders.dat' as folders;

-- Schema --

CREATE TABLE mail_index."MailItems" (
"id" INTEGER NOT NULL PRIMARY KEY,
"uniqueId",
"versionId",
"syncFlags" INTEGER,
"folder" INTEGER,
"syncFolder" INTEGER,
"date" TIMESTAMP,
"subject" TEXT,
"inReplyTo" TEXT,
"messageId" TEXT,
"importance" INTEGER,
"account" TEXT,
"flags" INTEGER,
"type" INTEGER,
"size" INTEGER,
"editTime" INTEGER,
"operationsPerformed" INTEGER,
"receivedDate" TIMESTAMP,
"replyDate" TIMESTAMP,
"forwardDate" TIMESTAMP, "references" TEXT);
CREATE INDEX mail_index."idx_MailItems" ON "MailItems" ("folder", "id");
CREATE INDEX mail_index."idx_MailItems_accountAndFlags" ON "MailItems"
("account", "flags");
CREATE INDEX mail_index."idx_MailItems_flags" ON "MailItems"
("folder", "flags", "syncFlags");
CREATE INDEX mail_index."idx_MailItems_folderAndAccountAndFlags" ON
"MailItems" ("folder", "account", "flags");
CREATE INDEX mail_index."idx_MailItems_uniqueId" ON "MailItems"
("folder", "syncFolder", "uniqueId");

CREATE TABLE folders."Folders" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"uniqueId",
"versionId",
"flags" INTEGER DEFAULT 0,
"name" TEXT,
"delimiter" CHAR DEFAULT '/',
"path" TEXT COLLATE NOCASE,
"parentFolderId" INTEGER);
CREATE INDEX folders.idx_Folders_idAndFlags ON "Folders" ("id", "flags");
CREATE INDEX folders.idx_Folders_parentAndFlags ON "Folders"
("parentFolderId", "flags");
CREATE UNIQUE INDEX folders.idx_Folders_path ON "Folders" ("path");

-- Before ANALYZE --

SELECT COUNT(*) FROM (SELECT id FROM mail_index.MailItems WHERE NOT
("flags" & 2) AND NOT ("flags" & 65536) AND "folder" not in
(32,301,140,160,187,185,273,270,281,478,481,7,6) AND  "folder" not in
(SELECT "id" FROM folders.Folders WHERE ("flags" & 8)));
155963

-- Plain query
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND  "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)));
0|0|0|SCAN TABLE MailItems (~62500 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE Folders (~50 rows)

-- Added ORDER BY
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND  "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)) ORDER BY "folder");
1|0|0|SCAN TABLE MailItems USING COVERING INDEX
idx_MailItems_folderAndAccountAndFlags (~62500 rows)
1|0|0|EXECUTE LIST SUBQUERY 2
1|0|0|EXECUTE LIST SUBQUERY 2
2|0|0|SCAN TABLE Folders (~50 rows)
0|0|0|SCAN SUBQUERY 1 (~62500 rows)

-- Added "folder" > 0
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND  "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)) AND "folder" > 0);
0|0|0|SEARCH TABLE MailItems USING COVERING INDEX
idx_MailItems_folderAndAccountAndFlags (folder>?) (~15625 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE Folders (~50 rows)

-- After ANALYZE --

-- Plain query
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND  "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)));
0|0|0|SCAN TABLE MailItems (~10986 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SCAN TABLE Folders (~167 rows)

-- Added ORDER BY
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM (SELECT id FROM
mail_index.MailItems WHERE NOT ("flags" & 2) AND NOT ("flags" & 65536)
AND "folder" not in (32,301,140,160,187,185,273,270,281,478,481,7,6)
AND  "folder" not in (SELECT "id" FROM folders.Folders WHERE ("flags"
& 8)) ORDER BY "folder");
1|0|0|SCAN TABLE MailItems USING COVERING INDEX 

Re: [sqlite] Improving the query optimizer

[Filip Navara]

On Tue, Aug 16, 2011 at 2:56 PM, Richard Hipp  wrote:
> On Tue, Aug 16, 2011 at 8:54 AM, Filip Navara wrote:
>
>> On Tue, Aug 16, 2011 at 2:24 PM, Simon Slavin 
>> wrote:
>> >
>> > On 16 Aug 2011, at 9:39am, Filip Navara wrote:
>> >
>> >> create table a (a, b, c, d);
>> >> create index aIdx on a (a, d);
>> >>
>> >> Now the data in columns "b" and "c" can be pretty large. Let's make
>> >> the following query:
>> >>
>> >> select a, d from a;
>> >>
>> >> Full-table scan is done even if an index exists that covers all the
>> >> data required by the query.
>> >
>> > That's a little strange.
>> >
>> > Please execute the SQL command ANALYZE on that database.  Then do your
>> 'EXPLAIN' tests again.  It won't fix the problem but it'll tell us something
>> useful.
>> >
>> > http://www.sqlite.org/lang_analyze.html
>>
>> It doesn't fix anything. It's not even possible to force the index
>> usage using INDEXED BY.
>>
>
> Force the covering index to be used by adding an ORDER BY clause:
>
>     SELECT a, d FROM a ORDER BY a, d;
>
> Run the experiment.  Does that make the query go any faster?

Yes, for the particular query it cuts down the time from 33 seconds to
0.4 seconds.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improving the query optimizer

[Richard Hipp]

On Tue, Aug 16, 2011 at 8:54 AM, Filip Navara wrote:

> On Tue, Aug 16, 2011 at 2:24 PM, Simon Slavin 
> wrote:
> >
> > On 16 Aug 2011, at 9:39am, Filip Navara wrote:
> >
> >> create table a (a, b, c, d);
> >> create index aIdx on a (a, d);
> >>
> >> Now the data in columns "b" and "c" can be pretty large. Let's make
> >> the following query:
> >>
> >> select a, d from a;
> >>
> >> Full-table scan is done even if an index exists that covers all the
> >> data required by the query.
> >
> > That's a little strange.
> >
> > Please execute the SQL command ANALYZE on that database.  Then do your
> 'EXPLAIN' tests again.  It won't fix the problem but it'll tell us something
> useful.
> >
> > http://www.sqlite.org/lang_analyze.html
>
> It doesn't fix anything. It's not even possible to force the index
> usage using INDEXED BY.
>

Force the covering index to be used by adding an ORDER BY clause:

 SELECT a, d FROM a ORDER BY a, d;

Run the experiment.  Does that make the query go any faster?


>
> Best regards,
> Filip Navara
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improving the query optimizer

[Filip Navara]

On Tue, Aug 16, 2011 at 2:24 PM, Simon Slavin  wrote:
>
> On 16 Aug 2011, at 9:39am, Filip Navara wrote:
>
>> create table a (a, b, c, d);
>> create index aIdx on a (a, d);
>>
>> Now the data in columns "b" and "c" can be pretty large. Let's make
>> the following query:
>>
>> select a, d from a;
>>
>> Full-table scan is done even if an index exists that covers all the
>> data required by the query.
>
> That's a little strange.
>
> Please execute the SQL command ANALYZE on that database.  Then do your 
> 'EXPLAIN' tests again.  It won't fix the problem but it'll tell us something 
> useful.
>
> http://www.sqlite.org/lang_analyze.html

It doesn't fix anything. It's not even possible to force the index
usage using INDEXED BY.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Improving the query optimizer

[Simon Slavin]

On 16 Aug 2011, at 9:39am, Filip Navara wrote:

> create table a (a, b, c, d);
> create index aIdx on a (a, d);
> 
> Now the data in columns "b" and "c" can be pretty large. Let's make
> the following query:
> 
> select a, d from a;
> 
> Full-table scan is done even if an index exists that covers all the
> data required by the query.

That's a little strange.

Please execute the SQL command ANALYZE on that database.  Then do your 
'EXPLAIN' tests again.  It won't fix the problem but it'll tell us something 
useful.

http://www.sqlite.org/lang_analyze.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.7.8

[Richard Hipp]

On Tue, Aug 16, 2011 at 5:37 AM, Filip Navara wrote:

> Is version 3.7.8 scheduled for September 28 (according to changes.in)
> or August 28 (bi-monthly schedule)?
>
> Current target release date is 2011-09-28.  But that might change.
Meanwhile, the trunk is stable if you want to use it.



> Thanks,
> Filip Navara
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] forcing X'' literals in sqlite3's .dump?

[Ivan Shmakov]

In the sqlite3's .dump command's output, the binary blobs may
either be represented as hexadecimal X''-literals, or as text
strings.

I wonder, how do I force sqlite3(1) to exclusively use the X''
representation?

Also, are the .dump and .read commands implemented as part of
the sqlite3 binary, or are they part of the library?

TIA.

-- 
FSF associate member #7257

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using dot commands with the command line binary

[Brian Curley]

Use of a heredoc to simulate a session...or staging it all into a file for
use via .read are good too.
On Aug 16, 2011 6:55 AM, "Ryan Henrie"  wrote:
> I finally figured out how to load multiple "dot commands" or settings
> from the command line tool. (Some users only have the default binary to
> rely on, ya know.) Since I have never found this information on the web
> before, I thought I would post it here to share the information.
>
> To load options before doing the query, they have to be separated from
> the query by a hard return, in the quoted string itself.
>
> So:
>
> > sqlite3 -header -column tmp.db ".width 5 30; select * from data;"
>
> doesn't really change the column widths.
>
> But, doing this does:
>
> > echo ".width 5 30\n select * from data;" | sqlite3 -header -column
tmp.db
>
> To load multiple options, you have to put them on different lines, with
> the query on the last line:
>
> > echo ".width 5 30\n.timeout 15000\n select * from data;" | sqlite3
> -header -column tmp.db
>
> Some systems I have tried this on /required /only a single hard return
> after the last option, with a semicolon between the multiple settings.
> Others required the hard returns without any semicolons. Experiment on
> your own platform/binary version.
>
> I don't know which versions/flavors this works on, but it works for me
> finally.
>
> Note: Your version of echo has to support turning '\n' into a hard
> return. Not all do. If not, you can do something like a perl -e to do
> it as well.
>
> All the examples on websites show applying the dot commands from a
> sqlite> prompt. Doing the hard returns within the string makes it look
> like a user typing the commands in, with the hard returns.
>
> If anyone could fix the parser in the sqlite3 source code, I'm sure many
> novices would greatly appreciate it. The above trick has eluded me for
> several months of working with sqlite3 from the command line, and is
> still cumbersome, although usable now.
>
> One other trick is to put the options in a .sqliterc file in your home
> directory, one dot command per line. This works, but is not really
> feasible in an environment where it will run on multiple hosts and you
> don't control other accounts that will run it. Also, you can't pick and
> choose which ones are loaded. They are global settings at that point.
>
> I hope this helps someone out there looking for this, as well as
> possibly getting the parser fixed to make it easier to use in the future.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using dot commands with the command line binary

[Ryan Henrie]

I finally figured out how to load multiple "dot commands" or settings 
from the command line tool. (Some users only have the default binary to 
rely on, ya know.) Since I have never found this information on the web 
before, I thought I would post it here to share the information.

To load options before doing the query, they have to be separated from 
the query by a hard return, in the quoted string itself.

So:

 > sqlite3 -header -column tmp.db ".width 5 30; select * from data;"

doesn't really change the column widths.

But, doing this does:

 > echo ".width 5 30\n select * from data;" | sqlite3 -header -column tmp.db

To load multiple options, you have to put them on different lines, with 
the query on the last line:

 > echo ".width 5 30\n.timeout 15000\n select * from data;" | sqlite3 
-header -column tmp.db

Some systems I have tried this on /required /only a single hard return 
after the last option, with a semicolon between the multiple settings. 
Others required the hard returns without any semicolons. Experiment on 
your own platform/binary version.

I don't know which versions/flavors this works on, but it works for me 
finally.

Note: Your version of echo has to support turning '\n' into a hard 
return. Not all do.  If not, you can do something like a perl -e to do 
it as well.

All the examples on websites show applying the dot commands from a 
sqlite> prompt.  Doing the hard returns within the string makes it look 
like a user typing the commands in, with the hard returns.

If anyone could fix the parser in the sqlite3 source code, I'm sure many 
novices would greatly appreciate it.  The above trick has eluded me for 
several months of working with sqlite3 from the command line, and is 
still cumbersome, although usable now.

One other trick is to put the options in a .sqliterc file in your home 
directory, one dot command per line.  This works, but is not really 
feasible in an environment where it will run on multiple hosts and you 
don't control other accounts that will run it. Also, you can't pick and 
choose which ones are loaded. They are global settings at that point.

I hope this helps someone out there looking for this, as well as 
possibly getting the parser fixed to make it easier to use in the future.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Version 3.7.8

[Filip Navara]

Is version 3.7.8 scheduled for September 28 (according to changes.in)
or August 28 (bi-monthly schedule)?

Thanks,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query optimizer and foreign keys

[Filip Navara]

Hello!

I'm seeking some advice on improving SQLite's query planning in
relation to foreign keys. Sometimes it may be useful to exploit the
fact that the columns in separate tables refer to the same
information.

Consider the following schema:

create table a (a, b, c);
create table b (a, b REFERENCES a(b), c);
create index bIdx on b(b);

It may be worthwhile to use the foreign key references to append the
"b.b=a.b" clause to queries spanning multiple tables:

explain query plan select a, b, c from a where b in (select b from b
where c='');
0|0|TABLE a
0|0|TABLE b WITH AUTOMATIC INDEX

explain query plan select a, b, c from a where b in (select b from b
where c='' and b.b=a.b);
0|0|TABLE a
0|0|TABLE b WITH INDEX bIdx

The second query plan could prove to be more useful if the number of
rows selected from table "a" is small and number of rows in table "b"
is big. Each lookup into table "b" is log(n) in this case, where n is
the number of rows in table "b". Obviously adding the clause can prove
to be useful in certain queries and not useful in others depending on
the result set sizes, but I believe that it is possible to count the
estimates and choose the better strategy.

Sometimes the foreign key information could be used to skip the lookup
in the original table altogether. Let's look at the following query:

select b from a where b in (select b from b where c='');

As long as a.b = b.b the query could actually be rewritten as:

select b from b where c='';

What use cases could you think of that would benefit from use of
foreign key information in query planning? What caveats could you
think of?

Thanks,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Improving the query optimizer

[Filip Navara]

Hello!

Over the last few weeks we were profiling our usage of SQLite. We
found three common patterns that are not treated well by the
optimizer. I believe at least two of them can be fixed easily and they
are included below.

1) Consider the following schema:

create table a (a, b, c, d);
create index aIdx on a (a, d);

Now the data in columns "b" and "c" can be pretty large. Let's make
the following query:

select a, d from a;

Full-table scan is done even if an index exists that covers all the
data required by the query. This is a very simplified example of a
general problem where the query contains several conditions in the
WHERE query, but none of them can be answered by lookup in the index.
A covering index exists though and using it causes much less data to
be loaded from the disk.

explain query plan select a, d from a;
0|0|0|SCAN TABLE a (~100 rows)
explain query plan select a, d from a where a >= 0 or a < 0;
0|0|0|SEARCH TABLE a USING COVERING INDEX aIdx (a>?) (~25 rows)
0|0|0|SEARCH TABLE a USING COVERING INDEX aIdx (a explain query plan select a from a where a='test' AND a IS NOT NULL;
0|0|TABLE a WITH INDEX aIdx

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Invitation to connect on LinkedIn

[daisy zhang via LinkedIn]

LinkedIn





daisy zhang requested to add you as a connection on LinkedIn:
  
--

Zarko,

I'd like to add you to my professional network on LinkedIn.

- daisy

Accept invitation from daisy zhang
http://www.linkedin.com/e/-62mihx-grek4qlr-15/BuxMMT0h2nypvIvNsoWseeA2ULM8vQLhJFCC/blk/I90534555_60/pmpxnSRJrSdvj4R5fnhv9ClRsDgZp6lQs6lzoQ5AomZIpn8_c3pvdjkRd3cRc3B9bQpah5Z9m7tebPcPcjkRc34Te3cLrCBxbOYWrSlI/EML_comm_afe/

View invitation from daisy zhang
http://www.linkedin.com/e/-62mihx-grek4qlr-15/BuxMMT0h2nypvIvNsoWseeA2ULM8vQLhJFCC/blk/I90534555_60/0MdBYRdjkQcPkMekALqnpPbOYWrSlI/svi/

--

Why might connecting with daisy zhang be a good idea?

Have a question? daisy zhang's network will probably have an answer:
You can use LinkedIn Answers to distribute your professional questions to daisy 
zhang and your extended network. You can get high-quality answers from 
experienced professionals.

http://www.linkedin.com/e/-62mihx-grek4qlr-15/ash/inv19_ayn/
 
-- 
(c) 2011, LinkedIn Corporation
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users