Re: [sqlite] suggestion for an optimized sql

2007-12-20 Thread Ken
It may be the case where the index will cause more harm than good in this 
instance depending upon the percentage matching. In other words if only 10% of 
the records match using the index great... But if say 90% match the index this 
would be Very Bad and A Full scan would be quicker.

I full table scan is not always a bad thing. It just depends upon the 
percentage of rows you need returned.



Clodo <[EMAIL PROTECTED]> wrote: Thanks to all great people that help me. I 
will create a specific field 
with an index on that. Bye!
> You could create a field in the table Value01LessThanValue02 and use a
> trigger to update this value whenever data is updated.  Then you can search
> on just this one field.  However, it's a boolean result so depending on the
> percentage of records that match this condition, the index may not be that
> helpful in the end anyways.
>
> HTH,
>
> Sam
>  
> ---
> We're Hiring! Seeking a passionate developer to join our team building Flex
> based products. Position is in the Washington D.C. metro area. If interested
> contact [EMAIL PROTECTED]
>  
> -Original Message-
> From: Clodo [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, December 20, 2007 5:36 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] suggestion for an optimized sql
>
> Hi, i have a table like this with thousand of records:
>
> CREATE TABLE Table01 (
>   id   integer,
>   value01  text,
>   value02  text
> );
>
> I need to optimize the following sql:
>
> SELECT * FROM Table01 WHERE VALUE01
>
> How i can use indexes to avoid a full-table scan? Thanks!
>
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>   


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




Re: [sqlite] suggestion for an optimized sql

2007-12-20 Thread Clodo
Thanks to all great people that help me. I will create a specific field 
with an index on that. Bye!

You could create a field in the table Value01LessThanValue02 and use a
trigger to update this value whenever data is updated.  Then you can search
on just this one field.  However, it's a boolean result so depending on the
percentage of records that match this condition, the index may not be that
helpful in the end anyways.

HTH,

Sam
 
---

We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Clodo [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 20, 2007 5:36 AM

To: sqlite-users@sqlite.org
Subject: [sqlite] suggestion for an optimized sql

Hi, i have a table like this with thousand of records:

CREATE TABLE Table01 (
  id   integer,
  value01  text,
  value02  text
);

I need to optimize the following sql:

SELECT * FROM Table01 WHERE VALUE01  



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



RE: [sqlite] suggestion for an optimized sql

2007-12-20 Thread Ken
Continuing with Sams thought process,

   Create another table that only contains record id's where value01=value02

Then just query table01_a

The use of an index is probably not going to help and your better off most 
likely performing the full table scan especially if you are getting a 
significant chunk of the table and the records more than likely have an even 
distribution.



"Samuel R. Neff" <[EMAIL PROTECTED]> wrote: 
You could create a field in the table Value01LessThanValue02 and use a
trigger to update this value whenever data is updated.  Then you can search
on just this one field.  However, it's a boolean result so depending on the
percentage of records that match this condition, the index may not be that
helpful in the end anyways.

HTH,

Sam
 
---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Clodo [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 20, 2007 5:36 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] suggestion for an optimized sql

Hi, i have a table like this with thousand of records:

CREATE TABLE Table01 (
  id   integer,
  value01  text,
  value02  text
);

I need to optimize the following sql:

SELECT * FROM Table01 WHERE VALUE01

How i can use indexes to avoid a full-table scan? Thanks!



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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




RE: [sqlite] suggestion for an optimized sql

2007-12-20 Thread Samuel R. Neff

You could create a field in the table Value01LessThanValue02 and use a
trigger to update this value whenever data is updated.  Then you can search
on just this one field.  However, it's a boolean result so depending on the
percentage of records that match this condition, the index may not be that
helpful in the end anyways.

HTH,

Sam
 
---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Clodo [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 20, 2007 5:36 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] suggestion for an optimized sql

Hi, i have a table like this with thousand of records:

CREATE TABLE Table01 (
  id   integer,
  value01  text,
  value02  text
);

I need to optimize the following sql:

SELECT * FROM Table01 WHERE VALUE01

Re: [sqlite] suggestion for an optimized sql

2007-12-20 Thread Kees Nuyt
On Thu, 20 Dec 2007 11:36:29 +0100, Clodo <[EMAIL PROTECTED]>
wrote:

>Hi, i have a table like this with thousand of records:
>
>CREATE TABLE Table01 (
>  id   integer,
>  value01  text,
>  value02  text
>);
>
>I need to optimize the following sql:
>
>SELECT * FROM Table01 WHERE VALUE01
>How i can use indexes to avoid a full-table scan? Thanks!

You could create an index on (value01) or on (value01,value02),
but it probably wouldn't help at all.
Being TEXT columns, the index could potentially be large.
A full table scan might be replaced by a full index scan.
I don't think it would be any faster.

I would say: try it and compare the results.

Use
EXPLAIN SELECT * FROM Table01 WHERE value01