403375 - content pref database does full table scan for most common query

Status: RESOLVED FIXED

(Firefox :: Settings UI, defect, P3)

Description

[Myk Melez [:myk] [@mykmelez]]

Attachment: patch v1: adds indices

The content pref database does a full table scan of the prefs table when the content pref service executes its most common query: retrieving the value of a single pref for some site.

This can be avoided with the judicious use of indices, specifically indices on the name columns in the groups and settings tables and an index across both groupID and settingID in the prefs table.

Here's a patch that adds those indices.

Note: this performance issue won't show up in our existing automated tests because those use fresh profiles with empty databases for which a full table scan looks just as fast as an index-based lookup.  But this patch will make a difference as users accumulate site-specific preference data.

Comment 1

[Myk Melez [:myk] [@mykmelez]]

Requesting wanted-firefox3 on this performance improvement for site-specific preferences.

Comment 2

[Mike Connor [:mconnor]]

Comment on attachment 288190 [details] [diff] [review]
patch v1: adds indices

Looks good, sorry for the delay here.

Comment 3

[Myk Melez [:myk] [@mykmelez]]

Attachment: patch v2: resolves conflicts from bug 385224

Here's a version of the patch that has been updated to fix minor conflicts from the checkin for bug 385224.  This is the version of the patch I'll check in.

Comment 4

[Myk Melez [:myk] [@mykmelez]]

Checking in toolkit/components/contentprefs/src/nsContentPrefService.js;
/cvsroot/mozilla/toolkit/components/contentprefs/src/nsContentPrefService.js,v  <--  nsContentPrefService.js
new revision: 1.12; previous revision: 1.11
done