1293375 - 2,500 "Suboptimal indexes for the SQL statement" emitted from nsDiskCacheDeviceSQL.cpp during linux64 debug testing

Status: RESOLVED FIXED

(Core :: Networking, defect, P5)

Description

[Eric Rahm [:erahm]]

> 884 WARNING: Suboptimal indexes for the SQL statement 0x7fdc6dc3d550 (http://mzl.la/1FuID0j).: file storage/mozStoragePrivateHelpers.cpp, line 114

This warning [1] shows up in the following test suites:

>    884 - desktop-test-linux64/debug-web-platform-tests-5 5

It shows up in 605 tests. A few of the most prevalent:

>    121 -        /html/semantics/document-metadata/the-meta-element/pragma-directives/attr-meta-http-equiv-refresh/parsing.html
>     25 -        /html/infrastructure/urls/resolving-urls/query-encoding/utf-16le.html
>     25 -        /html/infrastructure/urls/resolving-urls/query-encoding/windows-1252.html
>     25 -        /html/infrastructure/urls/resolving-urls/query-encoding/utf-8.html
>     25 -        /html/infrastructure/urls/resolving-urls/query-encoding/windows-1251.html
>      5 -        /html/semantics/embedded-content/the-img-element/sizes/parse-a-sizes-attribute.html
>      5 -        /html/semantics/scripting-1/the-template-element/definitions/template-contents.html
>      4 -        /html/semantics/forms/form-submission-0/url-encoded.html
>      4 -        /html/semantics/document-metadata/the-base-element/base_multiple.html
>      4 -        /html/semantics/scripting-1/the-template-element/template-element/template-descendant-frameset.html

[1] https://hg.mozilla.org/mozilla-central/annotate/e78975b53563/storage/mozStoragePrivateHelpers.cpp#l114

Comment 1

[Eric Rahm [:erahm]]

https://treeherder.mozilla.org/#/jobs?repo=try&revision=064ea3fd1dfa

Comment 5

[Eric Rahm [:erahm]]

The majority of these appear to be from two SQL statements in nsDiskCacheDeviceSQL.cpp [1]:

>  1258 WARNING: Suboptimal indexes for the SQL statement `SELECT ClientID, ItemType FROM moz_cache WHERE Key = ? ORDER BY LastFetched DESC, LastModified DESC;` [1 sort operation(s)] (http://mzl.la/1FuID0j).: file storage/mozStoragePrivateHelpers.cpp, line 114
>  1258 WARNING: Suboptimal indexes for the SQL statement `SELECT ns.ClientID, ns.ItemType FROM  moz_cache_namespaces AS ns JOIN moz_cache_groups AS groups  ON ns.ClientID = groups.ActiveClientID WHERE ns.NameSpace <= ?1 AND ?1 GLOB ns.NameSpace || '*' ORDER BY ns.NameSpace DESC, groups.ActivateTimeStamp DESC;` [1 sort operation(s)] (http://mzl.la/1FuID0j).: file storage/mozStoragePrivateHelpers.cpp, line 114

Looks like dcamp added these back in 2008 in bug 442813. Dave would you mind taking a look at this or redirecting to someone more appropriate? The general rule here is either it's possibly a perf hit and we should add an index or we can annotate the statement to avoid the warning.

[1] https://dxr.mozilla.org/mozilla-central/rev/e78975b53563d80c99ebfbdf8a9fbf6b829a8a48/netwerk/cache/nsDiskCacheDeviceSQL.cpp#1275-1283

Comment 6

[Shih-Chiang Chien [:schien] (UTC+8) (use ni? plz)]

ni? @michal since he might have some idea as well.

Comment 7

[Honza Bambas (:mayhemer)]

And Michal had an idea asking me :D  Yes, I'm probably the right person.  

This is appcache which we no longer maintain.  We don't update the appcache code unless it's a serious stability or security issue to fix.

Please annotate the statement to disable the warning.  Thanks.

Comment 8

[Eric Rahm [:erahm]]

Attachment: Annotate sql index warnings in nsDiskCacheDeviceSQL

This annotates two SQL statements that are responsible for a large amount of
warning spam due to "suboptimal indexes." Given we no longer wish to update
appcache code we can just disable the warning with a commment instead.

Comment 9

[Honza Bambas (:mayhemer)]

Comment on attachment 8949915 [details] [diff] [review]
Annotate sql index warnings in nsDiskCacheDeviceSQL

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

if this is the annotation to shut it up, then let's do it.

thanks!

Comment 10

[Pulsebot]

Pushed by [e-mail]:
https://hg.mozilla.org/integration/mozilla-inbound/rev/1354d17a426b
Annotate sql index warnings in nsDiskCacheDeviceSQL. r=mayhemer

Comment 11

[Andreea Pavel [:apavel]]

https://hg.mozilla.org/mozilla-central/rev/1354d17a426b