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

Changeset 3630


Ignore:
Timestamp:
Aug 6, 2007, 2:32:52 PM (12 years ago)
Author:
ogawa
Message:

#747:DBの日記カテゴリ関連テーブルのインデックス作成
r3612 のSQL修正

Location:
OpenPNE/trunk/setup/sql
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • OpenPNE/trunk/setup/sql/mysql40/update/update02-for2.9.2-diary_number.sql

    r3608 r3630  
    77) TYPE=MyISAM;
    88
    9 INSERT INTO tmp_c_diary_comment
    10 (c_diary_comment_id,
    11 number)
    12 (SELECT
    13 dc1.c_diary_comment_id,
    14 count(*) AS number
    15  FROM c_diary_comment AS dc1,c_diary_comment AS dc2
    16 WHERE dc1.c_diary_id =dc2.c_diary_id
    17  AND dc1.c_diary_comment_id >= dc2.c_diary_comment_id
    18 GROUP BY dc1.c_diary_comment_id
    19 );
     9INSERT INTO tmp_c_diary_comment (c_diary_comment_id, number)
     10  (SELECT dc1.c_diary_comment_id, COUNT(*)
     11     FROM c_diary_comment AS dc1, c_diary_comment AS dc2
     12     WHERE dc1.c_diary_id = dc2.c_diary_id
     13       AND dc1.c_diary_comment_id >= dc2.c_diary_comment_id
     14     GROUP BY dc1.c_diary_comment_id);
    2015
    21 UPDATE c_diary_comment AS dc1,tmp_c_diary_comment AS dc2
    22  SET dc1.number=dc2.number
    23  WHERE dc1.c_diary_comment_id = dc2.c_diary_comment_id;
     16UPDATE c_diary_comment INNER JOIN tmp_c_diary_comment USING (c_diary_comment_id)
     17 SET c_diary_comment.number = tmp_c_diary_comment.number;
    2418
    2519DROP TABLE tmp_c_diary_comment;
  • OpenPNE/trunk/setup/sql/mysql40/update/update03-for2.9.2-diary_category_index.sql

    r3612 r3630  
    11ALTER TABLE c_diary_category ADD INDEX c_member_id_c_diary_category_id(c_member_id, c_diary_category_id);
    2 ALTER TABLE c_diary_category ADD INDEX category_name_c_member_id(category_name(20),c_member_id);
     2ALTER TABLE c_diary_category ADD INDEX category_name_c_member_id(category_name(20), c_member_id);
    33ALTER TABLE c_diary_category_diary ADD INDEX c_diary_category_id(c_diary_category_id);
    44ALTER TABLE c_diary_category_diary ADD INDEX c_diary_id(c_diary_id);
  • OpenPNE/trunk/setup/sql/mysql41/update/update02-for2.9.2-diary_number.sql

    r3608 r3630  
     1/*!40101 SET NAMES utf8 */;
     2
    13ALTER TABLE `c_diary_comment` ADD COLUMN `number` int(11) NOT NULL default '0';
     4
    25
    36CREATE TABLE `tmp_c_diary_comment` (
     
    710) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    811
    9 INSERT INTO tmp_c_diary_comment
    10 (c_diary_comment_id,
    11 number)
    12 (SELECT
    13 dc1.c_diary_comment_id,
    14 count(*) AS number
    15  FROM c_diary_comment AS dc1,c_diary_comment AS dc2
    16 WHERE dc1.c_diary_id =dc2.c_diary_id
    17  AND dc1.c_diary_comment_id >= dc2.c_diary_comment_id
    18 GROUP BY dc1.c_diary_comment_id
    19 );
     12INSERT INTO tmp_c_diary_comment (c_diary_comment_id, number)
     13  (SELECT dc1.c_diary_comment_id, COUNT(*)
     14     FROM c_diary_comment AS dc1, c_diary_comment AS dc2
     15     WHERE dc1.c_diary_id = dc2.c_diary_id
     16       AND dc1.c_diary_comment_id >= dc2.c_diary_comment_id
     17     GROUP BY dc1.c_diary_comment_id);
    2018
    21 UPDATE c_diary_comment AS dc1,tmp_c_diary_comment AS dc2
    22  SET dc1.number=dc2.number
    23  WHERE dc1.c_diary_comment_id = dc2.c_diary_comment_id;
    24 
     19UPDATE c_diary_comment INNER JOIN tmp_c_diary_comment USING (c_diary_comment_id)
     20 SET c_diary_comment.number = tmp_c_diary_comment.number;
     21 
    2522DROP TABLE tmp_c_diary_comment;
  • OpenPNE/trunk/setup/sql/mysql41/update/update03-for2.9.2-diary_category_index.sql

    r3612 r3630  
     1/*!40101 SET NAMES utf8 */;
     2
    13ALTER TABLE c_diary_category ADD INDEX c_member_id_c_diary_category_id(c_member_id, c_diary_category_id);
    2 ALTER TABLE c_diary_category ADD INDEX category_name_c_member_id(category_name(20),c_member_id);
     4ALTER TABLE c_diary_category ADD INDEX category_name_c_member_id(category_name(20), c_member_id);
    35ALTER TABLE c_diary_category_diary ADD INDEX c_diary_category_id(c_diary_category_id);
    46ALTER TABLE c_diary_category_diary ADD INDEX c_diary_id(c_diary_id);
Note: See TracChangeset for help on using the changeset viewer.