"Damien Elmes" <reso...@ichi2.net> wrote
in message
news:625e49d20904042027y7c029d0fu2cc3812143225...@mail.gmail.com
> I have the following query:
>
>> explain query plan select id from cards where id in (select cardId
>> from cardTags where cardTags.tagId in (246)) order by cards.question
>> collate nocase
> order from detail
> 0 0 TABLE cards USING PRIMARY KEY
> 0 0 TABLE cardTags WITH INDEX ix_cardTags_tagCard
>
> It runs rather slowly. There is an index for the order:
>
> CREATE INDEX ix_cards_sort on cards (question collate nocase);
SQLite can only use one index per table. Given a choice between using
one on cards.id to satisfy the WHERE clause and one on cards(question)
to satisfy ORDER BY, it chose the former. This could be a poor choice if
the WHERE clause selects a significant percentage of all records. To
suppress the use of this index, write "... where +id in ...".
> So it seems that the subselect is preventing the ordering index from
> being used. What's interesting is that if I select the ids in a
> different sql statement, concatenate them together in a big list of
> numbers, and supply that in the extra statement, the ordering index
> is still not used, but the query runs twice as fast!
Is it still faster if you include the time of running that separate
statement for selecting IDs?
--
With best wishes,
Igor Tandetnik
With sufficient thrust, pigs fly just fine. However, this is not
necessarily a good idea. It is hard to be sure where they are going to
land, and it could be dangerous sitting under them as they fly
overhead. -- RFC 1925
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
