>>SELECT * FROM ART WHERE ID IN (SELECT docid FROM OCR WHERE FullText MATCH >>'mailing')
Thnak You Clemens that's excellent. KR, Marta -----Original Message----- From: Clemens Ladisch Sent: Monday, May 25, 2015 9:59 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] NOT EXISTS and LEFT JOIN Performance problem ShadowMarta at yahoo.de wrote: > ok, understand but I can't see why "NOT IN" would not force a full table > scan but "LEFT JOIN" would ? In a query like "SELECT ... WHERE ID NOT IN (SELECT ...)", the subquery is executed _once_, the results are put into a temporary table/index, and the remaining query can be executed efficiently. (If the subquery were a correlated subquery, it would need to be executed multiple times, and the situation would be different.) > SELECT ART.* FROM ART JOIN (SELECT docid FROM OCR WHERE FullText MATCH > 'mailing') AS ftstable WHERE ART.ID = docid; This is correct, but more complex than needed: SELECT * FROM ART WHERE ID IN (SELECT docid FROM OCR WHERE FullText MATCH 'mailing') There is no need to try to force every query to use joins. Some databases are said to have optimizers that work much better with joins, but this is not true for SQLite. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
