Re: [sqlite] Index with two columns
Richard, thank you very much. Jakub D. Richard Hipp wrote: On Fri, 2005-06-03 at 13:20 +0200, Jakub Adamek wrote: Hello, please, is there any way to make SQLite use an index on two columns when I want to select all rows which have some combination of the two columns? SELECT * FROM PointFeature WHERE DsetId=203 AND SectId IN (4,400); SQLite does use multiple columns of a multi-column index for == constraints. But for an IN operator, it will only using a single column. This is something that I need to work on. In the meantime, I suggest the following work-around: SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=4 UNION ALL SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=400;
Re: [sqlite] Index with two columns
On Fri, 2005-06-03 at 13:20 +0200, Jakub Adamek wrote: > Hello, please, is there any way to make SQLite use an index on two > columns when I want to select all rows which have some combination of > the two columns? > > SELECT * FROM PointFeature WHERE > DsetId=203 AND SectId IN (4,400); > SQLite does use multiple columns of a multi-column index for == constraints. But for an IN operator, it will only using a single column. This is something that I need to work on. In the meantime, I suggest the following work-around: SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=4 UNION ALL SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=400; -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Index with two columns
Jakub Adamek <[EMAIL PROTECTED]> writes: > Hello, please, is there any way to make SQLite use an index on two > columns when I want to select all rows which have some combination of > the two columns? > > My table is: > CREATE TABLE PointFeature ( > DSetId INTEGER, > SectId INTEGER, > PntItemId INTEGER); > > CREATE INDEX xxx ON PointFeature (DSetId, SectId, PntItemId); > > and the query is > SELECT * FROM PointFeature WHERE > DsetId=203 AND SectId IN (4,400); > > But the index is used just to find the DsetId and not to find SectId. > Is there another form of the SQL which could do that? Or do I have to > use two separate queries Try this instead: SELECT * FROM PointFeature WHERE DsetId=203 AND (SectId = 4 OR SectId = 400); Derrell
Re: [sqlite] Index with two columns
> Hello, please, is there any way to make SQLite use an index on two > columns when I want to select all rows which have some combination of > the two columns? > SELECT * FROM PointFeature WHERE > DsetId=203 AND SectId IN (4,400); I can't answer the question you asked, but I will point out one thing. Many SQL engines attempt to optimize index use in queries. For instance, in MS SQL Server, your query might *not* use the second column in the index if using the index would take perform more poorly than not using it. Using an index is not always faster than doing a table scan. If there are only a few rows where DsetId is equal to 203, the table scan of that subset could very well be faster than using an index to look up the values. In our MS SQL Server environment, we usually don't even bother to create indexes on more than one field if there are going to be less than a few hundred rows in a subset of a query like that. It takes the server longer to do the index lookup than it would the table scan.
[sqlite] Index with two columns
Hello, please, is there any way to make SQLite use an index on two columns when I want to select all rows which have some combination of the two columns? My table is: CREATE TABLE PointFeature ( DSetId INTEGER, SectId INTEGER, PntItemId INTEGER); CREATE INDEX xxx ON PointFeature (DSetId, SectId, PntItemId); and the query is SELECT * FROM PointFeature WHERE DsetId=203 AND SectId IN (4,400); But the index is used just to find the DsetId and not to find SectId. Is there another form of the SQL which could do that? Or do I have to use two separate queries SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=4; SELECT * FROM PointFeature WHERE DsetId=203 AND SectId=400; Thank you very much, Jakub SELECT DsetId, SectId, pntitemid FROM add.point_features WHERE 1 AND DsetId=203 AND SectId IN (4,435) addropcode p1 p2 p3 0 Goto0 49 1 Integer 2 0 2 OpenRead1 66 keyinfo(3,BINARY,BINARY) 3 KeyAsData 1 1 4 SetNumColumns 1 4 5 Integer 203 0 6 NotNull -1 9 7 Pop 1 0 8 Goto0 47 9 MakeRecord 1 0 iii 10 MemStore0 0 11 MoveGe 1 47 12 MemLoad 0 0 13 IdxGE 1 47 + 14 RowKey 1 0 15 IdxIsNull 1 46 16 Integer 1 0 17 IfNot 1 46 18 MemLoad 1 0 19 If 0 32 20 Integer 1 0 21 MemStore1 1 22 OpenTemp2 0 keyinfo(1,BINARY) 23 SetNumColumns 2 1 24 Integer 4 0 25 MakeRecord 1 0 i 26 String8 0 0 27 PutStrKey 2 0 28 Integer 435 0 29 MakeRecord 1 0 i 30 String8 0 0 31 PutStrKey 2 0 32 Integer 1 0 33 Column 1 1 34 NotNull -1 38 35 Pop 2 0 36 String8 0 0 37 Goto0 41 38 MakeRecord 1 0 i 39 Found 2 41 40 AddImm -1 0 41 IfNot 1 46 42 Column 1 0 43 Column 1 1 44 Column 1 2 45 Callback3 0 46 Next1 12 47 Close 1 0 48 Halt0 0 49 Transaction 2 0 50 VerifyCookie2 79 51 Goto0 1 52 Noop0 0