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 hav
On 13 Dec 2012, at 3:06pm, Selen Schabenberger wrote:
>> explain query plan Select Messages.Id from Messages where (Tag in ( 1146883,
>> 1146884, 1146886, 1146888, 1146892, 1146894, 1146896, 1146898, 1146920,
>> 1146922, 1147912, 1147914, 1147968, 1147970, 1147976, 1147978, 1148012,
>> 114801
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" INTEGE
till chooses the primary key on the Id column.
-Selen
From: Richard Hipp
To: Selen Schabenberger ; General Discussion of SQLite
Database
Sent: Friday, December 14, 2012 3:09 PM
Subject: Re: [sqlite] Multi-column index is not used with IN operator
On Thu, Dec 13, 2012 at 10:06 AM, S
[sqlite] Multi-column index is not used with IN operator
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" ("
Selen
From: Hick Gunter
To: 'Selen Schabenberger' ; 'General Discussion of
SQLite Database'
Sent: Friday, December 14, 2012 3:26 PM
Subject: AW: [sqlite] Multi-column index is not used with IN operator
From what I understand, SQLite performs query planning ba
gn? 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"
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
ubject: 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)
#x27;);
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 sm
elen
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 th
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
: 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
qlite.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 fi
Thanks,
Dan.
>
> Thanks!
> Selen
>
>
> From: Dan Kennedymailto:danielk1...@gmail.com>>
> To: sqlite-users@sqlite.org <mailto:sqlite-users@sqlite.org>
> Sent: Wednesday, January 16, 2013 12:05 PM
> Subject: Re: [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
> e
chema
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
DEX 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
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');
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' ('
R 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
neral Discussion of SQLite Database
; Dan Kennedy
*Sent:* Wednesday, January 16, 2013 3:46 PM
*Subject:* Re: [sqlite] Multi-column index is not used with IN operator
Sorry, wrong query wrong result.
But still when I add the order by, the index is used:
Select * from Messa
e to write another ticket for the enhancement you mentioned?
-Selen
>
> From: Dan Kennedy
>To: General Discussion of SQLite Database
>Sent: Thursday, January 17, 2013 2:38 PM
>Subject: Re: [sqlite] Multi-column index is not used with IN operator
&
rs.
Dan.
-Selen
From: Dan
Kennedy To: General Discussion of SQLite
Database Sent: Thursday, January 17, 2013
2:38 PM Subject: Re: [sqlite] Multi-column index is not used with
IN operator
On 01/17/2013 06:32 PM, Selen Schabenberger wrote:
Dan, I have jus
24 matches
Mail list logo
