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

RESOLVED FIXED in Firefox 41

Status

()

RESOLVED FIXED
3 years ago
3 years ago

People

(Reporter: erahm, Assigned: erahm)

Tracking

(Blocks: 1 bug)

unspecified
mozilla41
Points:
---

Firefox Tracking Flags

(firefox41 fixed)

Details

Attachments

(1 attachment)

(Assignee)

Description

3 years ago
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
(Assignee)

Comment 1

3 years ago
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
(Assignee)

Comment 2

3 years ago
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
(Assignee)

Comment 3

3 years ago
'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.
(Assignee)

Comment 4

3 years ago
Created attachment 8623325 [details] [diff] [review]
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.
Attachment #8623325 - Flags: review?(dietrich)
(Assignee)

Updated

3 years ago
Assignee: nobody → erahm
Status: NEW → ASSIGNED
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
Attachment #8623325 - Flags: review?(dietrich) → review+
https://hg.mozilla.org/mozilla-central/rev/d86bb3dff4ba
Status: ASSIGNED → RESOLVED
Last Resolved: 3 years ago
status-firefox41: --- → fixed
Resolution: --- → FIXED
Target Milestone: --- → mozilla41
You need to log in before you can comment on or make changes to this bug.