Hi folks, 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); And if the sql statement is changed to the following, it runs fast: > explain query plan select id from cards order by cards.question collate nocase order from detail 0 0 TABLE cards WITH INDEX ix_cards_sort ORDER BY 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! Unfortunately breaking the subselect into a join is not an option as the subselect may make use of intersect and except clauses as it gets more complex. Would sqlite be able to handle this situation any better than it does at the moment? Cheers, Damien _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
