Le 20.10.2010 16:44, Pavel Ivanov a écrit :
>> It seems legitimate to use the idx_foobar because it is already
>> sorted.. no?
>
> Yes, it is sorted. So for example you have 2 values and you need to
> put them in order. You know that these values exist somewhere in the
> index in exact order you need. How would you find the order? You'll
> need to scan the whole index to find values you have (you don't know
> where exactly they are in the index) and then you'll know what order
> they have. And you have to repeat this index scan for each value you
> have. Do you think it would be faster than just calling your
> comparison function?
Yes, okay... I was imagining an index like this:
/* for one data in a table */
struct data {
void *value;
...
const struct idx *idx; /* ptr on the entry in 'idx_foobar' */
} *my_data_in_the_table
/* for an index */
struct idx {
struct idx *next;
struct idx *prev;
...
const void *data; /* ptr on 'my_data_in_the_table' */
} *idx_foobar
To fix my problem then I must create my own high level index ...
Thanks everyone!
Mathieu
>
>
> Pavel
>
> On Wed, Oct 20, 2010 at 10:30 AM, Mathieu Schroeter
> <schroe...@epsitec.ch> wrote:
>> Le 20.10.2010 15:51, Drake Wilson a écrit :
>>> Quoth Mathieu Schroeter<schroe...@epsitec.ch>, on 2010-10-20 15:41:51 +0200:
>>>> Thanks for your interesting answer.
>>>>
>>>> The first plan looks the best but it does not explain the ORDER BY.
>>>> If I could done this job with pencil and paper, my way will be:
>>>>
>>>> a) enumerate all entries in lol
>>>> b) for each entry look up a corresponding row in tmp using its
>>>> primary key
>>>> c) the loop over lol is complete, I see that tmp.data uses
>>>> `COLLATE foobar` then I look if an index exists.
>>>> d) Yes then I use idx_foobar instead of many calls on the
>>>> comparison function.
>>>
>>> In step (d), you're doing what? Sorting the resulting rows? How
>>> exactly would you use the index for that?
>>>
>>
>> Well, I don't know how are implemented the indexes. I know only
>> that the index (idx_foobar) is ordered and this one is populated
>> at the same time that the INSERT.
>>
>> CREATE INDEX idx_foobar ON tmp (data COLLATE foobar);
>>
>> INSERT INTO tmp (data) values (CAST(x'0100' AS TEXT));
>> INSERT INTO tmp (data) values (CAST(x'0400' AS TEXT));
>> ...
>>
>> and CAST() is used because I have a BLOB in my table...
>>
>>
>> I would like to save time for this query (no index for lol.id):
>>
>> SELECT data
>> FROM lol INNER JOIN tmp
>> ON lol.id = tmp.id
>> ORDER BY tmp.data COLLATE foobar;
>>
>>
>> Now each row in the result are using the comparison function provided
>> by foobar. But the comparisons were already done when the data were
>> inserted in the table?!?
>>
>> It seems legitimate to use the idx_foobar because it is already
>> sorted.. no?
>>
>>
>> Maybe I've not understood what is an index.
>>
>>
>> Mathieu
>>
>>
>> _______________________________________________
>> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
