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

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