482351 - 1 sort operation required by mDBInvalidFrecencies

Status: RESOLVED FIXED

(Toolkit :: Places, defect)

Description

[Marco Bonardo [:mak]]

with bug 481261 this query will warn due to the fact it cannot use an index to sort (1 sort operation required).

Comment 1

[Shawn Wilsher :sdwilsh]

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

Comment 2

[Shawn Wilsher :sdwilsh]

...and disregard the garbage after I say "Where 12 is position.".  Stupid small text boxes...

Comment 3

[Shawn Wilsher :sdwilsh]

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...

Comment 4

[Shawn Wilsher :sdwilsh]

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

Comment 5

[Marco Bonardo [:mak]]

the "problem" is the select * FROM, needs all internal results before starting sorting by 12

Comment 6

[Martijn Wargers (dead)]

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

Comment 7

[Marco Bonardo [:mak]]

(In reply to comment #6)
> Is this the bug about the warning I see every time in my debug build?
it is bug 481261

Comment 8

[Shawn Wilsher :sdwilsh]

disabled the warning for this query for now:
http://hg.mozilla.org/mozilla-central/rev/9c5103a865e7

Comment 9

[Ed Lee :Mardak]

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