Comment 2 for bug 919413

The change does not work; charms/mysql has no results. The query got slower, some searches like 'ubuntu/gedit' never work on qastaging, but they reliably did yesterday. This is behind a feature flag only enabled on qastaging so I can safely work on this.

I am dismayed to see two near identical and expensive queries in OOPS-191b0f55deb4f3d3f35a25be045ce338 which I believe are caused by the CountableIterator call in the method. I wonder how the the duplication can be cached

1. 587.0 1 SQL-main-slave
WITH SearchableDSP AS
  ( SELECT dsp.id, dsps.name, dsps.binpkgnames, rank
   FROM DistributionSourcePackage dsp
   JOIN
     ( SELECT DISTINCT ON (spn.id) spn.id, spn.name, dspc.binpkgnames, CASE WHEN spn.name = u$STRING THEN $INT WHEN dspc.binpkgnames SIMILAR TO $STRING || u$STRING || $STRING THEN $INT WHEN spn.name SIMILAR TO $STRING || u$STRING || $STRING THEN $INT WHEN dspc.binpkgnames SIMILAR TO $STRING || u$STRING || $STRING THEN $INT ELSE $INT END AS rank
      FROM SourcePackageName spn
      LEFT JOIN DistributionSourcePackageCache dspc ON dspc.sourcepackagename = spn.id
      LEFT JOIN Archive a ON dspc.archive = a.id
      AND a.purpose IN ($INT, $INT)
      WHERE spn.name LIKE $STRING || u$STRING || $STRING
        OR dspc.binpkgnames LIKE $STRING || u$STRING || $STRING LIMIT $INT ) dsps ON dsp.sourcepackagename = dsps.id
   WHERE dsp.distribution = $INT
   ORDER BY rank DESC )
SELECT COUNT(*)
FROM SearchableDSP,
     DistributionSourcePackage
WHERE (DistributionSourcePackage.id = SearchableDSP.id)

2. 557.0 1 SQL-main-slave
WITH SearchableDSP AS
  ( SELECT dsp.id, dsps.name, dsps.binpkgnames, rank
   FROM DistributionSourcePackage dsp
   JOIN
     ( SELECT DISTINCT ON (spn.id) spn.id, spn.name, dspc.binpkgnames, CASE WHEN spn.name = u$STRING THEN $INT WHEN dspc.binpkgnames SIMILAR TO $STRING || u$STRING || $STRING THEN $INT WHEN spn.name SIMILAR TO $STRING || u$STRING || $STRING THEN $INT WHEN dspc.binpkgnames SIMILAR TO $STRING || u$STRING || $STRING THEN $INT ELSE $INT END AS rank
      FROM SourcePackageName spn
      LEFT JOIN DistributionSourcePackageCache dspc ON dspc.sourcepackagename = spn.id
      LEFT JOIN Archive a ON dspc.archive = a.id
      AND a.purpose IN ($INT, $INT)
      WHERE spn.name LIKE $STRING || u$STRING || $STRING
        OR dspc.binpkgnames LIKE $STRING || u$STRING || $STRING LIMIT $INT ) dsps ON dsp.sourcepackagename = dsps.id
   WHERE dsp.distribution = $INT
   ORDER BY rank DESC )
SELECT DistributionSourcePackage.bug_count,
       DistributionSourcePackage.bug_reported_acknowledgement,
       DistributionSourcePackage.bug_reporting_guidelines,
       DistributionSourcePackage.distribution,
       DistributionSourcePackage.enable_bugfiling_duplicate_search,
       DistributionSourcePackage.id,
       DistributionSourcePackage.is_upstream_link_allowed,
       DistributionSourcePackage.max_bug_heat,
       DistributionSourcePackage.po_message_count,
       DistributionSourcePackage.sourcepackagename,
       DistributionSourcePackage.total_bug_heat,
       binpkgnames
FROM SearchableDSP,
     DistributionSourcePackage
WHERE (DistributionSourcePackage.id = SearchableDSP.id)LIMIT $INT
OFFSET $INT