OpenPNE2.6.5(PNEManager環境) SLOWLOG SELECT d.c_diary_id, d.subjectが重い

[tejimaa]

# Query_time: 8  Lock_time: 0  Rows_sent: 10  Rows_examined: 58054
use ppne_jp;
SELECT d.c_diary_id, d.subject, d.c_member_id, MAX(dc.r_datetime) AS r_datetime, COUNT(DIS
TINCT dc.c_diary_comment_id) AS num_comment FROM c_diary AS d INNER JOIN c_diary_comment A
S dc USING (c_diary_id), c_diary_comment AS mydc WHERE mydc.c_member_id = 71 AND mydc.c_di
ary_id = d.c_diary_id AND mydc.c_member_id <> d.c_member_id AND d.c_member_id NOT IN (71)
AND (d.public_flag = 'public' OR (d.public_flag = 'friend' AND d.c_member_id IN (12,32,117
,195,213,42,221,224,238,239,101,3,9,272,40,295,298,304,312,167,81,324,82,99,70,2,254,22,75
,207,151,10,328,77,8,35,379,103,405))) GROUP BY dc.c_diary_id ORDER BY r_datetime DESC LIM
IT 0, 10;

[kunitada]

チューニング検討

[ebihara]

見てみます。

[ebihara]

p_h_diary_comment_list_c_diary_my_comment_list4c_member_id()ですね。

r4006 時点での trunk のコードで、620行目からはじまる else ブロックに当該SQL文があります。

    } else {
        $sql = 'SELECT d.c_diary_id' .
                ', d.subject' .
                ', d.c_member_id' .
                ', MAX(dc.r_datetime) AS r_datetime' .
                ', COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment' .
            ' FROM c_diary AS d' .
                ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' .
                ', c_diary_comment AS mydc' .
            ' WHERE mydc.c_member_id = ?' .
                ' AND mydc.c_diary_id = d.c_diary_id' .
                ' AND mydc.c_member_id <> d.c_member_id' .
                ' AND d.c_member_id NOT IN (' . $except_ids . ')' .
                ' AND (d.public_flag = \'public\' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . ')))' .
            ' GROUP BY dc.c_diary_id' .
            ' ORDER BY r_datetime DESC';
    }

    $params = array(intval($c_member_id));
    $list = db_get_all_page($sql, $page, $page_size, $params);

[ebihara]

p_h_diary_comment_list_c_diary_my_comment_list4c_member_id() は ?m=pc&a=page_h_diary_comment_list, ?m=ktai&a=page_h_diary_comment_listの2アクションでコールされています。

[ebihara]

重いことには違いないのですが、あまり使われていないページということで、下げます。

[ebihara]

手放します。

[ogawa]

#1151 に統合。