1800371 - Optimize orphan icons delete query

Status: RESOLVED FIXED

(Toolkit :: Places, enhancement, P5)

Description

[Marco Bonardo [:mak]]

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)

Comment 1

[Marco Bonardo [:mak]]

Attachment: Bug 1800371 - Optimize orphan icons delete query. r=standard8

Avoids a table scan, saving some time.
Functionality is already covered by existing tests.

Comment 2

[Pulsebot]

Pushed by [e-mail]:
https://hg.mozilla.org/integration/autoland/rev/faa4cea6d364
Optimize orphan icons delete query. r=Standard8

Comment 3

[Norisz Fay [:noriszfay]]

https://hg.mozilla.org/mozilla-central/rev/faa4cea6d364