Joomler!.net - Decided on Joomla!

Wiki or Wordpress? No it is JContentPlus.

 

Archives

Blog

Demo

Home » Blog » MySQLのSLOWQUERYを眺めていて・・・
Apr
6
2013

MySQLのSLOWQUERYを眺めていて・・・

EMailPrintPDF
0 votes
Written by:Joomler! 9217 hits

滅多に無いが、たまたまJoomla2.5のslowログがあった。メインサイト(私のサイトでは無い)では無いので少ないのは当然だが、そのクエリがやけに長いものだったので気になった。以下のようなクエリ

SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END AS modified, a.modified_by, uam.name AS modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END AS publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id IS NOT NULL THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id AS contactid,parent.title AS parent_title, parent.id AS parent_id, parent.path AS parent_route, parent.alias AS parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count AS rating_count,c.published, CASE WHEN badcats.id IS NULL THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
LEFT JOIN jos_users AS uam ON uam.id = a.modified_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM jos_contact_details AS contact
WHERE contact.published = 1
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN jos_categories AS parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id AS id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
INNER JOIN jos_content_frontpage AS fp ON fp.content_id = a.id
WHERE a.access IN (1,1) AND c.access IN (1,1) AND CASE WHEN badcats.id IS NULL THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2013-04-05 14:51:16') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2013-04-05 14:51:16')
GROUP BY a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id, c.title, c.path, c.access, c.alias, uam.id, ua.name, ua.email, contact.id, parent.title, parent.id, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id, a.images, a.urls
ORDER BY   CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END  DESC ,  a.created DESC LIMIT 0, 4;

整形せず、ログのままだがこれでは見づらい。最近は、複雑なクエリを書くことが多く、いつも使っているエディタではなかなか整形してくれなくて困っていた。検索してみると良さそうなJavascriptで作られた整形アプリがあったのでサイト上にアップしてみた。(設置している間は、ブックマークして使ってもらってかまわない。)

http://www.joomler.net/dmlbreaker/

上記の長いクエリも綺麗に整形してくれる。若干?なところもあるが、とにかく見やすく且つ編集しやすくなれば良いのでこれで充分というかこれ以上は必要性を感じないレベル。以下が整形後。

SELECT
    a.id,
    a.title,
    a.alias,
    a.title_alias,
    a.introtext,
    a.checked_out,
    a.checked_out_time,
    a.catid,
    a.created,
    a.created_by,
    a.created_by_alias,
    CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END AS modified,
    a.modified_by,
    uam.name AS modified_by_name,
    CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END AS publish_up,
    a.publish_down,
    a.images,
    a.urls,
    a.attribs,
    a.metadata,
    a.metakey,
    a.metadesc,
    a.access,
    a.hits,
    a.xreference,
    a.featured,
    LENGTH(a.fulltext) AS readmore,
    CASE WHEN badcats.id IS NOT NULL THEN 0 ELSE a.state END AS state,
    c.title AS category_title,
    c.path AS category_route,
    c.access AS category_access,
    c.alias AS category_alias,
    CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,
    ua.email AS author_email,
    contact.id AS contactid,
    parent.title AS parent_title,
    parent.id AS parent_id,
    parent.path AS parent_route,
    parent.alias AS parent_alias,
    ROUND(v.rating_sum / v.rating_count, 0) AS rating,
    v.rating_count AS rating_count,
    c.published,
    CASE WHEN badcats.id IS NULL THEN c.published ELSE 0 END AS parents_published
FROM
    jos_content AS a
LEFT JOIN
    jos_categories AS c
ON
        c.id = a.catid
LEFT JOIN
    jos_users AS ua
ON
        ua.id = a.created_by
LEFT JOIN
    jos_users AS uam
ON
        uam.id = a.modified_by
LEFT JOIN
    (
        SELECT
            contact.user_id,
            MAX(contact.id) AS id,
            contact.language
        FROM
            jos_contact_details AS contact
        WHERE
                contact.published = 1
        GROUP BY
            contact.user_id,
            contact.language
    ) AS contact
ON
        contact.user_id = a.created_by
LEFT JOIN
    jos_categories AS parent
ON
        parent.id = c.parent_id
LEFT JOIN
    jos_content_rating AS v
ON
        a.id = v.content_id
LEFT OUTER JOIN
    (
        SELECT
            cat.id AS id
        FROM
            jos_categories AS cat JOIN jos_categories AS parent
        ON
                cat.lft BETWEEN parent.lft AND parent.rgt
        WHERE
                parent.extension = 'com_content'
            AND parent.published != 1
        GROUP BY
            cat.id
    ) AS badcats
ON
        badcats.id = c.id
INNER JOIN
    jos_content_frontpage AS fp
ON
        fp.content_id = a.id
WHERE
        a.access IN (1,1)
    AND c.access IN (1,1)
    AND CASE WHEN badcats.id IS NULL THEN a.state ELSE 0 END = 1
    AND (a.publish_up = '0000-00-00 00:00:00'
    OR  a.publish_up <= '2013-04-05 14:51:16')
    AND (a.publish_down = '0000-00-00 00:00:00'
    OR  a.publish_down >= '2013-04-05 14:51:16')
GROUP BY
    a.id,
    a.title,
    a.alias,
    a.title_alias,
    a.introtext,
    a.checked_out,
    a.checked_out_time,
    a.catid,
    a.created,
    a.created_by,
    a.created_by_alias,
    a.created,
    a.modified,
    a.modified_by,
    uam.name,
    a.publish_up,
    a.attribs,
    a.metadata,
    a.metakey,
    a.metadesc,
    a.access,
    a.hits,
    a.xreference,
    a.featured,
    a.fulltext,
    a.state,
    a.publish_down,
    badcats.id,
    c.title,
    c.path,
    c.access,
    c.alias,
    uam.id,
    ua.name,
    ua.email,
    contact.id,
    parent.title,
    parent.id,
    parent.path,
    parent.alias,
    v.rating_sum,
    v.rating_count,
    c.published,
    c.lft,
    a.ordering,
    parent.lft,
    fp.ordering,
    c.id,
    a.images,
    a.urls
ORDER BY
    CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC ,
    a.created DESC LIMIT 0,
    4;

さて、このSlow Queryを見ると、まず一番気になったのがGROUP BYの項目が多いことだ。こんなに必要なのか・・・?改善する必要がある気がする。EXPLAINしてみると、3回 Using temporary; Using filesortが見られた。遅い原因とされるものが3度あればそりゃ遅くなって当然かもしれない。

最近までローカルでクエリを実行して早いものが一番良いクエリと思っていました。ところが、それがUsing temporaryを使用するものであれば、どれだけ早くてもボトルネックとなり得るということがわかりました。Using temporaryをUsing filesortのみを使用するクエリに書き換えるとローカル実行環境では倍以上遅いのに、稼働環境ではSlow queryにならない。おそらくMySQLの設定をもっと最適化すれば良いのかもしれないが、何しろローカル環境で発生しないものなので・・・どうしたものかといったところでこの対応となった。

誰かMySQLの設定に詳しい方教えてよ。

 

ADD A COMMENT

Subject*
:
Color Swatch




Email Link Page Code Text Align Center Text Align Justify Text Align Left Text Align Right Text Bold Text Quote Text Italic Text List Bullets Text List Numbers Text Strikethrough Text Underline World Link
Sun Unhappy Smile Evilgrin Wink Tongue Lightning Surprised Grin Rain Clouds Cloudy Snow Waii
Name*
:
EMail*
:
Please answer....
Submit Preview

JContentPlus for Joomla!1.5 powered by Joomler!.net

Related Items

joomler.net is not affiliated with or endorsed by the Joomla! Project or Open Source Matters.
The Joomla!(R) name is used under a limited license from Open Source Matters in the United States and other countries.
joomler.net is not affiliated with or endorsed by Open Source Matters or the Joomla! Project.