178 SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_close ... T ) ) AND Bug.private = FALSE ORDER BY BugTask.importance DESC, BugTask.id LIMIT $INT OFFSET $INT:
GET: 178 Robots: 169 Local: 0
4 https://bugs.launchpad.net/%7Ejcollins/+commentedbugs (Person:+commentedbugs)
OOPS-1762D306, OOPS-1762F338, OOPS-1762M581, OOPS-1762N365
4 https://bugs.launchpad.net/%7Emaco.m/+commentedbugs (Person:+commentedbugs)
OOPS-1762A134, OOPS-1762D130, OOPS-1762F112, OOPS-1762H176
4 https://bugs.launchpad.net/%7Embp/+commentedbugs (Person:+commentedbugs)
OOPS-1762B1197, OOPS-1762H101, OOPS-1762K735, OOPS-1762L1903
OOPS-1762D306:
Branch: launchpad-rev-11756
Revno: 11756
SQL time: 16232 ms
Non-sql time: 214 ms
Total time: 16446 ms
Statement Count: 11
1 15000.0 1 SQL-launchpad-main-slave SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache, Product.answers_usage, Product.blueprints_usage, Product.owner, Product.translations_usage, Product.active, Product.autoupdate, Product.bug_reported_acknowledgement, Product.bug_reporting_guidelines, Product.bug_supervisor, Product.bugtracker, Product.date_next_suggest_packaging, Product.datecreated, Product.description, Product.development_focus, Product.displayname, Product.downloadurl, Product.driver, Product.enable_bug_expiration, Product.freshmeatproject, Product.homepage_content, Product.homepageurl, Product.icon, Product.id, Product.lastdoap, Product.license_approved, Product.license_info, Product.reviewed, Product.logo, Product.max_bug_heat, Product.mugshot, Product.name, Product.official_answers, Product.official_blueprints, Product.official_malone, Product.official_rosetta, Product.private_bugs, Product.programminglang, Product.project, Product.registrant, Product.remote_product, Product.reviewer_whiteboard, Product.screenshotsurl, Product.security_contact, Product.sourceforgeproject, Product.summary, Product.title, Product.translation_focus, Product.translationgroup, Product.translationpermission, Product.wikiurl, SourcePackageName.id, SourcePackageName.name, Bug.date_last_message, Bug.date_last_updated, Bug.date_made_private, Bug.datecreated, Bug.description, Bug.duplicateof, Bug.heat, Bug.heat_last_updated, Bug.id, Bug.latest_patch_uploaded, Bug.message_count, Bug.name, Bug.number_of_duplicates, Bug.owner, Bug.private, Bug.security_related, Bug.title, Bug.users_affected_count, Bug.users_unaffected_count, Bug.who_made_private FROM BugTask LEFT JOIN Bug ON BugTask.bug = Bug.id LEFT JOIN Product ON BugTask.product = Product.id LEFT JOIN SourcePackageName ON BugTask.sourcepackagename = SourcePackageName.id WHERE (1=1) AND Bug.id = BugTask.bug AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, BugMessage, Message WHERE Message.owner = 293289 AND Message.id = BugMessage.message AND BugTask.bug = BugMessage.bug AND Message.id NOT IN ( SELECT BugMessage.message FROM BugMessage WHERE BugMessage.bug = BugTask.bug ORDER BY BugMessage.id LIMIT 1 ) ) AND Bug.private = FALSE ORDER BY BugTask.importance DESC, BugTask.id LIMIT 76 OFFSET 0
I don't think it is an 8.4 issue - I suspect this would perform just as badly under other releases with the right data. Things that stand out are an outer join where a normal join would do, and the IN clauses referencing tables in the outer scope causing them to be reevaluated many times and not scaling.
SELECT BugTask.*, Product.*, SourcePackageNa me.*, Bug.* sourcepackagena me = SourcePackageNa me.id WHERE
(BugTask. status = 10)
Message. owner = 293289
SELECT BugMessage.message FROM BugMessage
WHERE BugMessage.bug = BugTask.bug
ORDER BY BugMessage.id LIMIT 1 ) )
FROM BugTask
LEFT JOIN Bug ON BugTask.bug = Bug.id
LEFT JOIN Product ON BugTask.product = Product.id
LEFT JOIN SourcePackageName ON BugTask.
(1=1)
AND Bug.id = BugTask.bug
AND (
OR (BugTask.status = 15)
OR (BugTask.status = 20)
OR (BugTask.status = 21)
OR (BugTask.status = 22)
OR (BugTask.status = 25))
AND Bug.duplicateof is NULL
AND BugTask.id IN (
SELECT BugTask.id FROM BugTask, BugMessage, Message
WHERE
AND Message.id = BugMessage.message
AND BugTask.bug = BugMessage.bug
AND Message.id NOT IN (
AND Bug.private = FALSE
ORDER BY BugTask.importance DESC, BugTask.id LIMIT 76 OFFSET 0