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

Opened 11 years ago

#3201 new enhancement

2.10.12での負荷低減

Reported by: imamura623 Owned by: nobody
Priority: minor Milestone:
Component: 指定しない Version:
Keywords: 2.13要望 Cc:

Description

http://sns.openpne.jp/?m=pc&a=page_fh_diary&target_c_diary_id=19755より転記

webapp/lib/db/diary.php の 589行目
p_h_diary_comment_list_c_diary_my_comment_list4c_member_id 関数の書き込み件数をカウントする処理を別SQLにしてください。
日記コメントが100万超えると動きません。。

function p_h_diary_comment_list_c_diary_my_comment_list4c_member_id($c_member_id, $page, $page_size)
{
$blocked = db_member_access_block_list4c_member_id_to($c_member_id);
$blocked[] = $c_member_id;
$except_ids = implode(',', $blocked);

$friends = db_friend_c_member_id_list($c_member_id);
$friend_ids = implode(',', $friends);

if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$sql = 'SELECT d.c_diary_id' .
', d.subject' .
', d.c_member_id' .
', sub_diary_tbl.r_datetime' .
', sub_diary_tbl.num_comment' .
' FROM c_diary AS d' .
', ( SELECT dc.c_diary_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\'';
if ($friend_ids) {
$sql .= ' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . '))';
}
$sql .= ')' .
' GROUP BY dc.c_diary_id' .
') as sub_diary_tbl' .
' WHERE' .
' sub_diary_tbl.c_diary_id = d.c_diary_id' .
' ORDER BY r_datetime DESC';
} else {
$sql = 'SELECT d.c_diary_id' .
', d.subject' .
', d.c_member_id' .
', MAX(dc.r_datetime) AS r_datetime' .
' FROM c_diary_comment AS mydc' .
' LEFT JOIN c_diary AS d USING (c_diary_id)' .
' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' .
' WHERE mydc.c_member_id = ?' .
' AND dc.r_datetime >= mydc.r_datetime' .
' AND d.c_member_id NOT IN (' . $except_ids . ')' .
' AND (d.public_flag = \'public\'';
if ($friend_ids) {
$sql .= ' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . '))';
}
$sql .= ')' .
' 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);

foreach ($list as $key => $value) {
$list[$key] += db_common_c_member4c_member_id_LIGHT($value['c_member_id']);
$list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
}

$sql = 'SELECT COUNT(DISTINCT d.c_diary_id)' .
' FROM c_diary_comment AS mydc' .
' LEFT JOIN c_diary AS d USING (c_diary_id)' .
' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' .
' WHERE mydc.c_member_id = ?' .
' AND dc.r_datetime >= mydc.r_datetime' .
' AND d.c_member_id NOT IN (' . $except_ids . ')' .
' AND (d.public_flag = \'public\'';
if ($friend_ids) {
$sql .= ' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . '))';
}
$sql .= ')';

$total_num = db_get_one($sql, $params);

$is_prev = false;
$is_next = false;
if ($total_num) {
$is_prev = (bool)($page > 1);
$is_next = (bool)($page < ceil($total_num / $page_size));
}
return array($list, $is_prev, $is_next, $total_num);
}

こんな感じでお願いします。

Change History (0)

Note: See TracTickets for help on using tickets.