date:20130116

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

2013-01-16 Thread Dan Kennedy


On 01/16/2013 02:21 PM, Ashok Pitambar wrote:

Hi All,

 I have used sqlite for Database in my client , to speed up the
performance
I used *"begin"(BEGIN;) *and *"end"(END;)* query transactions to
include multiple
transactions in single transaction. This helped in performance by reducing
the time
taken to write all the transactions but when I used this for some of the DB
operations
sqlite throws error "unable to open database file".If I remove the changes
it works
fine.What could be the problem here?


It might be failing to create a temporary file to use for a
statement journal.

  http://www.sqlite.org/pragma.html#pragma_temp_store
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about delete problem

2013-01-16 Thread YAN HONG YE

create table mytable( ID integer primary key autoincrement, name nvarchar(32) );
insert into mytable(name) values("aa1");
insert into mytable(name) values("aa2");
insert into mytable(name) values("aa3");
insert into mytable(name) values("aa4");
insert into mytable(name) values("aa5");
insert into mytable(name) values("aa6");
delete from mytable where id>3;
insert into mytable(name) values("aa7");
insert into mytable(name) values("aa8");
select * from mytable;
1|aa1
2|aa2
3|aa3
7|aa7
8|aa8

I wish after I  delete any rows from mytable, and then when I insert into any 
row into mytable, The Id should follow the last exists in mytable.
in the sample sql, when I delete any rows in mytable,the last id is 3 ,  and 
then when I add any column into mytable, the ID should from 3 to 
the next column, how to do this? maybe have any rubbish in the database when 
delete from mytable, how to clean that rubbish?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about delete problem

2013-01-16 Thread Simon Davies

On 16 January 2013 08:16, YAN HONG YE  wrote:
> create table mytable( ID integer primary key autoincrement, name nvarchar(32) 
> );
> insert into mytable(name) values("aa1");
> insert into mytable(name) values("aa2");
> insert into mytable(name) values("aa3");
> insert into mytable(name) values("aa4");
> insert into mytable(name) values("aa5");
> insert into mytable(name) values("aa6");
> delete from mytable where id>3;
> insert into mytable(name) values("aa7");
> insert into mytable(name) values("aa8");
> select * from mytable;
> 1|aa1
> 2|aa2
> 3|aa3
> 7|aa7
> 8|aa8
>
> I wish after I  delete any rows from mytable, and then when I insert into any 
> row into mytable, The Id should follow the last exists in mytable.
> in the sample sql, when I delete any rows in mytable,the last id is 3 ,  and 
> then when I add any column into mytable, the ID should from 3 to
> the next column, how to do this? maybe have any rubbish in the database when 
> delete from mytable, how to clean that rubbish?

Try:

create table mytable( ID integer primary key, name nvarchar(32) );

and read up on "integer primary key" and "autoincrement" on the SQLite web-site

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

Re: [sqlite] about delete problem

2013-01-16 Thread Hick Gunter

There is no easy and efficient way to do this. Autoincrement needs to store 
only the last value used (or the next value to use). 63 bits of unique IDs 
should be enough.

You may try the following, but it is probably rather expensive.

INSERT INTO mytable (SELECT max(id)+1, FROM mytable);

Also, what should the behavior be if you delete a "middle" id? Should it be 
reused? If so, when? Why do you require ids to be contiguous? Are you trying to 
adapt an algorithm based on an in-memory array?

-Ursprüngliche Nachricht-
Von: YAN HONG YE [mailto:yanhong...@mpsa.com]
Gesendet: Mittwoch, 16. Jänner 2013 09:17
An: sqlite-users@sqlite.org
Betreff: [sqlite] about delete problem

create table mytable( ID integer primary key autoincrement, name nvarchar(32) 
); insert into mytable(name) values("aa1"); insert into mytable(name) 
values("aa2"); insert into mytable(name) values("aa3"); insert into 
mytable(name) values("aa4"); insert into mytable(name) values("aa5"); insert 
into mytable(name) values("aa6"); delete from mytable where id>3; insert into 
mytable(name) values("aa7"); insert into mytable(name) values("aa8"); select * 
from mytable;
1|aa1
2|aa2
3|aa3
7|aa7
8|aa8

I wish after I  delete any rows from mytable, and then when I insert into any 
row into mytable, The Id should follow the last exists in mytable.
in the sample sql, when I delete any rows in mytable,the last id is 3 ,  and 
then when I add any column into mytable, the ID should from 3 to the next 
column, how to do this? maybe have any rubbish in the database when delete from 
mytable, how to clean that rubbish?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Selen Schabenberger

I attach a small database where it is possible to reproduce the issue. I 
deleted all irrelevant tables and all the tuples in the Message table to keep 
the file size small but had run ANALYZE before doing that.

This is the query to reproduce with 3.7.15.2:
EXPLAIN QUERY PLAN

SELECT * FROM message
WHERE tag IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
) AND flag=1
ORDER BY id LIMIT 200;

I get this result:
selectId   order  from   detail                        

0  0  0  SCAN TABLE Message USING INTEGER PRIMARY KEY 
(~4601 rows)
0  0  0  EXECUTE LIST SUBQUERY 1   


Hope someone can help.


- Selen




 From: Selen Schabenberger 
To: General Discussion of SQLite Database ; Richard 
Hipp  
Sent: Wednesday, January 2, 2013 12:22 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator
 
Hi Richard,
I tested the whole scenario one more time with the new SQLite version. As you 
suggested I put a plus sign in front of the Flag column and that really made 
the query much faster by using the multi column index (Tag, Flag, Id) instead 
of the primary index on the Id column. However what I don't get is, I actually 
had removed that single column index on the Flag before and run ANALZE. How 
come the query optimizer makes another decision when I put a + in front of a 
column which is not indexed alone?
Is there another way to improve this query, other than using the + sign? I 
would really appreciate any suggestions.
Happy new year!
Regards,Selen

--- On Fri, 12/14/12, Richard Hipp  wrote:

From: Richard Hipp 
Subject: Re: [sqlite] Multi-column index is not used with IN operator
To: "Selen Schabenberger" , "General Discussion of 
SQLite Database" 
Date: Friday, December 14, 2012, 3:09 PM



On Thu, Dec 13, 2012 at 10:06 AM, Selen Schabenberger  
wrote:


Hi All,



I am observing some strange behaviour on my database when I execute a query 
with an IN operator having more than "22" expressions. My table structure looks 
basically as follows:



CREATE TABLE "Messages" ("Id" INTEGER PRIMARY KEY NOT NULL, "Tag" INTEGER NOT 
NULL, "Flag" INTEGER )





I have a multi-column index on  (Tag, Flag, Id) as well as a single column 
index on the Flag column.

My guess is that the single-column index on Flag is misleading the query 
optimizer.  You can probably fix this by either (1) running ANALYZE or (2) 
adding a "+" in front of the "Flag" column name in the WHERE clause of your 
query, like this:   "... +Flag=1 ..."



-- 
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

2013-01-16 Thread Ashok Pitambar

Thanks Dan,

I will try by setting this pragma but still I don't understand why it is
failing for few query operations.

Regards,
Ashok



On Wed, Jan 16, 2013 at 1:46 PM, Dan Kennedy  wrote:

> On 01/16/2013 02:21 PM, Ashok Pitambar wrote:
>
>> Hi All,
>>
>>  I have used sqlite for Database in my client , to speed up
>> the
>> performance
>> I used *"begin"(BEGIN;) *and *"end"(END;)* query transactions to
>>
>> include multiple
>> transactions in single transaction. This helped in performance by reducing
>> the time
>> taken to write all the transactions but when I used this for some of the
>> DB
>> operations
>> sqlite throws error "unable to open database file".If I remove the changes
>> it works
>> fine.What could be the problem here?
>>
>
> It might be failing to create a temporary file to use for a
> statement journal.
>
>   
> http://www.sqlite.org/pragma.**html#pragma_temp_store
> __**_
> 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] Facing "SQLite ERROR : unable to open database file" error

2013-01-16 Thread Dan Kennedy


On 01/16/2013 05:42 PM, Ashok Pitambar wrote:

Thanks Dan,

I will try by setting this pragma but still I don't understand why it is
failing for few query operations.


The temporary files might be statement journals. Which are only required
by some statements and only if they occur within a BEGIN/COMMIT block.

  http://www.sqlite.org/tempfiles.html#stmtjrnl




Regards,
Ashok



On Wed, Jan 16, 2013 at 1:46 PM, Dan Kennedy  wrote:


On 01/16/2013 02:21 PM, Ashok Pitambar wrote:


Hi All,

  I have used sqlite for Database in my client , to speed up
the
performance
I used *"begin"(BEGIN;) *and *"end"(END;)* query transactions to

include multiple
transactions in single transaction. This helped in performance by reducing
the time
taken to write all the transactions but when I used this for some of the
DB
operations
sqlite throws error "unable to open database file".If I remove the changes
it works
fine.What could be the problem here?



It might be failing to create a temporary file to use for a
statement journal.

   
http://www.sqlite.org/pragma.**html#pragma_temp_store
__**_
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] Multi-column index is not used with IN operator

2013-01-16 Thread Dan Kennedy


On 01/16/2013 05:13 PM, Selen Schabenberger wrote:

I attach a small database where it is possible to reproduce the
issue. I deleted all irrelevant tables and all the tuples in the
Message table to keep the file size small but had run ANALYZE before
doing that.


Mailing list does not allow attachments. Can you either upload the
db somewhere, or include the output of ".dump" in the body of the
message if it is small enough? Thanks.






This is the query to reproduce with 3.7.15.2: EXPLAIN QUERY PLAN

SELECT * FROM message WHERE tag IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



) AND flag=1

ORDER BY id LIMIT 200;

I get this result: selectId   order  from   detail


0  0  0  SCAN TABLE Message USING INTEGER
PRIMARY KEY (~4601 rows) 0  0  0  EXECUTE
LIST SUBQUERY 1


Hope someone can help.


- Selen



 From: Selen
Schabenberger To: General Discussion of SQLite
Database; Richard Hipp Sent:
Wednesday, January 2, 2013 12:22 PM Subject: Re: [sqlite]
Multi-column index is not used with IN operator

Hi Richard, I tested the whole scenario one more time with the new
SQLite version. As you suggested I put a plus sign in front of the
Flag column and that really made the query much faster by using the
multi column index (Tag, Flag, Id) instead of the primary index on
the Id column. However what I don't get is, I actually had removed
that single column index on the Flag before and run ANALZE. How come
the query optimizer makes another decision when I put a + in front of
a column which is not indexed alone? Is there another way to improve
this query, other than using the + sign? I would really appreciate
any suggestions. Happy new year! Regards,Selen

--- On Fri, 12/14/12, Richard Hipp  wrote:

From: Richard Hipp Subject: Re: [sqlite] Multi-column
index is not used with IN operator To: "Selen
Schabenberger", "General Discussion of SQLite
Database" Date: Friday, December 14, 2012,
3:09 PM



On Thu, Dec 13, 2012 at 10:06 AM, Selen
Schabenberger  wrote:


Hi All,



I am observing some strange behaviour on my database when I execute a
query with an IN operator having more than "22" expressions. My table
structure looks basically as follows:



CREATE TABLE "Messages" ("Id" INTEGER PRIMARY KEY NOT NULL, "Tag"
INTEGER NOT NULL, "Flag" INTEGER )





I have a multi-column index on  (Tag, Flag, Id) as well as a single
column index on the Flag column.

My guess is that the single-column index on Flag is misleading the
query optimizer.  You can probably fix this by either (1) running
ANALYZE or (2) adding a "+" in front of the "Flag" column name in the
WHERE clause of your query, like this:   "... +Flag=1 ..."






___ 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] Multi-column index is not used with IN operator

2013-01-16 Thread Selen Schabenberger

Below is the output of the dump. If it does not help reproduce the error, then 
I can try to share the original database file itself.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER NOT 
NULL, 'Flag' INTEGER NOT NULL );
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 
1275 1');
CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
COMMIT;


Thanks!
Selen


 From: Dan Kennedy 
To: sqlite-users@sqlite.org 
Sent: Wednesday, January 16, 2013 12:05 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator
 
On 01/16/2013 05:13 PM, Selen Schabenberger wrote:
> I attach a small database where it is possible to reproduce the
> issue. I deleted all irrelevant tables and all the tuples in the
> Message table to keep the file size small but had run ANALYZE before
> doing that.

Mailing list does not allow attachments. Can you either upload the
db somewhere, or include the output of ".dump" in the body of the
message if it is small enough? Thanks.




>
> This is the query to reproduce with 3.7.15.2: EXPLAIN QUERY PLAN
>
> SELECT * FROM message WHERE tag IN
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>
>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>
>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>
>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>
>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>
>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>
>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>
>
) AND flag=1
> ORDER BY id LIMIT 200;
>
> I get this result: selectId   order      from       detail
>
>
> 0          0          0          SCAN TABLE Message USING INTEGER
> PRIMARY KEY (~4601 rows) 0          0          0          EXECUTE
> LIST SUBQUERY 1
>
>
> Hope someone can help.
>
>
> - Selen
>
>
>
>  From: Selen
> Schabenberger To: General Discussion of SQLite
> Database; Richard Hipp Sent:
> Wednesday, January 2, 2013 12:22 PM Subject: Re: [sqlite]
> Multi-column index is not used with IN operator
>
> Hi Richard, I tested the whole scenario one more time with the new
> SQLite version. As you suggested I put a plus sign in front of the
> Flag column and that really made the query much faster by using the
> multi column index (Tag, Flag, Id) instead of the primary index on
> the Id column. However what I don't get is, I actually had removed
> that single column index on the Flag before and run ANALZE. How come
> the query optimizer makes another decision when I put a + in front of
> a column which is not indexed alone? Is there another way to improve
> this query, other than using the + sign? I would really appreciate
> any suggestions. Happy new year! Regards,Selen
>
> --- On Fri, 12/14/12, Richard Hipp  wrote:
>
> From: Richard Hipp Subject: Re: [sqlite] Multi-column
> index is not used with IN operator To: "Selen
> Schabenberger", "General Discussion of SQLite
> Database" Date: Friday, December 14, 2012,
> 3:09 PM
>
>
>
> On Thu, Dec 13, 2012 at 10:06 AM, Selen
> Schabenberger  wrote:
>
>
> Hi All,
>
>
>
> I am observing some strange behaviour on my database when I execute a
> query with an IN operator having more than "22" expressions. My table
> structure looks basically as follows:
>
>
>
> CREATE TABLE "Messages" ("Id" INTEGER PRIMARY KEY NOT NULL, "Tag"
> INTEGER NOT NULL, "Flag" INTEGER )
>
>
>
>
>
> I have a multi-column index on  (Tag, Flag, Id) as well as a single
> column index on the Flag column.
>
> My guess is that the single-column index on Flag is misleading the
> query optimizer.  You can probably fix this by either (1) running
> ANALYZE or (2) adding a "+" in front of the "Flag" column name in the
> WHERE clause of your query, like this:   "... +Flag=1 ..."
>
>
>
>
>
>
> ___ 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] Multi-column index is not used with IN operator

2013-01-16 Thread Dan Kennedy


On 01/16/2013 06:25 PM, Selen Schabenberger wrote:

Below is the output of the dump. If it does not help reproduce the error, then 
I can try to share the original database file itself.

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER NOT 
NULL, 'Flag' INTEGER NOT NULL );
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 
1275 1');
CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
COMMIT;



Got it this time. Considering this one:

  SELECT * FROM message
  WHERE tag IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) 
AND flag=1

  ORDER BY id LIMIT 200;

Looks like the query planner now assigns a higher cost to
external sorts (ORDER BY clauses that cannot use indexes) than it
did in 3.6.23.1. In both cases, SQLite assumes that scanning the
full table implies visiting 460132 rows. In both versions, this
plan is assigned a cost of 460132.

If there are 30 elements in the IN(...) set, SQLite assumes that
scanning the index requires visiting (30*1275)=38250 rows.
Basic cost of 38250, plus some insignificant amount for the 30
seek operations required.

In version 3.6.23.1, the penalty for the external sort is
(nRow*log10(nRow)), where nRow is the number of rows to sort (in
this case 38250). SQLite rounds up the log10() expression to 5,
so the penalty is roughly 191250. Total cost of 229500. It then
gets a 50% discount for using a covering index, so the overall
cost is roughly 115000. Making it preferable to use the index.

However, in 3.7.16, the penalty for the external sort is
(3*nRow*log10(nRow)) and there is no discount for using a
covering index (instead, there would be another penalty if the
index were not a covering index). For a total cost of roughly
612000. So this version of SQLite does a full table scan.

None of this jumps out as obviously incorrect. In practice, how
much slower is 3.7.16 at running the query above?

What does:

  SELECT count(*) FROM message WHERE tag IN () AND flag=1;

return? Is it close to the 38250 that SQLite is using as an
estimate when planning the query?

Thanks,
Dan.






Thanks!
Selen


  From: Dan Kennedy
To: sqlite-users@sqlite.org
Sent: Wednesday, January 16, 2013 12:05 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator

On 01/16/2013 05:13 PM, Selen Schabenberger wrote:

I attach a small database where it is possible to reproduce the
issue. I deleted all irrelevant tables and all the tuples in the
Message table to keep the file size small but had run ANALYZE before
doing that.


Mailing list does not allow attachments. Can you either upload the
db somewhere, or include the output of ".dump" in the body of the
message if it is small enough? Thanks.











This is the query to reproduce with 3.7.15.2: EXPLAIN QUERY PLAN

SELECT * FROM message WHERE tag IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30

,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30



) AND flag=1

ORDER BY id LIMIT 200;

I get this result: selectId   order  from   detail


0  0  0  SCAN TABLE Message USING INTEGER
PRIMARY KEY (~4601 rows) 0  0  0  EXECUTE
LIST SUBQUERY 1


Hope someone can help.


- Selen



 From: Selen
Schabenberger  To: General Discussion of SQLite
Database; Richard Hipp  Sent:
Wednesday, January 2, 2013 12:22 PM Subject: Re: [sqlite]
Multi-column index is not used with IN operator

Hi Richard, I tested the whole scenario one more time with the new
SQLite version. As you suggested I put a plus sign in front of the
Flag column and that really made the query much faster by using the
multi column index (Tag, Flag, Id) instead of the primary index on
the Id column. However what I don't get is, I actually had removed
that single column index on the Flag before and run ANALZE. How come

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

2013-01-16 Thread Ashok Pitambar

Got it , Thanks Dan.

On Wed, Jan 16, 2013 at 4:29 PM, Dan Kennedy  wrote:

> On 01/16/2013 05:42 PM, Ashok Pitambar wrote:
>
>> Thanks Dan,
>>
>> I will try by setting this pragma but still I don't understand why it is
>> failing for few query operations.
>>
>
> The temporary files might be statement journals. Which are only required
> by some statements and only if they occur within a BEGIN/COMMIT block.
>
>   
> http://www.sqlite.org/**tempfiles.html#stmtjrnl
>
>
>
>> Regards,
>> Ashok
>>
>>
>>
>> On Wed, Jan 16, 2013 at 1:46 PM, Dan Kennedy
>>  wrote:
>>
>>  On 01/16/2013 02:21 PM, Ashok Pitambar wrote:
>>>
>>>  Hi All,

   I have used sqlite for Database in my client , to speed up
 the
 performance
 I used *"begin"(BEGIN;) *and *"end"(END;)* query transactions to

 include multiple
 transactions in single transaction. This helped in performance by
 reducing
 the time
 taken to write all the transactions but when I used this for some of the
 DB
 operations
 sqlite throws error "unable to open database file".If I remove the
 changes
 it works
 fine.What could be the problem here?


>>> It might be failing to create a temporary file to use for a
>>> statement journal.
>>>
>>>
>>> http://www.sqlite.org/pragma.html#pragma_temp_store
>>> http://www.sqlite.org/pragma.html#pragma_temp_store>
>>> >
>>> ___
>>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Selen Schabenberger



I attach a small database where it is possible to reproduce the issue. I 
deleted all irrelevant tables and all the tuples in the Message table to keep 
the file size small but had run ANALYZE before doing that.

This is the query to reproduce with 3.7.15.2:
EXPLAIN QUERY PLAN

SELECT * FROM message
WHERE tag IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
            
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
) AND flag=1
ORDER BY id LIMIT 200;

I get this result:
selectId   order  from   detail                        

0  0  0  SCAN TABLE Message USING INTEGER PRIMARY KEY 
(~4601 rows)
0  0  0  EXECUTE LIST SUBQUERY 1   


Hope someone can help.


- Selen



 From: Selen Schabenberger 
To: General Discussion of SQLite Database ; Richard 
Hipp  
Sent: Wednesday, January 2, 2013 12:22 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator
 
Hi Richard,
I tested the whole scenario one more time with the new SQLite version. As you 
suggested I put a plus sign in front of the Flag column and that really made 
the query much faster by using the multi column index (Tag, Flag, Id) instead 
of the primary index on the Id column. However what I don't get is, I actually 
had removed that single column index on the Flag before and run ANALZE. How 
come the query optimizer makes another decision when I put a + in front of a 
column which is not indexed alone?
Is there another way to improve this query, other than using the + sign? I 
would really appreciate any suggestions.
Happy new year!
Regards,Selen

--- On Fri, 12/14/12, Richard Hipp  wrote:

From: Richard Hipp 
Subject: Re: [sqlite] Multi-column index is not used with IN operator
To: "Selen Schabenberger" , "General Discussion of 
SQLite Database" 
Date: Friday, December 14, 2012, 3:09 PM



On Thu, Dec 13, 2012 at 10:06 AM, Selen Schabenberger  
wrote:


Hi All,



I am observing some strange behaviour on my database when I execute a query 
with an IN operator having more than "22" expressions. My table structure looks 
basically as follows:



CREATE TABLE "Messages" ("Id" INTEGER PRIMARY KEY NOT NULL, "Tag" INTEGER NOT 
NULL, "Flag" INTEGER )





I have a multi-column index on  (Tag, Flag, Id) as well as a single column 
index on the Flag column.

My guess is that the single-column index on Flag is misleading the query 
optimizer.  You can probably fix this by either (1) running ANALYZE or (2) 
adding a "+" in front of the "Flag" column name in the WHERE clause of your 
query, like this:   "... +Flag=1 ..."



-- 
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Selen Schabenberger

My original query looks like this one and the Message table has in total 10 
columns in it (there is one BLOB). 

Select * from Message where ((Tag in ( 1146883, 1146884, 1146886, 1146888, 
1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912, 1147914, 
1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016, 1148018, 
1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138, 1148191, 
1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667, 1167671, 1167675 
) ) and Flag=1)  limit 200

If the database file is on the network share (which is most likely the case), 
then it takes ~22000 ms (with the index 300ms!) to return the results. On the 
local drive it is ~300 ms (with the index 10 ms). There are in total 101 rows 
that match the where clause.

Thanks!
Selen

 From: Dan Kennedy 
To: sqlite-users@sqlite.org 
Sent: Wednesday, January 16, 2013 1:12 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator

On 01/16/2013 06:25 PM, Selen Schabenberger wrote:
> Below is the output of the dump. If it does not help reproduce the error, 
> then I can try to share the original database file itself.
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER NOT 
> NULL, 'Flag' INTEGER NOT NULL );
> ANALYZE sqlite_master;
> INSERT INTO "sqlite_stat1" VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 
> 1289 1275 1');
> CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
> COMMIT;
>

Got it this time. Considering this one:

   SELECT * FROM message
   WHERE tag IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)

AND flag=1
   ORDER BY id LIMIT 200;

Looks like the query planner now assigns a higher cost to
external sorts (ORDER BY clauses that cannot use indexes) than it
did in 3.6.23.1. In both cases, SQLite assumes that scanning the
full table implies visiting 460132 rows. In both versions, this
plan is assigned a cost of 460132.

If there are 30 elements in the IN(...) set, SQLite assumes that
scanning the index requires visiting (30*1275)=38250 rows.
Basic cost of 38250, plus some insignificant amount for the 30
seek operations required.

In version 3.6.23.1, the penalty for the external sort is
(nRow*log10(nRow)), where nRow is the number of rows to sort (in
this case 38250). SQLite rounds up the log10() expression to 5,
so the penalty is roughly 191250. Total cost of 229500. It then
gets a 50% discount for using a covering index, so the overall
cost is roughly 115000. Making it preferable to use the index.

However, in 3.7.16, the penalty for the external sort is
(3*nRow*log10(nRow)) and there is no discount for using a
covering index (instead, there would be another penalty if the
index were not a covering index). For a total cost of roughly
612000. So this version of SQLite does a full table scan.

None of this jumps out as obviously incorrect. In practice, how
much slower is 3.7.16 at running the query above?

What does:

   SELECT count(*) FROM message WHERE tag IN () AND flag=1;

return? Is it close to the 38250 that SQLite is using as an
estimate when planning the query?

Thanks,
Dan.

>
> Thanks!
> Selen
>
> 
>   From: Dan Kennedy
> To: sqlite-users@sqlite.org
> Sent: Wednesday, January 16, 2013 12:05 PM
> Subject: Re: [sqlite] Multi-column index is not used with IN operator
>
> On 01/16/2013 05:13 PM, Selen Schabenberger wrote:
>> I attach a small database where it is possible to reproduce the
>> issue. I deleted all irrelevant tables and all the tuples in the
>> Message table to keep the file size small but had run ANALYZE before
>> doing that.
>
> Mailing list does not allow attachments. Can you either upload the
> db somewhere, or include the output of ".dump" in the body of the
> message if it is small enough? Thanks.
>

>
>
>
>>
>> This is the query to reproduce with 3.7.15.2: EXPLAIN QUERY PLAN
>>
>> SELECT * FROM message WHERE tag IN
>> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24

[sqlite] Memory / off jornal still causes disc syncs

2013-01-16 Thread Jason Taylor


If 'journal_mode' is set to 'memory' or to 'off' it seems like sqlite still 
synchs the main database file to disk on commit, unless you also set 
'synchronous' to 'off'. This is arguably a bug since at this point the fsyncs 
buy you nothing except lost performance.
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory / off jornal still causes disc syncs

2013-01-16 Thread Richard Hipp

On Wed, Jan 16, 2013 at 8:36 AM, Jason Taylor  wrote:

>
> If 'journal_mode' is set to 'memory' or to 'off' it seems like sqlite
> still synchs the main database file to disk on commit, unless you also set
> 'synchronous' to 'off'. This is arguably a bug since at this point the
> fsyncs buy you nothing except lost performance.
>

I think the fsyncs get you Durability.


>
> ___
> 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] Memory / off jornal still causes disc syncs

2013-01-16 Thread Jason Taylor


> I think the fsyncs get you Durability.

Well, in my opinion durability without atomicity and consistency is a bit of a 
fringe requirement.

But I can see how you could argue it both ways.
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

2013-01-16 Thread Michael Black

Is there some reason there's the rather poor "unable to open" message
without the actual error message?

Strerror coming to mind if it hasn't disappeared under foot at those places?

I see these questions on the list fairly often and they would all be
answered with an intelligent error messagefrequently permission
oriented.

All such errors should say the file they errored on and the system error
along with it.

At an absolute minimum they should say the filename which would still allow
much better diagnosis rather than trying to guess what's going on.





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

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Selen Schabenberger

>However, in 3.7.16, the penalty for the external sort is
>(3*nRow*log10(nRow)) and there is no discount for using a
>covering index (instead, there would be another penalty if the
>index were not a covering index). For a total cost of roughly
>612000. So this version of SQLite does a full table scan.

If the index consists of only the Tag, then this index is used. You said, there 
would be a penalty if the index was not a covering index. But seems like it is 
a discount for 3.7.15.2?

- Selen

 From: Dan Kennedy 
To: sqlite-users@sqlite.org 
Sent: Wednesday, January 16, 2013 1:12 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator

On 01/16/2013 06:25 PM, Selen Schabenberger wrote:
> Below is the output of the dump. If it does not help reproduce the error, 
> then I can try to share the original database file itself.
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER NOT 
> NULL, 'Flag' INTEGER NOT NULL );
> ANALYZE sqlite_master;
> INSERT INTO "sqlite_stat1" VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 
> 1289 1275 1');
> CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
> COMMIT;
>

Got it this time. Considering this one:

   SELECT * FROM message
   WHERE tag IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)

AND flag=1
   ORDER BY id LIMIT 200;

Looks like the query planner now assigns a higher cost to
external sorts (ORDER BY clauses that cannot use indexes) than it
did in 3.6.23.1. In both cases, SQLite assumes that scanning the
full table implies visiting 460132 rows. In both versions, this
plan is assigned a cost of 460132.

If there are 30 elements in the IN(...) set, SQLite assumes that
scanning the index requires visiting (30*1275)=38250 rows.
Basic cost of 38250, plus some insignificant amount for the 30
seek operations required.

In version 3.6.23.1, the penalty for the external sort is
(nRow*log10(nRow)), where nRow is the number of rows to sort (in
this case 38250). SQLite rounds up the log10() expression to 5,
so the penalty is roughly 191250. Total cost of 229500. It then
gets a 50% discount for using a covering index, so the overall
cost is roughly 115000. Making it preferable to use the index.

However, in 3.7.16, the penalty for the external sort is
(3*nRow*log10(nRow)) and there is no discount for using a
covering index (instead, there would be another penalty if the
index were not a covering index). For a total cost of roughly
612000. So this version of SQLite does a full table scan.

None of this jumps out as obviously incorrect. In practice, how
much slower is 3.7.16 at running the query above?

What does:

   SELECT count(*) FROM message WHERE tag IN () AND flag=1;

return? Is it close to the 38250 that SQLite is using as an
estimate when planning the query?

Thanks,
Dan.

>
> Thanks!
> Selen
>
> 
>   From: Dan Kennedy
> To: sqlite-users@sqlite.org
> Sent: Wednesday, January 16, 2013 12:05 PM
> Subject: Re: [sqlite] Multi-column index is not used with IN operator
>
> On 01/16/2013 05:13 PM, Selen Schabenberger wrote:
>> I attach a small database where it is possible to reproduce the
>> issue. I deleted all irrelevant tables and all the tuples in the
>> Message table to keep the file size small but had run ANALYZE before
>> doing that.
>
> Mailing list does not allow attachments. Can you either upload the
> db somewhere, or include the output of ".dump" in the body of the
> message if it is small enough? Thanks.
>

>
>
>
>>
>> This is the query to reproduce with 3.7.15.2: EXPLAIN QUERY PLAN
>>
>> SELECT * FROM message WHERE tag IN
>> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>>
>>
> ) AND flag=1
>> ORDER BY id LIMIT 200;
>>
>> I get this result: selectId   order      from       deta

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Dan Kennedy

On 01/16/2013 08:48 PM, Selen Schabenberger wrote:

However, in 3.7.16, the penalty for the external sort is
(3*nRow*log10(nRow)) and there is no discount for using a
covering index (instead, there would be another penalty if the
index were not a covering index). For a total cost of roughly
612000. So this version of SQLite does a full table scan.

If the index consists of only the Tag, then this index is used.

When you create the index on just the Tag column, what does the
corresponding sqlite_stat1 entry look like?

Dan.

 You

said, there would be a penalty if the index was not a covering index.
But seems like it is a discount for 3.7.15.2?

- Selen

*From:* Dan Kennedy 
*To:* sqlite-users@sqlite.org
*Sent:* Wednesday, January 16, 2013 1:12 PM
*Subject:* Re: [sqlite] Multi-column index is not used with IN operator

On 01/16/2013 06:25 PM, Selen Schabenberger wrote:
 > Below is the output of the dump. If it does not help reproduce the
error, then I can try to share the original database file itself.
 >
 > PRAGMA foreign_keys=OFF;
 > BEGIN TRANSACTION;
 > CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag'
INTEGER NOT NULL, 'Flag' INTEGER NOT NULL );
 > ANALYZE sqlite_master;
 > INSERT INTO "sqlite_stat1"
VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 1275 1');
 > CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
 > COMMIT;
 >

Got it this time. Considering this one:

SELECT * FROM message
WHERE tag IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)

AND flag=1
ORDER BY id LIMIT 200;

Looks like the query planner now assigns a higher cost to
external sorts (ORDER BY clauses that cannot use indexes) than it
did in 3.6.23.1. In both cases, SQLite assumes that scanning the
full table implies visiting 460132 rows. In both versions, this
plan is assigned a cost of 460132.

If there are 30 elements in the IN(...) set, SQLite assumes that
scanning the index requires visiting (30*1275)=38250 rows.
Basic cost of 38250, plus some insignificant amount for the 30
seek operations required.

In version 3.6.23.1, the penalty for the external sort is
(nRow*log10(nRow)), where nRow is the number of rows to sort (in
this case 38250). SQLite rounds up the log10() expression to 5,
so the penalty is roughly 191250. Total cost of 229500. It then
gets a 50% discount for using a covering index, so the overall
cost is roughly 115000. Making it preferable to use the index.

However, in 3.7.16, the penalty for the external sort is
(3*nRow*log10(nRow)) and there is no discount for using a
covering index (instead, there would be another penalty if the
index were not a covering index). For a total cost of roughly
612000. So this version of SQLite does a full table scan.

None of this jumps out as obviously incorrect. In practice, how
much slower is 3.7.16 at running the query above?

What does:

SELECT count(*) FROM message WHERE tag IN () AND flag=1;

return? Is it close to the 38250 that SQLite is using as an
estimate when planning the query?

Thanks,
Dan.

 >
 > Thanks!
 > Selen
 >
 > 
 > From: Dan Kennedymailto:danielk1...@gmail.com>>
 > To: sqlite-users@sqlite.org 
 > Sent: Wednesday, January 16, 2013 12:05 PM
 > Subject: Re: [sqlite] Multi-column index is not used with IN operator
 >
 > On 01/16/2013 05:13 PM, Selen Schabenberger wrote:
 >> I attach a small database where it is possible to reproduce the
 >> issue. I deleted all irrelevant tables and all the tuples in the
 >> Message table to keep the file size small but had run ANALYZE before
 >> doing that.
 >
 > Mailing list does not allow attachments. Can you either upload the
 > db somewhere, or include the output of ".dump" in the body of the
 > message if it is small enough? Thanks.
 >

 >
 >
 >
 >>
 >> This is the query to reproduce with 3.7.15.2: EXPLAIN QUERY PLAN
 >>
 >> SELECT * FROM message WHERE tag IN
 >>
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
 >>
 >
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
 >>
 >
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
 >>
 >
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
 >>
 >
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
 >>
 >
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
 >>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Selen Schabenberger



The entry for the Tag index in sqlite_stat1:

460132 1289

The results are returned in 163 ms on the network share.

- Selen



 From: Dan Kennedy 
To: General Discussion of SQLite Database  
Sent: Wednesday, January 16, 2013 2:56 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator
 
On 01/16/2013 08:48 PM, Selen Schabenberger wrote:
>>However, in 3.7.16, the penalty for the external sort is
>>(3*nRow*log10(nRow)) and there is no discount for using a
>>covering index (instead, there would be another penalty if the
>>index were not a covering index). For a total cost of roughly
>>612000. So this version of SQLite does a full table scan.
>
> If the index consists of only the Tag, then this index is used.

When you create the index on just the Tag column, what does the
corresponding sqlite_stat1 entry look like?

Dan.




  You
> said, there would be a penalty if the index was not a covering index.
> But seems like it is a discount for 3.7.15.2?
>
> - Selen
>
> 
> *From:* Dan Kennedy 
> *To:* sqlite-users@sqlite.org
> *Sent:* Wednesday, January 16, 2013 1:12 PM
> *Subject:* Re: [sqlite] Multi-column index is not used with IN operator
>
> On 01/16/2013 06:25 PM, Selen Schabenberger wrote:
>  > Below is the output of the dump. If it does not help reproduce the
> error, then I can try to share the original database file itself.
>  >
>  > PRAGMA foreign_keys=OFF;
>  > BEGIN TRANSACTION;
>  > CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag'
> INTEGER NOT NULL, 'Flag' INTEGER NOT NULL );
>  > ANALYZE sqlite_master;
>  > INSERT INTO "sqlite_stat1"
> VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 1275 1');
>  > CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
>  > COMMIT;
>  >
>
> Got it this time. Considering this one:
>
> SELECT * FROM message
> WHERE tag IN
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)
>
> AND flag=1
> ORDER BY id LIMIT 200;
>
> Looks like the query planner now assigns a higher cost to
> external sorts (ORDER BY clauses that cannot use indexes) than it
> did in 3.6.23.1. In both cases, SQLite assumes that scanning the
> full table implies visiting 460132 rows. In both versions, this
> plan is assigned a cost of 460132.
>
> If there are 30 elements in the IN(...) set, SQLite assumes that
> scanning the index requires visiting (30*1275)=38250 rows.
> Basic cost of 38250, plus some insignificant amount for the 30
> seek operations required.
>
> In version 3.6.23.1, the penalty for the external sort is
> (nRow*log10(nRow)), where nRow is the number of rows to sort (in
> this case 38250). SQLite rounds up the log10() expression to 5,
> so the penalty is roughly 191250. Total cost of 229500. It then
> gets a 50% discount for using a covering index, so the overall
> cost is roughly 115000. Making it preferable to use the index.
>
> However, in 3.7.16, the penalty for the external sort is
> (3*nRow*log10(nRow)) and there is no discount for using a
> covering index (instead, there would be another penalty if the
> index were not a covering index). For a total cost of roughly
> 612000. So this version of SQLite does a full table scan.
>
> None of this jumps out as obviously incorrect. In practice, how
> much slower is 3.7.16 at running the query above?
>
> What does:
>
> SELECT count(*) FROM message WHERE tag IN () AND flag=1;
>
> return? Is it close to the 38250 that SQLite is using as an
> estimate when planning the query?
>
> Thanks,
> Dan.
>
>
>
>
>  >
>  > Thanks!
>  > Selen
>  >
>  > 
>  > From: Dan Kennedymailto:danielk1...@gmail.com>>
>  > To: sqlite-users@sqlite.org 
>  > Sent: Wednesday, January 16, 2013 12:05 PM
>  > Subject: Re: [sqlite] Multi-column index is not used with IN operator
>  >
>  > On 01/16/2013 05:13 PM, Selen Schabenberger wrote:
>  >> I attach a small database where it is possible to reproduce the
>  >> issue. I deleted all irrelevant tables and all the tuples in the
>  >> Message table to keep the file size small but had run ANALYZE before
>  >> doing that.
>  >
>  > Mailing list does not allow attachments. Can you either upload the
>  > db somewhere, or include the output of ".dump" in the body of the
>  > message if it is small enough? Thanks.
>  >
>
>
>
>  >
>  >
>  >
>  >>
>  >> This is the query to reproduce with 3.7.15.2: EXPLAIN QUERY PLAN
>  >>
>  >> SELECT * FROM message WHERE tag IN
>  >>
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>  >>
>  >>
>  >
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>  >>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>  >>
>  >>
>  >
> ,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
>  >>
> ,1,2,3,4,5,6,7,8,9,10,11,12,13

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Dan Kennedy

On 01/16/2013 09:04 PM, Selen Schabenberger wrote:

The entry for the Tag index in sqlite_stat1:

460132 1289

The results are returned in 163 ms on the network share.

I can't reproduce that. Once I add the entry to sqlite_stat1
it uses the IPK index. Can you post the new .dump of the schema
and query again?

Dan.

- Selen

  From: Dan Kennedy
To: General Discussion of SQLite Database
Sent: Wednesday, January 16, 2013 2:56 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator

On 01/16/2013 08:48 PM, Selen Schabenberger wrote:

However, in 3.7.16, the penalty for the external sort is
(3*nRow*log10(nRow)) and there is no discount for using a
covering index (instead, there would be another penalty if the
index were not a covering index). For a total cost of roughly
612000. So this version of SQLite does a full table scan.

If the index consists of only the Tag, then this index is used.

When you create the index on just the Tag column, what does the
corresponding sqlite_stat1 entry look like?

Dan.

   You

said, there would be a penalty if the index was not a covering index.
But seems like it is a discount for 3.7.15.2?

- Selen

*From:* Dan Kennedy
*To:* sqlite-users@sqlite.org
*Sent:* Wednesday, January 16, 2013 1:12 PM
*Subject:* Re: [sqlite] Multi-column index is not used with IN operator

On 01/16/2013 06:25 PM, Selen Schabenberger wrote:
   >  Below is the output of the dump. If it does not help reproduce the
error, then I can try to share the original database file itself.
   >
   >  PRAGMA foreign_keys=OFF;
   >  BEGIN TRANSACTION;
   >  CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag'
INTEGER NOT NULL, 'Flag' INTEGER NOT NULL );
   >  ANALYZE sqlite_master;
   >  INSERT INTO "sqlite_stat1"
VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 1275 1');
   >  CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
   >  COMMIT;
   >

Got it this time. Considering this one:

SELECT * FROM message
WHERE tag IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)

AND flag=1
ORDER BY id LIMIT 200;

Looks like the query planner now assigns a higher cost to
external sorts (ORDER BY clauses that cannot use indexes) than it
did in 3.6.23.1. In both cases, SQLite assumes that scanning the
full table implies visiting 460132 rows. In both versions, this
plan is assigned a cost of 460132.

If there are 30 elements in the IN(...) set, SQLite assumes that
scanning the index requires visiting (30*1275)=38250 rows.
Basic cost of 38250, plus some insignificant amount for the 30
seek operations required.

In version 3.6.23.1, the penalty for the external sort is
(nRow*log10(nRow)), where nRow is the number of rows to sort (in
this case 38250). SQLite rounds up the log10() expression to 5,
so the penalty is roughly 191250. Total cost of 229500. It then
gets a 50% discount for using a covering index, so the overall
cost is roughly 115000. Making it preferable to use the index.

However, in 3.7.16, the penalty for the external sort is
(3*nRow*log10(nRow)) and there is no discount for using a
covering index (instead, there would be another penalty if the
index were not a covering index). For a total cost of roughly
612000. So this version of SQLite does a full table scan.

None of this jumps out as obviously incorrect. In practice, how
much slower is 3.7.16 at running the query above?

What does:

SELECT count(*) FROM message WHERE tag IN () AND flag=1;

return? Is it close to the 38250 that SQLite is using as an
estimate when planning the query?

Thanks,
Dan.

   >
   >  Thanks!
   >  Selen
   >
   >  
   >  From: Dan Kennedymailto:danielk1...@gmail.com>>
   >  To: sqlite-users@sqlite.org
   >  Sent: Wednesday, January 16, 2013 12:05 PM
   >  Subject: Re: [sqlite] Multi-column index is not used with IN operator
   >
   >  On 01/16/2013 05:13 PM, Selen Schabenberger wrote:
   >>  I attach a small database where it is possible to reproduce the
   >>  issue. I deleted all irrelevant tables and all the tuples in the
   >>  Message table to keep the file size small but had run ANALYZE before
   >>  doing that.
   >
   >  Mailing list does not allow attachments. Can you either upload the
   >  db somewhere, or include the output of ".dump" in the body of the
   >  message if it is small enough? Thanks.
   >

   >
   >
   >
   >>
   >>  This is the query to reproduce with 3.7.15.2: EXPLAIN QUERY PLAN
   >>
   >>  SELECT * FROM message WHERE tag IN
   >>
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
   >>
   >>
   >
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
   >>
,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
   >>
   >>
   >
,1,2,3,4,5,6,7,8,9,10,1

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Selen Schabenberger

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER NOT 
NULL, 'Flag' INTEGER NOT NULL );
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('Message','IDX_TAG','460132 1289');
INSERT INTO "sqlite_stat1" VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 
1275 1');
CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
CREATE INDEX IDX_TAG on Message (Tag);
COMMIT;

Select * from Message where ((Tag in ( 1146883, 1146884, 1146886, 1146888, 
1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912, 1147914, 
1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016, 1148018, 
1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138, 1148191, 
1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667, 1167671, 1167675 
) ) and Flag=1)  limit 200


selectId   order  from   detail
0  0  0  SEARCH TABLE Message USING INDEX IDX_TAG 
(Tag=?) (~432 rows)
0  0  0  EXECUTE LIST SUBQUERY 1   
0  0  0  USE TEMP B-TREE FOR ORDER BY  


- Selen



>
> From: Dan Kennedy 
>To: sqlite-users@sqlite.org 
>Sent: Wednesday, January 16, 2013 3:14 PM
>Subject: Re: [sqlite] Multi-column index is not used with IN operator
> 
>On 01/16/2013 09:04 PM, Selen Schabenberger wrote:
>>
>>
>> The entry for the Tag index in sqlite_stat1:
>>
>> 460132 1289
>>
>> The results are returned in 163 ms on the network share.
>
>I can't reproduce that. Once I add the entry to sqlite_stat1
>it uses the IPK index. Can you post the new .dump of the schema
>and query again?
>
>Dan.
>
>
>
>>
>> - Selen
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Dan Kennedy


On 01/16/2013 09:22 PM, Selen Schabenberger wrote:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER
NOT NULL, 'Flag' INTEGER NOT NULL );
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('Message','IDX_TAG','460132 1289');
INSERT INTO "sqlite_stat1"
VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 1275 1');
CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
CREATE INDEX IDX_TAG on Message (Tag);
COMMIT;

Select * from Message where ((Tag in ( 1146883, 1146884, 1146886,
1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912,
1147914, 1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016,
1148018, 1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138,
1148191, 1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667,
1167671, 1167675 ) ) and Flag=1) limit 200

selectId order from detail
0 0 0 SEARCH TABLE Message USING INDEX IDX_TAG (Tag=?) (~432 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
0 0 0 & nbsp; USE TEMP B-TREE FOR ORDER BY


The query and the result above don't match.

If I add the ORDER BY clause to the query it uses the IPK index.

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

Re: [sqlite] Multi-column index is not used with IN operator

2013-01-16 Thread Selen Schabenberger

Sorry, wrong query wrong result.

But still when I add the order by, the index is used:

Select * from Message where ((Tag in ( 1146883, 1146884, 1146886, 1146888, 
1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912, 1147914, 
1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016, 1148018, 
1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138, 1148191, 
1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667, 1167671, 1167675 
) ) and Flag=1) order by Id limit 200


selectId   order  from   detail
0  0  0  SEARCH TABLE Message USING INDEX IDX_TAG 
(Tag=?) (~33 rows)
0  0  0  EXECUTE LIST SUBQUERY 1   
0  0  0  USE TEMP B-TREE FOR ORDER BY  





>
> From: Dan Kennedy 
>To: General Discussion of SQLite Database  
>Sent: Wednesday, January 16, 2013 3:40 PM
>Subject: Re: [sqlite] Multi-column index is not used with IN operator
> 
>On 01/16/2013 09:22 PM, Selen Schabenberger wrote:
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE 'Message' ('Id' INTEGER PRIMARY KEY NOT NULL, 'Tag' INTEGER
>> NOT NULL, 'Flag' INTEGER NOT NULL );
>> ANALYZE sqlite_master;
>> INSERT INTO "sqlite_stat1" VALUES('Message','IDX_TAG','460132 1289');
>> INSERT INTO "sqlite_stat1"
>> VALUES('Message','IDX_MSGS_TAG_FLAG_ID','460132 1289 1275 1');
>> CREATE INDEX 'IDX_MSGS_TAG_FLAG_ID' on 'Message' ('Tag', 'Flag', 'Id');
>> CREATE INDEX IDX_TAG on Message (Tag);
>> COMMIT;
>>
>> Select * from Message where ((Tag in ( 1146883, 1146884, 1146886,
>> 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912,
>> 1147914, 1147968, 1147970, 1147976, 1147978, 1148012, 1148015, 1148016,
>> 1148018, 1148020, 1148022, 1148040, 1148042, 1148079, 1148136, 1148138,
>> 1148191, 1148232, 1148234, 1167643, 1167659, 1167660, 1167663, 1167667,
>> 1167671, 1167675 ) ) and Flag=1) limit 200
>>
>> selectId order from detail
>> 0 0 0 SEARCH TABLE Message USING INDEX IDX_TAG (Tag=?) (~432 rows)
>> 0 0 0 EXECUTE LIST SUBQUERY 1
>> 0 0 0 & nbsp; USE TEMP B-TREE FOR ORDER BY
>
>The query and the result above don't match.
>
>If I add the ORDER BY clause to the query it uses the IPK index.
>
>Dan.
>___
>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] Facing "SQLite ERROR : unable to open database file" error

[sqlite] about delete problem

Re: [sqlite] about delete problem

Re: [sqlite] about delete problem

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

[sqlite] Memory / off jornal still causes disc syncs

Re: [sqlite] Memory / off jornal still causes disc syncs

Re: [sqlite] Memory / off jornal still causes disc syncs

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

Re: [sqlite] Multi-column index is not used with IN operator

24 matches

Site Navigation

Mail list logo

Footer information