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

Opened 12 years ago

Closed 12 years ago

Last modified 12 years ago

#1003 closed enhancement (fixed)

トピック検索の速度を改善する

Reported by: kunitada Owned by: ebihara
Priority: major Milestone: OpenPNE2.9.2
Component: pne-sysadmin Version: 2.10.x
Keywords: hidden Cc:

Description (last modified by ogawa)

トピック検索の検索速度が遅いので早くする。

■現在
検索語にマッチしたc_topic_commentの含まれるc_topicの最新のc_topic_commentの最新日時で降順ソートしている。

■修正版
検索語にマッチしたc_topic_commentの最新日時で降順ソートする。

■参考
http://trac.openpne.jp/ticket/974


Change History (8)

comment:1 Changed 12 years ago by kunitada

Description: modified (diff)

comment:2 Changed 12 years ago by ebihara

Owner: changed from nobody to ebihara
Status: newassigned

まず現象確認、コード面での確認をやります。

comment:3 Changed 12 years ago by ebihara

r3575

対応しました。

comment:4 Changed 12 years ago by ebihara

r3575のコミットでそれなりに改善されたはずですが、まだ気がかりです。

mysql> EXPLAIN SELECT c.name AS commu_name, c.image_filename AS commu_image, ct.*, ctc.r_datetime FROM c_commu AS c, c_commu_topic AS ct, c_commu_topic_comment AS ctc WHERE ct.c_commu_topic_id = ctc.c_commu_topic_id AND c.c_commu_id = ct.c_commu_id AND c.public_flag IN ('public', 'auth_sns') GROUP BY ct.c_commu_topic_id ORDER BY ctc.r_datetime DESC;

+----+-------------+-------+--------+--------------------+---------+---------+----------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys      | key     | key_len | ref                        | rows | Extra                           |
+----+-------------+-------+--------+--------------------+---------+---------+----------------------------+------+---------------------------------+
|  1 | SIMPLE      | c     | system | PRIMARY,c_commu_id | NULL    | NULL    | NULL                       |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | ctc   | ALL    | c_commu_topic_id   | NULL    | NULL    | NULL                       |   82 |                                 |
|  1 | SIMPLE      | ct    | eq_ref | PRIMARY,c_commu_id | PRIMARY | 4       | trunk.ctc.c_commu_topic_id |    1 | Using where                     |
+----+-------------+-------+--------+--------------------+---------+---------+----------------------------+------+---------------------------------+
3 rows in set (0.00 sec)

c_commuのUsing temporary; Using filesortがかなりくせ者で、どうインデックスを貼っても消えてくれません。

comment:5 Changed 12 years ago by ebihara

r3588でtrunkにコミットしました

comment:6 Changed 12 years ago by ogawa

Description: modified (diff)
Resolution: fixed
Status: assignedclosed

comment:7 Changed 12 years ago by tejimaa

Component: core【OpenPNEコア部分】pne-sysadmin

comment:8 Changed 12 years ago by ogawa

Keywords: hidden added
Note: See TracTickets for help on using tickets.