Closed Bug 482351 Opened 12 years ago Closed 12 years ago

1 sort operation required by mDBInvalidFrecencies

Categories

(Toolkit :: Places, defect)

defect
Not set
normal

Tracking

()

RESOLVED FIXED
mozilla1.9.2a1

People

(Reporter: mak, Assigned: Mardak)

References

Details

(Whiteboard: [fixed by bug 476298])

with bug 481261 this query will warn due to the fact it cannot use an index to sort (1 sort operation required).
Full query is:
SELECT * FROM (
  SELECT h.id, h.url, COALESCE(b.title, h.title), h.rev_host,
         h.visit_count, IFNULL(
           (SELECT visit_date
            FROM moz_historyvisits_temp
            WHERE place_id = h.id
            AND visit_type NOT IN (0,4,7)
            ORDER BY visit_date DESC
            LIMIT 1),
           (SELECT visit_date
            FROM moz_historyvisits
            WHERE place_id = h.id
            AND visit_type NOT IN (0,4,7)
            ORDER BY visit_date DESC
            LIMIT 1)
         ), f.url, null, b.id, b.dateAdded, b.lastModified,
         b.position, b.type, b.fk, b.folder_type,
  FROM moz_bookmarks b
  JOIN moz_places_temp h
    ON b.fk = h.id
  LEFT JOIN moz_favicons f
    ON h.favicon_id = f.id
  WHERE b.parent = ?1
  UNION ALL 
  SELECT h.id, h.url, COALESCE(b.title, h.title), h.rev_host,
         h.visit_count, IFNULL(
           (SELECT visit_date
            FROM moz_historyvisits_temp
            WHERE place_id = h.id
            AND visit_type NOT IN (0,4,7)
            ORDER BY visit_date DESC
            LIMIT 1),
           (SELECT visit_date
            FROM moz_historyvisits
            WHERE place_id = h.id
            AND visit_type NOT IN (0,4,7)
            ORDER BY visit_date DESC
            LIMIT 1)
         ), f.url, null, b.id, b.dateAdded, b.lastModified,
         b.position, b.type, b.fk, b.folder_type,
  FROM moz_bookmarks b
  JOIN moz_places h
    ON b.fk = h.id
  LEFT JOIN moz_favicons f
    ON h.favicon_id = f.id
  WHERE b.parent = ?1
  AND (
    b.fk ISNULL
    OR b.fk NOT IN (SELECT id FROM moz_places_temp)
  )
)
ORDER BY 12 ASC

Where 12 is position.

  SELECT h.id, h.url, COALESCE(b.title, h.title), h.rev_host, h.visit_count, IFNULL( (SELECT visit_date FROM moz_historyvisits_temp WHERE place_id = h.id AND visit_type NOT IN (0,4,7) ORDER BY visit_date DESC LIMIT 1), (SELECT visit_date FROM moz_historyvisits WHERE place_id = h.id AND visit_type NOT IN (0,4,7) ORDER BY visit_date DESC LIM
...and disregard the garbage after I say "Where 12 is position.".  Stupid small text boxes...
oy.  and disregard the commas after b.folder_type.  I was trying what I thought might be a fix and forgot to edit those out...
So, we do have an index here:
CREATE INDEX moz_bookmarks_parentindex ON moz_bookmarks (parent, position)

But, I'm not sure how would ever actually help us since we don't test on both parent and position for anything.  However, making them two separate indexes doesn't help...

We also have:
CREATE INDEX moz_historyvisits_dateindex ON moz_historyvisits (visit_date)
So I don't think the subqueries are our problem
the "problem" is the select * FROM, needs all internal results before starting sorting by 12
Is this the bug about the warning I see every time in my debug build?
WARNING: 1 sort operation has occurred for the SQL statement 'SELECT b.id FROM m
oz_bookmarks b JOIN ( SELECT id FROM moz_places_temp WHERE url = ?1 UNION ALL SE
LECT id FROM moz_places WHERE url = ?1 AND +id NOT IN (SELECT id FROM moz_places
_temp) ) AS h ON b.fk = h.id WHERE b.type = ?2 ORDER BY MAX(IFNULL(b.lastModifie
d, 0), b.dateAdded) DESC, b.id DESC'.  This may indicate an opportunity to impro
ve performance through the careful use of indexes.: file c:/mozilla-build-1.3/sr
c/storage/src/mozStoragePrivateHelpers.cpp, line 105
(In reply to comment #6)
> Is this the bug about the warning I see every time in my debug build?
it is bug 481261
disabled the warning for this query for now:
http://hg.mozilla.org/mozilla-central/rev/9c5103a865e7
Depends on: 476298
http://hg.mozilla.org/mozilla-central/rev/0bf1c26438e1

The query is replaced by the one in FixInvalidFrecencies which doesn't have an ORDER BY but hits on indices:
TABLE moz_places_temp WITH INDEX moz_places_temp_frecencyindex
TABLE moz_places WITH INDEX moz_places_frecencytypedvisitindex
Assignee: nobody → edilee
Status: NEW → RESOLVED
Closed: 12 years ago
No longer depends on: 476298
Flags: in-testsuite-
Flags: in-litmus-
Resolution: --- → FIXED
Whiteboard: [fixed by bug 476298]
Target Milestone: --- → mozilla1.9.2a1
Depends on: 476298
You need to log in before you can comment on or make changes to this bug.