Re: [sqlite] Re: multi column select

[RaghavendraK 70574]

Thank u.
**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: Igor Tandetnik <[EMAIL PROTECTED]>
Date: Wednesday, September 12, 2007 8:10 pm
Subject: [sqlite] Re: multi column select

> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > Can anyone explain how does the below sql statmenent work,
> >
> > select * from tbl1 where a=xx and b=yy;
> >
> > when a is indexed and b is
> > indexed seperately?
> 
> The query can only use an index on one of the fields (the 
> optimizer will 
> try to guess which one).
> 
> > I tried explain could not make out. Does it
> > select all records with t=111 and then do a search for b=222 with
> > in that set
> 
> Either that, or the other way round - use the index to find 
> records with 
> b=222, then scan them linearly to find those with t=111
> 
> > From the trace below i don;t see idx1 being used.
> 
> > 2|OpenRead|0|2|
> 
> This opens the table itself.
> 
> > 5|OpenRead|1|4|keyinfo(1,BINARY)
> 
> This opens one of the indexes (look in sqlite_master table to 
> figure out 
> which one). Again, only one index is used. You can also run this 
> query:
> explain query plan select * from test where t='111' and b='222';
> 
> The output summarizes which tables and which indexes are used to 
> satisfy 
> the request.
> 
> Igor Tandetnik 
> 
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: multi column select

[Igor Tandetnik]

RaghavendraK 70574
<[EMAIL PROTECTED]> wrote:

Can anyone explain how does the below sql statmenent work,

select * from tbl1 where a=xx and b=yy;

when a is indexed and b is
indexed seperately?


The query can only use an index on one of the fields (the optimizer will 
try to guess which one).



I tried explain could not make out. Does it
select all records with t=111 and then do a search for b=222 with
in that set


Either that, or the other way round - use the index to find records with 
b=222, then scan them linearly to find those with t=111



From the trace below i don;t see idx1 being used.



2|OpenRead|0|2|


This opens the table itself.


5|OpenRead|1|4|keyinfo(1,BINARY)


This opens one of the indexes (look in sqlite_master table to figure out 
which one). Again, only one index is used. You can also run this query:


explain query plan select * from test where t='111' and b='222';

The output summarizes which tables and which indexes are used to satisfy 
the request.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-