The current query is slower than a NOT EXISTS because it has to extract the id from old query plan 3 0 0 SCAN TABLE moz_pages_w_icons 6 0 0 USING INDEX moz_places_url_hashindex FOR IN-OPERATOR 18 0 0 LIST SUBQUERY 2 21 18 0 SCAN TABLE moz_icons_to_pages 47 0 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) new query plan 3 0 0 SCAN TABLE moz_pages_w_icons 6 0 0 USING INDEX moz_places_url_hashindex FOR IN-OPERATOR 15 0 0 CORRELATED SCALAR SUBQUERY 2 19 15 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) 36 0 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) The new query on my system takes 100ms VS 150ms of the old one
Bug 1800371 Comment 0 Edit History
Note: The actual edited comment in the bug view page will always show the original commenter’s name and original timestamp.
The current query is slower than a NOT EXISTS because it has to extract the id from old query plan 3 0 0 SCAN TABLE moz_pages_w_icons 6 0 0 USING INDEX moz_places_url_hashindex FOR IN-OPERATOR 18 0 0 LIST SUBQUERY 2 21 18 0 SCAN TABLE moz_icons_to_pages 47 0 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) new query plan 3 0 0 SCAN TABLE moz_pages_w_icons 6 0 0 USING INDEX moz_places_url_hashindex FOR IN-OPERATOR 15 0 0 CORRELATED SCALAR SUBQUERY 2 19 15 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) 36 0 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) The new query on my system takes 100ms VS 150ms of the old one (tested updating DB Browser for Sqlite to the latest sqlite3.dll from sqlite.org)
The current query is slower than a NOT EXISTS because it has to extract the id from moz_pages_w_icons old query plan 3 0 0 SCAN TABLE moz_pages_w_icons 6 0 0 USING INDEX moz_places_url_hashindex FOR IN-OPERATOR 18 0 0 LIST SUBQUERY 2 21 18 0 SCAN TABLE moz_icons_to_pages 47 0 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) new query plan 3 0 0 SCAN TABLE moz_pages_w_icons 6 0 0 USING INDEX moz_places_url_hashindex FOR IN-OPERATOR 15 0 0 CORRELATED SCALAR SUBQUERY 2 19 15 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) 36 0 0 SEARCH TABLE moz_icons_to_pages USING PRIMARY KEY (page_id=?) The new query on my system takes 100ms VS 150ms of the old one (tested updating DB Browser for Sqlite to the latest sqlite3.dll from sqlite.org)