Le 20.10.2010 14:14, Igor Tandetnik a écrit :
> Mathieu Schroeter<schroe...@epsitec.ch> wrote:
>> Le 20.10.2010 12:32, Mathieu Schroeter a écrit :
>>> I've made a small program with this output (please, look at the
>>> attached C code _first_):
>>
>> Mmmh.. sorry but the previous attached code has at least one error
>> and some parts commented. And now I see that without EXPLAIN QUERY
>> PLAN, the comparison callback is called with the second query.
>>
>> There is way in order to have the second query using the index
>> with ORDER BY? It is strange that it is not the case..
>
> Think about how you, a human, would execute such a query with pencil
> and paper. There is no index on lol. So you could a) enumerate all
> entries in lol, b) for each entry look up a corresponding row in tmp
> using its primary key, and c) at the end, after the loop over lol is
> complete, sort all records obtained in step b.
>
> Or, you could a) enumerate all records in tmp in the correct order
> using the index, and b) for each tmp record, scan through all entries
> in lol to see if the ID appears there (remember, there are no indexes
> on lol, full scan is the only option).
>
> Which plan would you choose?
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.
Why not?
The way used by SQLite looks like
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, there is `COLLATE foobar` then
I call the cmp function...
Mathieu
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
