1175249 - nsNavBookmarks.cpp causes multiple 'WARNING: Suboptimal indexes for the SQL statement' on first run

Status: RESOLVED FIXED

(Toolkit :: Places, defect)

Description

[Eric Rahm [:erahm]]

On first run I'm seeing multiple warnings about suboptimal indexes. Printing out the sql statement involved shows:

> (lldb) print sqlite3_sql(aStatement)
> (const char *) $3 = 0x000000012268cf10 "SELECT b.id, b.guid, b.parent, b.lastModified, t.guid, t.parent FROM moz_bookmarks b JOIN moz_bookmarks t on t.id = b.parent WHERE b.fk = (SELECT id FROM moz_places WHERE url = :page_url) ORDER BY b.lastModified DESC, b.id DESC "

This points to various statements in nsNavBookmarks.cpp [1,2,3] as possible culprits.

It looks like we're doing something suboptimal with these queries and they should be fixed.

[1] https://hg.mozilla.org/mozilla-central/annotate/ce863f9d8864/toolkit/components/places/nsNavBookmarks.cpp#l2088
[2] https://hg.mozilla.org/mozilla-central/annotate/ce863f9d8864/toolkit/components/places/nsNavBookmarks.cpp#l58
[3] https://hg.mozilla.org/mozilla-central/annotate/ce863f9d8864/toolkit/components/places/nsNavBookmarks.cpp#l58

Comment 1

[Eric Rahm [:erahm]]

Enabling 'MOZ_STORAGE_SORTWARNING_SQL_DUMP' shows 6 bookmarks related warnings are emitted:

> [38716] WARNING: Suboptimal indexes for the SQL statement `SELECT b.id, b.guid, b.parent, b.lastModified, t.guid, t.parent FROM moz_bookmarks b JOIN moz_bookmarks t on t.id = b.parent WHERE b.fk = (SELECT id FROM moz_places WHERE url = :page_url) ORDER BY b.lastModified DESC, b.id DESC ` [1 sort operation(s)] (http://mzl.la/1FuID0j).: file /Users/ericrahm/dev/mozilla-central/storage/mozStoragePrivateHelpers.cpp, line 109
> [38716] WARNING: Suboptimal indexes for the SQL statement `SELECT b.id, b.guid, b.parent, b.lastModified, t.guid, t.parent FROM moz_bookmarks b JOIN moz_bookmarks t on t.id = b.parent WHERE b.fk = (SELECT id FROM moz_places WHERE url = :page_url) ORDER BY b.lastModified DESC, b.id DESC ` [1 sort operation(s)] (http://mzl.la/1FuID0j).: file /Users/ericrahm/dev/mozilla-central/storage/mozStoragePrivateHelpers.cpp, line 109
> [38716] WARNING: Suboptimal indexes for the SQL statement `SELECT b.id, b.guid, b.parent, b.lastModified, t.guid, t.parent FROM moz_bookmarks b JOIN moz_bookmarks t on t.id = b.parent WHERE b.fk = (SELECT id FROM moz_places WHERE url = :page_url) ORDER BY b.lastModified DESC, b.id DESC ` [1 sort operation(s)] (http://mzl.la/1FuID0j).: file /Users/ericrahm/dev/mozilla-central/storage/mozStoragePrivateHelpers.cpp, line 109
> [38716] WARNING: Suboptimal indexes for the SQL statement `SELECT b.id, b.guid, b.parent, b.lastModified, t.guid, t.parent FROM moz_bookmarks b JOIN moz_bookmarks t on t.id = b.parent WHERE b.fk = (SELECT id FROM moz_places WHERE url = :page_url) ORDER BY b.lastModified DESC, b.id DESC ` [1 sort operation(s)] (http://mzl.la/1FuID0j).: file /Users/ericrahm/dev/mozilla-central/storage/mozStoragePrivateHelpers.cpp, line 109
> [38716] WARNING: Suboptimal indexes for the SQL statement `SELECT b.id, b.guid, b.parent, b.lastModified, t.guid, t.parent FROM moz_bookmarks b JOIN moz_bookmarks t on t.id = b.parent WHERE b.fk = (SELECT id FROM moz_places WHERE url = :page_url) ORDER BY b.lastModified DESC, b.id DESC ` [1 sort operation(s)] (http://mzl.la/1FuID0j).: file /Users/ericrahm/dev/mozilla-central/storage/mozStoragePrivateHelpers.cpp, line 109

Comment 2

[Eric Rahm [:erahm]]

Adding additional logging points to |AsyncGetBookmarksForURI::Init| [1] as causing all of the warnings.

[1] https://hg.mozilla.org/mozilla-central/annotate/ce863f9d8864/toolkit/components/places/nsNavBookmarks.cpp#l58

Comment 3

[Eric Rahm [:erahm]]

'WARNING: Suboptimal indexes for the SQL statement' shows up 8,062 times during a linux64 debug test session. Running a small set of reftests with:

> ./mach -l reftest.log reftest image/test/reftest/downscaling

Results in 109 instances of the warning, all originating from nsNavBookmarks.cpp. I'm reasonably certain this accounts for a majority of the 8,062 warnings.

Comment 4

[Eric Rahm [:erahm]]

Attachment: Silence warning about possibly slow query to moz_bookmarks

Add comment ot disable potential slow query warning. The odds that more than one
bookmark for a URI is returned seems pretty low, the odds that there are enough
to warrant an index seems even lower.

Comment 5

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

Comment on attachment 8623325 [details] [diff] [review]
Silence warning about possibly slow query to moz_bookmarks

Review of attachment 8623325 [details] [diff] [review]:
-----------------------------------------------------------------

makes sense, thanks

Comment 6

[Eric Rahm [:erahm]]

https://hg.mozilla.org/integration/mozilla-inbound/rev/d86bb3dff4ba

Comment 7

[Carsten Book [:Tomcat]]

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