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

On 01/17/2013 09:17 PM, Selen Schabenberger wrote: I was trying to avoid the INDEXED BY or the "+" operator as I cannot make sure that the search with the index will always (for every database created by my application) be faster than with the PK. I prefer the optimizer does the cost

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

I was trying to avoid the INDEXED BY or the "+" operator as I cannot make sure that the search with the index will always (for every database created by my application) be faster than with the PK. I prefer the optimizer does the cost calculations and makes the choice.  Do you want me to write

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

On 01/17/2013 06:32 PM, Selen Schabenberger wrote: Dan, I have just realised that with the sqlite_stat3 table, the query optimizer uses the INDEX IDX_TAG. When I drop this table, the PK is used as you said. But the concatenated index is still not used with the stat3. Looks like with stat3

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

Dan, I have just realised that with the sqlite_stat3 table, the query optimizer uses the  INDEX IDX_TAG. When I drop this table, the PK is used as you said. But the concatenated index is still not used with the stat3. Knowing that the query could be 60 times faster, is there anything I can do

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 Message where ((Tag in ( 1146883, 1146884, 1146886, 1146888, 1146892, 1146894, 1146896, 1146898, 1146920, 1146922, 1147912, 1147914, 1147968, 1147970, 1147976, 1147978, 1148012, 1148015,

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

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

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"

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

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

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

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

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

>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

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

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,

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'

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

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

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

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

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

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

Actually I'm already running ANALYZE with the SQLITE_ENABLE_STAT3 enabled. By the way I observe different behaviour with different SQLite versions. Version 3.6.19 (testing with Sqliteman 1.2.2)  is selecting the multi-column index, whereas the v3.7 primary index. - Selen

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

>From what I understand, SQLite performs query planning based on cost estimates. A full table scan costs less per record than an index lookup. 1) (scan cost) * (IN Lookup) is compared with 2) (IN size) * (key lookup) * (sort factor) IN Lookup cost is logarithmic; IN size is linear. The

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

Reordering the index worked but actually I need the index in (Tag, Flag, Id) order. The Flag column has a very low cardinality, and my queries are mostly filtering Tag & Flag or the Tag alone.  When I drop the index on the Flag, and leave the index on (Tag, Flag, Id), the query planner still

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

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

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

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,

[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" ("Id" INTEGER PRIMARY KEY NOT NULL, "Tag" INTEGER NOT NULL, "Flag" INTEGER ) I