564807 - Optimize the directory's gnarly slow DB queries

Status: RESOLVED INCOMPLETE

(Websites :: plugins.mozilla.org, defect)

Description

[Les Orchard [:lorchard]]

To accommodate the fuzzy multidimensional queries required by the search API, the plugin directory makes gnarly DB queries that seem difficult to optimize.  Like these two:

https://bug564567.bugzilla.mozilla.org/attachment.cgi?id=444224

To avoid most of the joins, we could just hit the DB with a query of plugins and releases by mime-type.  Then, take all those results and perform filtering in PHP.  Since the result set from the DB constrained by mime-type should be on the order of 10's of items, this should be not too onerous.

Would still need joins to collect all the columns needed for PHP-side filtering, but they wouldn't be involved in the WHERE clause.  Maybe this will eventually call for some denormalization of the DB

Comment 2

[Sheeri Cabral [:sheeri]]

:lorchard, is this still desired? I see that it was scheduled for q1 2013 back in January, should we make this a priority for q3?

Comment 3

[Les Orchard [:lorchard]]

(In reply to Sheeri Cabral [:sheeri] from comment #2)
> :lorchard, is this still desired? I see that it was scheduled for q1 2013
> back in January, should we make this a priority for q3?

Probably no? I haven't worked on this project in a few of years. Last I heard I think the whole thing was getting rewritten

Comment 4

[Sheeri Cabral [:sheeri]]

OK, I'm resolving incomplete and if it's an issue, someone can re-open or we can make a new bug.