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

Opened 13 years ago

Closed 13 years ago

Last modified 12 years ago

#2694 closed defect (fixed)

p_h_home_c_diary_friend_list4c_member_idのindex指定間違い

Reported by: toraneko Owned by: ebihara
Priority: major Milestone: OpenPNE2.12.1
Component: 指定しない Version: 2.12.x & 2.14.x
Keywords: OpenPNE2.13.2 Cc:

Description (last modified by kudo)

■現象

プロファイリングで速度測定したところ 提供したソースに間違いがあり、2.10より性能悪化させている部分が見つかりました。

http://sns.openpne.jp/?m=pc&a=page_fh_diary&target_c_diary_id=17483&comment_count=2

p_h_diary_list_friend_h_diary_list_friend4c_member_id と p_h_home_c_diary_friend_list4c_member_id の

$hint = db_mysql_hint('USE INDEX (c_member_id_r_datetime_public_flag)');

$hint = db_mysql_hint('USE INDEX (r_datetime_c_member_id, r_datetime)');

に戻してください。提供したパッチが間違っていました。

■仕様

■関連情報

Change History (9)

comment:1 Changed 13 years ago by ebihara

Keywords: OpenPNE2.13.2 added
Milestone: OpenPNE2.12.1

検証および対応コードの提示、ありがとうございます。

是非対応させていただきたいと思います。

comment:2 Changed 13 years ago by ebihara

Priority: criticalmajor

comment:3 Changed 13 years ago by ebihara

Version: 2.12.x & 2.13.x

comment:4 Changed 13 years ago by ebihara

Owner: changed from nobody to ebihara
Status: newassigned

comment:5 Changed 13 years ago by ebihara

EXPLAIN で インデックスの使用状況をみてみたところ、以下のようになりました。

OpenPNE2.12.0 の SQL

+----+-------------+---------+-------+------------------------------------+------------------------------------+---------+------+------+-----------------------------+
| id | select_type | table   | type  | possible_keys                      | key                                | key_len | ref  | rows | Extra                       |
+----+-------------+---------+-------+------------------------------------+------------------------------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | c_diary | range | c_member_id_r_datetime_public_flag | c_member_id_r_datetime_public_flag | 4       | NULL | 2751 | Using where; Using filesort | 
+----+-------------+---------+-------+------------------------------------+------------------------------------+---------+------+------+-----------------------------+

toraneko さんの指摘通り、インデックスの指定を元に戻した場合

+----+-------------+---------+-------+---------------+------------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key                    | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | c_diary | index | NULL          | r_datetime_c_member_id | 12      | NULL | 3636 | Using where | 
+----+-------------+---------+-------+---------------+------------------------+---------+------+------+-------------+

HINT を使用しない場合

+----+-------------+---------+-------+------------------------------------------------------------------------------------------+------------------------+---------+------+------+-----------------------------+
| id | select_type | table   | type  | possible_keys                                                                            | key                    | key_len | ref  | rows | Extra                       |
+----+-------------+---------+-------+------------------------------------------------------------------------------------------+------------------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | c_diary | range | c_member_id,c_member_id_r_date,c_member_id_r_datetime,c_member_id_r_datetime_public_flag | c_member_id_r_datetime | 4       | NULL | 2619 | Using where; Using filesort | 
+----+-------------+---------+-------+------------------------------------------------------------------------------------------+------------------------+---------+------+------+-----------------------------+

これにより、元に戻す形が最善といえそうです。

comment:6 Changed 13 years ago by ebihara

Keywords: 確認中 added

以下のリビジョンで対応しました。

comment:7 Changed 13 years ago by ogawa

Keywords: テスト待ち added; 確認中 removed

comment:8 Changed 13 years ago by kiwa

Keywords: テスト待ち removed
Resolution: fixed
Status: assignedclosed

comment:9 Changed 12 years ago by kudo

Description: modified (diff)
Note: See TracTickets for help on using tickets.