Re: [sqlite] Support for clustered indexing (or something similar)?

[Dan]

On Oct 27, 2008, at 12:38 PM, Julian Bui wrote:

> Thanks for the reply dan.
>
> You probably don't "need" clustered indexing as such, but this would  
> be
>>
>> the kind of case where it provides some advantages. You can get the  
>> same
>> effect in SQLite by including all the data columns in your index
>> definition.
>>
>>
> Unfortunately, because I will be making very frequent inserts (every
> 10-100ms), an index spanning all columns would be much too expensive  
> and
> would hurt insert times.

Have you been able to verify this experimentally? Predicting performance
is a tricky exercise.

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for clustered indexing (or something similar)?

[Julian Bui]

Thanks for the reply dan.

You probably don't "need" clustered indexing as such, but this would be
>
> the kind of case where it provides some advantages. You can get the same
> effect in SQLite by including all the data columns in your index
> definition.
>
>
Unfortunately, because I will be making very frequent inserts (every
10-100ms), an index spanning all columns would be much too expensive and
would hurt insert times.

Julian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for clustered indexing (or something similar)?

[Dan]

On Oct 26, 2008, at 5:15 PM, Julian Bui wrote:

> Hi all,
>
> I have records w/ a timestamp attribute which is not unique and  
> cannot be
> used as a primary key.  These records will be inserted according to
> timestamp value.  From this important fact, I've gathered I need a  
> clustered
> index since my SELECT statements use a time-range in the WHERE clause.
>
> Does sqlite support clustered indexing or something that would take
> advantage of nature of my records and the nature of how they're being
> inserted?

You probably don't "need" clustered indexing as such, but this would be
the kind of case where it provides some advantages. You can get the same
effect in SQLite by including all the data columns in your index  
definition.
The data will be stored in the database twice, but SELECT queries on  
ranges
of timestamps will get the same benefits that a clustered index would  
provide.

For Example, if this is your table:

   CREATE TABLE t1(time TIMESTAMP, data1, data2, data3);

Then you do:

   CREATE INDEX time_idx ON t1(time);
   SELECT * FROM t1 WHERE time >= '2008-10-25 10:00' AND time <=  
'2008-10-25 11:00';

then SQLite will use the index to find the set of rows that match the  
WHERE
constraint. Each index entry contains a reference to the corresponding  
entry
in the main table structure that SQLite will use to find the values of
data1, data2 and data3 to return. i.e. a non-clustered index.

If you do this instead:

   CREATE INDEX time_idx ON t1(time, data1, data2, data3);
   SELECT * FROM t1 WHERE time >= '2008-10-25 10:00' AND time <=  
'2008-10-25 11:00';

Then each index entry will have the corresponding values of data1,  
data2 and
data3 stored with it. In this case, there is no need to seek to the  
corresponding
table entry for each matching row, as the data stored as part of the  
index
entry can be inspected to find the values of data1, data2 and data3 to  
return.
Just like a clustered index.

As with all performance tips, you should probably test this with your  
specific
data to make sure it really does provide some benefits.

Dan.





> Please let me know.
>
> Thanks,
> Julian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users