Index with two columns

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Index with two columns

Jakub Adamek
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)
addr opcode p1 p2 p3
0 Goto 0 49
1 Integer 2 0
2 OpenRead 1 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 Goto 0 47
9 MakeRecord 1 0 iii
10 MemStore 0 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 MemStore 1 1
22 OpenTemp 2 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 Goto 0 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 Callback 3 0
46 Next 1 12
47 Close 1 0
48 Halt 0 0
49 Transaction 2 0
50 VerifyCookie 2 79
51 Goto 0 1
52 Noop 0 0

Reply | Threaded
Open this post in threaded view
|

Re: Index with two columns

Derrell Lipman
Jakub Adamek <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Index with two columns

Brad-19
In reply to this post by Jakub Adamek
> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Index with two columns

D. Richard Hipp
In reply to this post by Jakub Adamek
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 <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Running Sqlite in 64-bit mode on Win XP x64???

basil thomas
In reply to this post by Brad-19
To all
Has anyone run sqlite in 64-bit mode on the new Win XP x64 os???
Have you been able to create an in memory database greater than 2
gigabytes???
What is the performance compared to sqlite in 32-bit mode??
If no to any of the above, can Sqlite run in full 64-bit mode anyway???

Reply | Threaded
Open this post in threaded view
|

Re: Running Sqlite in 64-bit mode on Win XP x64???

Cory Nelson
On 6/3/05, basil thomas <[hidden email]> wrote:
> To all
> Has anyone run sqlite in 64-bit mode on the new Win XP x64 os???

Yup.  It is running beautifully for me in PeerGuardian.

> Have you been able to create an in memory database greater than 2
> gigabytes???

Can't say I've tried.

> What is the performance compared to sqlite in 32-bit mode??

The majority of work is I/O bound, so the performance is about the
same.  Maybe I can run some tests on in memory databases.

> If no to any of the above, can Sqlite run in full 64-bit mode anyway???
>
>


--
Cory Nelson
http://www.int64.org
Reply | Threaded
Open this post in threaded view
|

Re: Index with two columns

Jakub Adamek
In reply to this post by D. Richard Hipp
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;
>