Re: [sqlite] Custom collating sequences and performance

[Duquette, William H (316H)]

On 11/18/10 8:57 PM, "Dan Kennedy"  wrote:

On 11/19/2010 05:22 AM, Duquette, William H (316H) wrote:
> On 11/18/10 2:16 PM, "Drake Wilson"  wrote:
>
> Quoth "Duquette, William H (316H)", on 
> 2010-11-18 14:08:10 -0800:
>> It seems to me that it shouldn't be necessary for SQLite to evaluate
>> FOO's comparison function when doing queries on mytable; the
>> collation order should be implicit in the mykey column's index.  Is
>> this in fact the case?
>
> When doing which queries?
>
> How do you propose to look up a key value in the index without using
> the collation function?
>
> I was thinking of queries like
>
>  SELECT mykey FROM mytab ORDER BY mykey

The collation sequence callback will not be invoked in that
case. SQLite will just iterate from start to finish of the
index b-tree. You do need to have the collation sequence
registered when compiling the query though.

Dan.

Thanks, Dan.  That's what I was expecting. :-)

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

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] Custom collating sequences and performance

[Dan Kennedy]

On 11/19/2010 05:22 AM, Duquette, William H (316H) wrote:
> On 11/18/10 2:16 PM, "Drake Wilson"  wrote:
>
> Quoth "Duquette, William H (316H)", on 
> 2010-11-18 14:08:10 -0800:
>> It seems to me that it shouldn't be necessary for SQLite to evaluate
>> FOO's comparison function when doing queries on mytable; the
>> collation order should be implicit in the mykey column's index.  Is
>> this in fact the case?
>
> When doing which queries?
>
> How do you propose to look up a key value in the index without using
> the collation function?
>
> I was thinking of queries like
>
>  SELECT mykey FROM mytab ORDER BY mykey

The collation sequence callback will not be invoked in that
case. SQLite will just iterate from start to finish of the
index b-tree. You do need to have the collation sequence
registered when compiling the query though.

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

Re: [sqlite] Custom collating sequences and performance

[Duquette, William H (316H)]

On 11/18/10 2:16 PM, "Drake Wilson"  wrote:

Quoth "Duquette, William H (316H)" , on 
2010-11-18 14:08:10 -0800:
> It seems to me that it shouldn't be necessary for SQLite to evaluate
> FOO's comparison function when doing queries on mytable; the
> collation order should be implicit in the mykey column's index.  Is
> this in fact the case?

When doing which queries?

How do you propose to look up a key value in the index without using
the collation function?

I was thinking of queries like

SELECT mykey FROM mytab ORDER BY mykey

But yes, looking up a particular record would have
to use the collation function function, wouldn't it.

Will

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] Custom collating sequences and performance

[Drake Wilson]

Quoth "Duquette, William H (316H)" , on 
2010-11-18 14:08:10 -0800:
> It seems to me that it shouldn't be necessary for SQLite to evaluate
> FOO's comparison function when doing queries on mytable; the
> collation order should be implicit in the mykey column's index.  Is
> this in fact the case?

When doing which queries?

How do you propose to look up a key value in the index without using
the collation function?

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

[sqlite] Custom collating sequences and performance

[Duquette, William H (316H)]

Suppose I define a custom collating sequence FOO, and use it on an indexed 
column:

   CREATE TABLE mytable (mykey TEXT PRIMARY KEY COLLATE FOO, ...)

It seems to me that it shouldn't be necessary for SQLite to evaluate FOO's 
comparison function when doing queries on mytable; the collation order should 
be implicit in the mykey column's index.  Is this in fact the case?

Thanks!

Will


--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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