500290 - Re-engineer PlacesSQLQueryBuilder class, to build more optimized queries, joining least number of tables

Status: RESOLVED WONTFIX

(Toolkit :: Places, defect, P3)

Description

[Marco Bonardo [:mak]]

Actually URI queries need to join historyvisits only to apply some filtering, but there are common paths that do not even require to be aware of all visits.
Joining with all visits can be really slow, and we should do that only if we really need to use those visits data.
We should flatten most useful data to moz_places, we already have visit_count, frecency, last_visit_date, hidden, typed... we could have an is_redirect field for example, and is session really useful (we don't use it really, and is not a commonly appreciated feature).

That would cover vast majority of our needs, we would only need to use historyvisist when we have to query by date or by visit_type, or we need to update moz_places values, or we have a VISIT query.

If this would be possible, many queries would be no more dependent on size of history, and faster.

Comment 1

[Marco Bonardo [:mak]]

morphing bug a bit: best thing would be to split out placesSQLQueryBuilder to a separate source file, and re-engineer it so that it will be able to build really optimized queries based on actual query options.

Comment 2

[Marco Bonardo [:mak]]

I'm marking the bug as wontfix, not because it's not something we want to do, but because we are unlikely to make this kind of change to the existing querying code. We should refactor, or even rewrite, the querying API and consider performance of batch results from the beginning, doing as much as possible in SQL.