> 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
To fix your imagination just ask yourself a question: what if I have
several indexes on the table? Should data in the table have pointers
to all indexes? So on creation of each index the whole table structure
should change? What about deletion of the index? Again the whole table
structure should change?
Index structure is roughly correct except some additional tricks to
allow quick search of a value in the middle of the index.
Pavel
On Wed, Oct 20, 2010 at 11:10 AM, Mathieu Schroeter
<schroe...@epsitec.ch> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
