ここの情報は古いです。ご理解頂いた上でお取り扱いください。

Opened 13 years ago

Closed 12 years ago

#562 closed enhancement (duplicate)

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

Reported by: tejimaa Owned by: nobody
Priority: minor Milestone: OpenPNE2.10beta1
Component: pne-sysadmin Version:
Keywords: Cc:

Description

# 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;

Change History (15)

comment:1 Changed 13 years ago by tejimaa

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

comment:2 Changed 12 years ago by tejimaa

Keywords: pne-sysadmin added

comment:3 Changed 12 years ago by tejimaa

Component: 100,000,000PV【1億PVプロジェクト】pne-sysadmin
Keywords: pne-sysadmin removed

comment:4 Changed 12 years ago by kunitada

Milestone: OpenPNE2.10

チューニング検討

comment:5 Changed 12 years ago by kunitada

Priority: minorcritical

comment:6 Changed 12 years ago by kunitada

Milestone: OpenPNE2.10OpenPNE2.10beta2
Priority: criticalblocker

comment:7 Changed 12 years ago by kunitada

Milestone: OpenPNE2.10beta2OpenPNE2.10beta1

comment:8 Changed 12 years ago by ebihara

Owner: changed from nobody to ebihara
Status: newassigned

見てみます。

comment:9 Changed 12 years ago by 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);

comment:10 Changed 12 years ago by 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アクションでコールされています。

comment:11 Changed 12 years ago by kunitada

Priority: blockercritical

comment:12 Changed 12 years ago by kunitada

Priority: criticalmajor

comment:13 Changed 12 years ago by ebihara

Priority: majorminor

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

comment:14 Changed 12 years ago by ebihara

Owner: changed from ebihara to nobody
Status: assignednew

手放します。

comment:15 Changed 12 years ago by ogawa

Resolution: duplicate
Status: newclosed

#1151 に統合。

Note: See TracTickets for help on using tickets.