[slow query] Fix slow sphinx queries

RESOLVED FIXED in 4.x (triaged)


addons.mozilla.org Graveyard
Code Quality
7 years ago
2 years ago


(Reporter: clouserw, Assigned: jbalogh)


4.x (triaged)


(Whiteboard: [waiting on elastic search])



7 years ago
Seeing a bunch of queries in this format in the slave slow query log.  Most are taking 19-20 seconds to do.

# Time: 100412 12:30:19
# User@Host: sphinx[sphinx] @  []
# Query_time: 20  Lock_time: 0  Rows_sent: 147023  Rows_examined: 3328454
SET timestamp=1271100619;
SELECT         id, app, addon_id, type, status as addon_status, locale,         locale_ord, averagerating, weeklydownloads, totaldownloads,         inactive, guid_ord,        name, name AS name_ord,         homepage, description, developercomments,         (             SELECT 1 FROM features f             WHERE f.addon_id=t.addon_id                 AND f.start <= NOW()                 AND f.end > NOW()                 AND (f.locale IS NULL OR t.locale = f.locale)                 AND t.app = f.application_id                 AND t.type != 9                 LIMIT 1         ) AS recommended,         IF (status=6, 1,           (             SELECT count(*)             FROM versions v, files f             WHERE f.version_id=v.id AND addon_id=t.addon_id AND f.status>0          )         ) AS num_files,         (            SELECT count(DISTINCT av.application_id)             FROM addons a, versions v, applications_versions av             WHERE a.id=t.addon_id AND v.addon_id=a.id AND av.version_id=v.id         ) AS num_apps,         (             SELECT GROUP_CONCAT(version)             FROM versions WHERE addon_id = t.addon_id         ) AS addon_versions,         (             SELECT GROUP_CONCAT(nickname)             FROM addons_users au, users u             WHERE addon_id=t.addon_id AND au.user_id = u.id AND listed = 1         ) AS authors,         (              SELECT GROUP_CONCAT(tag_text)              FROM users_tags_addons, tags              WHERE tags.id = tag_id              AND addon_id = t.addon_id         ) AS tags,         (             SELECT IF(type=4,9999999999999, max(version_int))             FROM versions s_v, applications_versions s_av, appversions max             WHERE s_v.addon_id = t.addon_id                 AND s_av.application_id = t.app                 AND s_av.version_id = s_v.id AND s_av.max = max.id         ) AS max_ver,         (             SELECT IF(type=4,0, min(version_int))             FROM versions s_v, applications_versions s_av, appversions min             WHERE s_v.addon_id = t.addon_id                 AND s_av.application_id = t.app                 AND s_av.version_id = s_v.id                 AND s_av.min = min.id         ) AS min_ver,         created,         modified     FROM         (             SELECT DISTINCT                 (name.autoid*100+IFNULL(av.application_id,0)) AS id,                 IF(a.addontype_id IN (4,9), 99, av.application_id) AS app,                 a.id AS addon_id,                 a.addontype_id AS type,                 a.status,                 CRC32(a.guid) AS guid_ord,                 name.locale,                 CRC32(name.locale) AS locale_ord,                 a.bayesianrating AS averagerating,                 a.weeklydownloads,                 a.totaldownloads,                 a.inactive,                 LTRIM(name.localized_string) AS name,                 homepage.localized_string AS homepage,                 description.localized_string AS description,                 summary.localized_string AS summary,                 developercomments.localized_string AS developercomments,                 UNIX_TIMESTAMP(a.created) AS created,                 UNIX_TIMESTAMP(a.last_updated) AS modified             FROM                 (                     translations name,                     addons a                     LEFT JOIN versions v ON v.addon_id = a.id                     LEFT JOIN applications_versions av ON av.version_id = v.id                 )             LEFT JOIN translations homepage                 ON a.homepage = homepage.id                 AND homepage.locale = name.locale             LEFT JOIN translations description                 ON a.description = description.id                 AND description.locale = name.locale             LEFT JOIN translations summary                 ON a.summary = summary.id                 AND summary.locale = name.locale             LEFT JOIN translations developercomments                 ON a.developercomments = developercomments.id                 AND developercomments.locale = name.locale             WHERE                 a.name = name.id                 AND name.localized_string IS NOT NULL                 AND                   (                     description.localized_string IS NOT NULL                     OR summary.localized_string IS NOT NULL                     OR defaultlocale = name.locale                   )         ) t;


7 years ago
Target Milestone: 5.12.7 → Q1 2011


7 years ago
Whiteboard: [waiting on elastic search]
Target Milestone: Q1 2011 → 4.x (triaged)

Comment 1

7 years ago
Assignee: dd → jbalogh
Last Resolved: 7 years ago
Resolution: --- → FIXED
Product: addons.mozilla.org → addons.mozilla.org Graveyard
You need to log in before you can comment on or make changes to this bug.