Re: [sqlite] Long retrieval times

[R S]

Thanks everyone for your response.

Dennis, that works great!
Jay, I think  I presume you meant (x >= 'a'). This works great too!

Thank you again!


On 8/2/05, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Thomas Briggs wrote:
> 
> >
> >
> >
> >
> >>However if I use something like:
> >>select * from myTable where column2!='';
> >>(Takes a long time to return).
> >>
> >>I guess because the column in that row isn't indexed? Any
> >>alternatives?
> >>
> >>
> >
> >   I don't believe that indices can be used to satisfy != conditions, so
> >even if the column is indexed, you always have to do a table scan to
> >find rows matching the criteria.  That can obviously take some time
> >depending on the size of the table being scanned.
> >
> >   -Tom
> >
> >
> >
> You can get the same effect as != using a union to combine the results
> of two queries that can use the index, one using <, and the other using >.
> 
> select * from myTable where column2 < ''
> union
> select * from myTable where column2 > ''
> 
> This produces the same results as != (even skipping columns where
> column2 is NULL), but uses the index to speed up locating the rows that
> meet the criterion.
> 
> I'm not sure if it really is faster in your application, but it may be
> much faster if there are many rows that equal the criterion and are
> skipped by using the index. If most rows are selected then there is
> probably little gain.
> 
> HTH
> Dennis Cote
>

Re: [sqlite] Long retrieval times

[Dennis Cote]

Thomas Briggs wrote:




 


However if I use something like:
select * from myTable where column2!='';
(Takes a long time to return).

I guess because the column in that row isn't indexed? Any 
alternatives?
   



  I don't believe that indices can be used to satisfy != conditions, so
even if the column is indexed, you always have to do a table scan to
find rows matching the criteria.  That can obviously take some time
depending on the size of the table being scanned.

  -Tom

 

You can get the same effect as != using a union to combine the results 
of two queries that can use the index, one using <, and the other using >.


select * from myTable where column2 < ''
union
select * from myTable where column2 > ''

This produces the same results as != (even skipping columns where 
column2 is NULL), but uses the index to speed up locating the rows that 
meet the criterion.


I'm not sure if it really is faster in your application, but it may be 
much faster if there are many rows that equal the criterion and are 
skipped by using the index. If most rows are selected then there is 
probably little gain.


HTH
Dennis Cote

Re: [sqlite] Long retrieval times

[Jay Sprenkle]

did you try this instead of  not equal?
 (x >= 'abc' AND x < 'abd').

RE: [sqlite] Long retrieval times

[Thomas Briggs]

 

> However if I use something like:
> select * from myTable where column2!='';
> (Takes a long time to return).
> 
> I guess because the column in that row isn't indexed? Any 
> alternatives?

   I don't believe that indices can be used to satisfy != conditions, so
even if the column is indexed, you always have to do a table scan to
find rows matching the criteria.  That can obviously take some time
depending on the size of the table being scanned.

   -Tom

[sqlite] Long retrieval times

[R S]

Hi,
   I have a DB containing about 6 million records with my indexes
properly created. When I run a query against a particular column where
a particular (varchar) value is present, it returns very quickly.

e.g.
select * from myTable where column2='abcd';
(Returns pretty quick)

However if I use something like:
select * from myTable where column2!='';
(Takes a long time to return).

I guess because the column in that row isn't indexed? Any alternatives?
Thanks!