トップ  > メモ一覧  > カテゴリ「SQL文」の絞り込み結果 : 58件

58件中 1 〜 10 表示  1 | 2 | 3 | 4 ... 次の10件> 最後»

No.5232 N日前を条件指定

90日前例
where r_datetime > DATE_ADD(NOW(), INTERVAL - 90 DAY)
更新:2015/02/13 11:42 カテゴリ: MySQL  > SQL文 ▲トップ

No.4320 古いデータの削除

古いデータの削除

DELETE FROM activity_data_cache
  WHERE created_at < ADDDATE(NOW(), -14);

更新:2012/02/28 15:17 カテゴリ: MySQL  > SQL文 ▲トップ

No.4179 SQLの結果をCSV で出力

SQLの結果をCSV で出力

↓をSQLの末尾に追加
INTO OUTFILE '/tmp/hoge.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

※既にファイルが存在していたらエラーになるよ

select *
from message_send_list A
order by A.created_at
INTO OUTFILE '/tmp/hoge.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

更新:2011/11/19 17:43 カテゴリ: MySQL  > SQL文 ▲トップ

No.3907【引用】■[その他]SQLのLIKE演算子のエスケープ 22:27


■ [ その他 ]SQLのLIKE演算子のエスケープ 22:27
例えば、「\%foo」から始まる文字列を検索する場合には、どのようなSQL文を書けばよいのでしょうか。
条件は以下の通りです。

DBMSソフトはMySQL
ESCAPE節は使わない

MySQLでESCAPE節を使わない場合、ワイルドカード文字(「%」や「_」)は「\」でエスケープすることになります。
間違った答え
直感的に以下のようなSQL文を書いてしまう人もいると思います。
SELECT * FROM table1 WHERE hoge LIKE '\\\%foo%'...

引用元

更新:2011/07/15 13:24 カテゴリ: MySQL  > SQL文 ▲トップ

No.3528 PRIMARY以外のindexをスキーマ情報から抽出

PRIMARY以外のindexをスキーマ情報から抽出
$ mysql -u root information_schema

select TABLE_NAME,INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX from STATISTICS where TABLE_SCHEMA='DB名' and INDEX_NAME !='PRIMARY';

更新:2011/03/27 16:08 カテゴリ: MySQL  > SQL文 ▲トップ

No.3493 mysqlの外部キー制約の削除

mysql の外部キー制約の削除

「DROP CONSTRAINT」とか出来ないらしい@@
----------
【誤】
ALTER TABLE ashiato DROP CONSTRAINT ashiato_member_id_to_member_id;
----------

----------
【正】
ALTER TABLE ashiato DROP FOREIGN KEY ashiato_member_id_to_member_id;
----------
更新:2011/03/07 14:10 カテゴリ: MySQL  > SQL文 ▲トップ

No.2327 緯度・経度より距離の近い順に取り出す

↓新宿御苑から1km以内にある駅

mysql> SELECT id, address, name,
    ->  ( 6371 * acos( cos( radians(35.688588) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(139.71069) )
    ->   + sin( radians(35.688588) ) * sin( radians( lat ) ) ) ) AS distance
    -> FROM gps HAVING distance < 1
    -> ORDER BY distance LIMIT 0 , 20;
+------------------------+-----------------+---------------------+
| address                | name            | distance            |
+------------------------+-----------------+---------------------+
| 新宿1~2丁目  | 新宿御苑前 | 0.00031486476271382 |
| 新宿東口           | 新宿三丁目 |    0.45863453707973 |
| 新宿東口           | 新宿三丁目 |    0.58043841805294 |
| 新宿東口           | 新宿三丁目 |    0.58577754533609 |
| 千駄ヶ谷/信濃町 | 千駄ケ谷    |    0.82238207007257 |
| 千駄ヶ谷/信濃町 | 千駄ケ谷    |    0.82238207007257 |
| 四谷                 | 四谷三丁目 |    0.85345135910327 |
| 新宿東口           | 新宿          |    0.93165043871648 |
| 新宿東口           | 新宿          |    0.93165043871648 |
| 新宿東口           | 新宿          |    0.93165043871648 |
| 新宿東口           | 新宿          |    0.93165043871648 |
| 新宿東口           | 新宿          |    0.93165043871648 |
| 新宿東口           | 新宿          |    0.93165043871648 |
| 新宿東口           | 新宿          |    0.93165043871648 |
| 新宿東口           | 新宿          |     0.9932342176026 |
| 新宿南口/代々木 | 代々木       |    0.99396752017771 |
| 新宿南口/代々木 | 代々木       |    0.99396752017771 |
| 新宿南口/代々木 | 代々木       |    0.99396752017771 |
+------------------------+-----------------+---------------------+
18 rows in set (0.00 sec)

新宿より千駄ヶ谷の方が近かったんかい!@@

引用元

更新:2010/04/23 15:39 カテゴリ: MySQL  > SQL文 ▲トップ

No.2202 INSERT...SELECT構文

INSERT ... SELECT 構文

ランダムに突っ込んで、並べ替えーの、ID振り直し
insert into tmp_diary (diary_id) select id from diary order by created_at;

※tmp_diaryにはdiary_idの最大値以上から始めるべし




6.4.3.1. INSERT ... SELECT 構文
INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...

INSERT ... SELECT ステートメントでは、1 つまたは複数のテーブルの数多くのレコードを別の 1 つのテーブルにすばやく挿入することができます。

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

INSERT ... SELECT ステートメントでは、以下の条件が適用されます。

  • MySQL 4.0.1 より前のバージョンでは、INSERT ... SELECT は暗黙的に IGNORE モードで動作する。MySQL 4.0.1 以降では、重複キー違反を引き起こすレコードを無視するには、IGNORE を明示的に指定しなければならない。

  • MySQL 4.0.14 より前のバージョンでは、INSERT ステートメントの対象テーブルをクエリの SELECT 部分の FROM 節に示すことはできない。 この制約は 4.0.14 でなくなった。

  • AUTO_INCREMENT カラムは通常どおり機能する。

  • C プログラムでは、C API 関数 mysql_info() を使用してクエリに関する情報を取得できる。 See 項6.4.3. 「INSERT 構文」

  • バイナリログを使用して元のテーブルを確実に再作成できるようにするため、MySQL では、INSERT ... SELECT 実行中の同時挿入は行えない。

以前のレコードを上書きするには、INSERT の代わりに REPLACE を使用します。以前のレコードの値と重複するユニークキー値を持つ新しいレコードの処理に関して、REPLACEINSERT IGNORE に対立する働きをします。つまり、新しいレコードが廃棄されるのではなく、新しいレコードによって以前のレコードが置き換えられます。





引用元

更新:2010/03/17 19:33 カテゴリ: MySQL  > SQL文 ▲トップ

No.2173 nnoDBでCOUNT()を扱う際の注意事項あれこれ。

nnoDBでCOUNT()を扱う際の注意事項あれこれ。

InnoDBを使うとき、MyISAMと比較して度 々やり玉に挙げられるポイントとして「COUNT()が遅い」というものがある。確かにInnoDBにおいて行数を弾き出すのにはテーブルスキャンが必要 なのだが、そもそもMyISAMのCOUNT()が速い(テーブルの行数を保持してる)のが特殊なのであって、InnoDBが遅いわけではないのである。 とはいえ、高速なCOUNT()については需要が多く、この問題には多くの人取り組んでおられるようだ。しかしながら、COUNT()のチューニングにつ いては未だ語られていない点があるように見受けられるので、今日はCOUNT()のチューニングについて解説しようと思う。

COUNT(*)、COUNT(col)、COUNT(1)の違い

基本的なことではあるが、COUNT(*)とCOUNT(col)で は意味が異なるため、異なる結果が返される場合がある。COUNT(*)はフェッチした全ての行をカウントするが、COUNT(col)ではcolが NULLでない値の場合だけカウントされるという違いがある。colというように単一のカラムではなく、COUNT()の中身はもう少し複雑な式であって も良い。以下に、この違いがはっきり分かる簡単な例を示す。
  1. mysql> CREATE TABLE num_tbl (a INT) ENGINE InnoDB;  
  2. Query OK, 0 rows affected (0.44 sec)  
  3.   
  4. mysql> INSERT INTO num_tbl VALUES(0),(1),(2),(3),(NULL);  
  5. Query OK, 5 rows affected (0.00 sec)  
  6. Records: 5  Duplicates: 0  Warnings: 0  
  7.   
  8. mysql> SELECT COUNT(*) FROM num_tbl;  
  9. +----------+  
  10. COUNT(*) |  
  11. +----------+  
  12. |        5 |  
  13. +----------+  
  14. 1 row in set (0.00 sec)  
  15.   
  16. mysql> SELECT COUNT(a) FROM num_tbl;  
  17. +----------+  
  18. COUNT(a) |  
  19. +----------+  
  20. |        4 | <--- 値がNULLのカラムがカウントされない。  
  21. +----------+  
  22. 1 row in set (0.00 sec)  
  23.   
  24. mysql> SELECT COUNT(100/a) FROM num_tbl;  
  25. +--------------+  
  26. COUNT(100/a) |  
  27. +--------------+  
  28. |            3 | <--- ゼロ除算の結果はNULLなのでカウントされない。  
  29. +--------------+  
  30. 1 row in set (0.00 sec)  
このことはチューニングする上で意味があることなので覚えていて貰いたい。 ちなみに、COUNT(*)という表記は慣習的なものであり、実はアスタリスクを指定する意味はあまりない。COUNT(1)を指定しても同じ結果が得られるのである。

よく用いられるCOUNT()高速化対策法

COUNT()を高速化する方法として最もよく利用されるのが、トリガを使う方法であろう。 行数を保持するテーブルを作成して、COUNT()したいテーブルにトリガを設定し、INSERTされるごとに行数を+1、DELETEされるごとに-1 することで、別テーブルで行数をメンテしようというものである。これにより、COUNT(*)をする代わりに行数を保持しているテーブルから1行のレコー ドをフェッチするだけで済むため、行数を数える処理が格段に高速化するというわけだ。ただし、この手法では、既にKazuhooku氏がブログで紹介しているよ うに、INSERT時にオーバーヘッドが生じるという問題がある。また、わざわざ別テーブルとトリガをいちいち作成するのは面倒であり、運用の手間が増え てしまう。このようなデメリットがあるとはいえ、それでもCOUNT()を高速化したいんだよ!という場合には、非常に有効な対策である。 また、テーブルがキューとして使われている場合のように、主キーが整数型で途中に欠番がないような場合には、MIN()/MAX()を活用することで COUNT()と同様の結果を得られるという方法がある。こちらについてはKamipo氏がブログで紹介しているのでそちらを参照されたい。

MyISAMにおけるCOUNT()の限界

MyISAMを利用しているといつでもCOUNT()が高速であるか?というと、そういうわ けではない。実はMyISAMを利用している場合であっても、高速なのはSELECT COUNT(*) FROM tblというように、テーブル全体の行数を取得するようなクエリでないと速くないのである。例えば、COUNT(col)でNULL値を含む可能性のある カラムを指定すると、テーブルスキャンが必要になるため、COUNT()は別に速くもなんともない。 GROUP BYを利用した場合も同様で、MyISAMテーブルが保持している「テーブル全体の行数」は役に立たないため、スキャンが必要になる。以下は、MySQL公式サンプルであるWorldデータベースを用いた例である。テーブルスキャンが発生していることが分かる。
  1. mysql> EXPLAIN SELECT COUNT(*) FROM Country GROUP BY Continent;  
  2. +----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+  
  3. | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                           |  
  4. +----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+  
  5. |  1 | SIMPLE      | country | ALL  | NULL          | NULL | NULL    | NULL |  239 | Using temporary; Using filesort |  
  6. +----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+  
  7. 1 row in set (0.01 sec)  
WHERE句で範囲を絞って行数を数えたいような場合も、「テーブル全体の行数」は役に立たないため同様にスキャンが生じることになる。以上をまとめると、次のような場合には、MyISAMにおいても高速に行数を取得することは出来ないのである。
  • NULL値が含まれる可能性のあるカラムをCOUNT(col)する場合
  • GROUP BY句を利用する場合
  • WHERE句で行数をカウントする範囲を限定する場合

セカンダリインデックスを使って少しだけ高速化

さ て、ここからが今回のエントリの本題である。前置きが長いのはいつものことなので気にしないで頂きたい。 MyISAMと同様の制約は、InnoDBにおいて前述の「よく用いられるCOUNT()高速化対策法」で紹介したような方法を使っている場合にも当ては まる。特に、WHERE句やGROUP BY句などと絡めて行数を取得したい場合には、テーブルをスキャンする以外に道はない。ここで思い出して頂きたいのが、InnoDBテーブルの構造であ る。そう、InnoDBはクラスタインデックスを採用しているのである。クラスタインデックスとは、レコードが主キーの一部になっている形式のインデック スで、InnoDBの場合には「テーブルスキャン=主キーのスキャン」という図式が成り立つのだ! ではどうすればCOUNT(*)を高速化できるか?それにはセカンダリインデックスを活用するしかない。例えば次のようなテーブルがあるとしよう。
  1. CREATE TABLE t1 (  
  2.   a bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
  3.   b int(11) DEFAULT NULL,  
  4.   c tinyint(4) DEFAULT NULL,  
  5.   d date DEFAULT NULL,  
  6.   e varchar(200) DEFAULT NULL,  
  7.   f varchar(200) DEFAULT NULL,  
  8.   g varchar(200) DEFAULT NULL,  
  9.   h varchar(200) DEFAULT NULL,  
  10.   i varchar(200) DEFAULT NULL,  
  11.   PRIMARY KEY (a)  
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
このテーブルには主キーがひとつ設定されているだけであり、現時点ではセカンダリインデックスはない。このテーブルからCOUNT(*)すると、もれなく主キーのスキャンが発生する。以下は、1000万件のデータをつっこんでCOUNT(*)を実行したときの結果である。
  1. mysql> explain select count(*) from t1;  
  2. +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+  
  3. | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |  
  4. +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+  
  5. |  1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 8       | NULL | 10044347 | Using index |   
  6. +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+  
  7. 1 row in set (0.07 sec)  
  8.   
  9. mysql> select count(*) from t1;  
  10. +----------+  
  11. count(*) |  
  12. +----------+  
  13. | 10000000 |   
  14. +----------+  
  15. 1 row in set (1 min 3.30 sec)  
このように、手元のマシンではおよそ1分半かかっている。ちなみに、innodb_buffer_pool_size=1GBに設定してあ る。 このスキャンが何故遅いか?という理由は、このテーブルの全体的なデータサイズが大きいからだ。今回の例ではバッファプールに収まる行数が限られているこ とによる要因が大きい。このテーブル全体をスキャンするのに必要なI/O回数が多く、処理に時間を要してしまうのである。このような場合、スキャンを高速 化するテクニックとして有効なことのひとつが、セカンダリインデックスをつけることである。 そこで、b(INT型)、c(TINYINT型)、d(DATE型)、e(VARCHAR(200) CHARACTER SET utf8)のカラムにそれぞれインデックスをつけて同じクエリを実行してみよう。
  1. mysql> select count(*) from t1;  
  2. mysql> explain select count(*) from t1;  
  3. +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+  
  4. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |  
  5. +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+  
  6. |  1 | SIMPLE      | t1    | index | NULL          | c    | 2       | NULL | 10042706 | Using index |   
  7. +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+  
  8. 1 row in set (0.01 sec)  
  9.   
  10. +----------+  
  11. count(*) |  
  12. +----------+  
  13. | 10000000 |   
  14. +----------+  
  15. 1 row in set (2.82 sec)  
なんとMySQLは自動的に一番小さいサイズのカラムを選択したではないか!もちろん結果も劇的に変化している。1分半→3秒弱である。まさ に劇的ビフォーアフター! このように、COUNT(*)の場合は適切なインデックスがあればそれを勝手に選んでくれるのでユーザーがあまり気にすることはない。ここでひとつポイン トとして覚えて(思い出して)貰いたいことは、どのインデックスをスキャンしてもCOUNT()の結果自体は変わらないということである。NULL値に対 してもエントリが作成されるため、セカンダリインデックスには全ての行レコードに対するエントリが存在するのである。従って、NULL値を区別しなければ いけないCOUNT(c)のような場合でも、区別しないCOUNT(*)のような場合でも、セカンダリインデックスをスキャンするだけで正しい結果が得ら れることになり、オプティマイザは最も効率よくスキャンが出来るカラム=サイズの小さいカラムを選択するわけだ。ちなみに、COUNT(a)でもカラムc のインデックスが利用される。カラムaはNOT NULLが指定されているため、COUNT(a)とCOUNT(*)は同じ結果になり、どのインデックスを選んでも結果は変わらないからである。 各インデックスを使った場合の結果を比較してみよう。このテーブルのセカンダリインデックスのカラムは、NOT NULLが指定されていない、つまりNULL値を含む可能性があるため、COUNT(b)というようにそのカラムを指定すれば該当するセカンダリインデッ クスが利用される。以下はb(INT型)、d(DATE型)、e(VARCHAR(200) CHARACTER SET utf8)の比較である。
  1. mysql> select count(b) from t1;  
  2. +----------+  
  3. count(b) |  
  4. +----------+  
  5. | 10000000 |   
  6. +----------+  
  7. 1 row in set (3.03 sec)  
  8.   
  9. mysql> select count(d) from t1;  
  10. +----------+  
  11. count(d) |  
  12. +----------+  
  13. | 10000000 |   
  14. +----------+  
  15. 1 row in set (3.02 sec)  
  16.   
  17. mysql> select count(e) from t1;  
  18. +----------+  
  19. count(e) |  
  20. +----------+  
  21. | 10000000 |   
  22. +----------+  
  23. 1 row in set (14.08 sec)  
b、dはcとほとんど差はないが、セカンダリインデックスが全てバッファプールに収まっているからであろう。ちなみに、各インデックスがどの程度ページを消費しているかということは、InnoDBテーブルモニターで確認できる。以下はテーブルモニターの出力例である。
  1. TABLEname test/t1, id 0 263, columns 12, indexes 5, appr.rows 10179472  
  2.   COLUMNS: a: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 8; b: DATA_INT DATA_BINARY_TYPE len 4; c: DATA_INT DATA_BINARY_TYPE len 1; d: DATA_INT DATA_BINARY_TYPE len 3; e: type 12 len 600; f: type 12 len 600; g: type 12 len 600; h: type 12 len 600; i: type 12 len 600; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;   
  3.   INDEXname PRIMARY, id 0 456, fields 1/11, uniq 1, type 3  
  4.    root page 131076, appr.key vals 10179472, leaf pages 360335, size pages 360768  
  5.    FIELDS:  a DB_TRX_ID DB_ROLL_PTR b c d e f g h i  
  6.   INDEXname b, id 0 457, fields 1/2, uniq 2, type 0  
  7.    root page 131077, appr.key vals 8533926, leaf pages 16443, size pages 18944  
  8.    FIELDS:  b a  
  9.   INDEXname c, id 0 458, fields 1/2, uniq 2, type 0  
  10.    root page 131078, appr.key vals 9, leaf pages 9847, size pages 11313  
  11.    FIELDS:  c a  
  12.   INDEXname d, id 0 459, fields 1/2, uniq 2, type 0  
  13.    root page 131079, appr.key vals 2541345, leaf pages 16343, size pages 18816  
  14.    FIELDS:  d a  
  15.   INDEXname e, id 0 460, fields 1/2, uniq 2, type 0  
  16.    root page 131080, appr.key vals 8703960, leaf pages 103311, size pages 119360  
  17.    FIELDS:  e a  
cは9800ページ程度、bとdはそれぞれ16000ページ程度、eは100000ページ程度であり、クエリの実行結果をよく反映しているこ とが分かる。このように、COUNT()のチューニングにはインデックスの最適化が重要なのである。(InnoDBテーブルモニターについては、執筆中の 書籍でも触れているので乞うご期待!) セカンダリインデックスがついている状態でも、セカンダリインデックスがなく、なおかつNULL値が格納される可能性があるカラムをわざわざ指定すると、 次のように主キーのスキャンが行われる。(EXPLAINを見るとtype=ALLになるが、InnoDBの場合は主キーのスキャンと同じことである。)
  1. mysql> select count(i) from t1;  
  2. +----------+  
  3. count(i) |  
  4. +----------+  
  5. | 10000000 |   
  6. +----------+  
  7. 1 row in set (1 min 34.41 sec)  

Covering Indexの活用

前節はテーブル全体の件数を数えるクエリについてのチューニングの話であるが、 GROUP BY句やWHERE句を用いた場合はどのようなクエリを書けばいいのだろう?もちろんこの場合も先ほどの場合と同様、出来るだけサイズが小さいセカンダリ インデックスのスキャンになるのが望ましい。インデックスを用いてスキャンされるためには、Covering Indexがなければいけない。 例えば次のクエリはカラムdに貼らているインデックスがCovering Indexになる。
  1. mysql> SELECT FLOOR(YEAR(d)/100)*100 AS drange, COUNT(*) FROM t1 GROUP BY drange HAVING drange IS NOT NULL;;  
  2. +--------+----------+  
  3. | drange | COUNT(*) |  
  4. +--------+----------+  
  5. |   1000 |     1283 |   
  6. ・・・出力省略・・・  
  7. |   9900 |     1272 |   
  8. +--------+----------+  
  9. 90 rows in set (10.95 sec)  
  10.   
  11. mysql> SELECT COUNT(*) FROM t1 WHERE d BETWEEN '1990-01-01' AND '2000-01-01';  
  12. +----------+  
  13. COUNT(*) |  
  14. +----------+  
  15. |     1034 |   
  16. +----------+  
  17. 1 row in set (0.00 sec)  
次は少々複雑なクエリの例である。この例では、(c,d)というインデックスがCovering Indexとなる。
  1. mysql> SELECT FLOOR(YEAR(d)/100)*100 AS drange, COUNT(*) FROM t1 WHERE c = 100 GROUP BY drange HAVING drange IS NOT NULL;  
  2. ・・・出力省略・・・  
さきほど作成したテスト用テーブルには(c,d)というインデックスはないため(c)が使われるが、この場合c=100で行を絞り込んでから 主キーを検索して行をフェッチし、GROUP BYが行われることになり、上記のクエリは割と時間が掛かってしまう。Covering Indexがあれば主キーをフェッチする必要はなくなり、格段に効率が向上するのである。 なお、上記のテーブルをALTERするにはかなり時間がかかるため(投稿が遅れるので)、結果は割愛させて頂く!!が、Covering Indexの効果は絶大なので、ぜひ自分で試して見て欲しい。

セカンダリインデックス追加時の注意事項

これまで、セカンダリインデックスを追加することで如何にCOUNT()クエリが高速化され るかということについて説明したが、何でもかんでもインデックスをつければいいというわけではないということに触れてエントリを締めくくることにする。セ カンダリインデックスをつければ、その分確実にページが増えてディスク上に占めるテーブルスペースの容量も増え、さらにはバッファプールを逼迫することに なるだろう。それにより、インデックスを追加することでCOUNT()クエリが高速化する代わりに、全体的なパフォーマンスが低下するかも知れないのであ る。従って、インデックスを追加した場合には、アプリケーション全体の性能を確認するべきなのである。そのアプリケーションにとって、COUNT()が頻 繁に実行されるクエリであれば、セカンダリインデックスの追加を検討すると良いだろう。そうでなければ、たまにCOUNT()を実行するときだけ遅くなる のをガマンしたほうが、全体的なパフォーマンスは良好に保たれるのである。

おまけ:トリガの活用リターンズ

先ほど、GROUP BY句を利用する場合にはトリガ+別テーブルでテーブル全体の件数を保持する方法は使えないという旨のことを述べたが、次のようなテーブルを使って解決することも可能だ。
  1. CREATE TABLE t1_count_by_d (  
  2.   drange date NOT NULL,  
  3.   nrows int(10) unsigned NOT NULL,  
  4.   PRIMARY KEY (drange, nrows)  
  5. ) ENGINE=InnoDB;  
トリガの記述は割愛するが、drangeごとに行数を増減させればいい。この方法ならCovering Indexよりも遙かに少ないディスク容量で、高速な件数の取得が可能になる。ただし、トリガは更新時のオーバーヘッドが大きいので、どちらの方が良いか は、ぜひ皆さんの手で検証して頂きたい。

引用元

更新:2010/03/09 10:30 カテゴリ: MySQL  > SQL文 ▲トップ

No.2028 最速でOpenPNE3でmysqldumpしてリストアする方法

最速でOpenPNE3でmysqldumpしてリストアする方法

出典: Public KFSPedia


2010年1月21日 (木) 09:03 時点における最新版

mysqldump --opt --no-autocommit -u DBUSERNAME -p --default-character-set=utf8 DBNAME > hoge.sql
mysql -u DBUSERNAME -p --default-character-set=utf8 DBNAME < hoge.sql


■最速dump時の中身
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

LOCK TABLES `member` WRITE;
/*!40000 ALTER TABLE `member` DISABLE KEYS */;
set autocommit=0;

INSERTデータ

/*!40000 ALTER TABLE `member` ENABLE KEYS */;
UNLOCK TABLES;
commit;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

引用元

更新:2010/01/22 12:05 カテゴリ: MySQL  > SQL文 ▲トップ
58件中 1 〜 10 表示  1 | 2 | 3 | 4 ... 次の10件> 最後»

FuelPHP

Mac

web開発

プロマネ

マネタイズ

プレゼン

webサービス運用

webサービス

Linux

サーバ管理

MySQL

ソース・開発

svn・git

PHP

HTML・CSS

JavaScript

ツール, ライブラリ

ビジネス

テンプレート

負荷・チューニング

Windows

メール

メール・手紙文例

CodeIgniter

オブジェクト指向

UI・フロントエンド

cloud

マークアップ・テキスト

Flash

デザイン

DBその他

Ruby

PostgreSQL

ユーティリティ・ソフト

Firefox

ハードウェア

Google

symfony

OpenPNE全般

OpenPNE2

Hack(賢コツ)

OpenPNE3

リンク

個人開発

その他

未確認

KVS

ubuntu

Android

負荷試験

オープンソース

社会

便利ツール

マネー

Twig

食品宅配

WEB設計

オーディオ

一般常識

アプリ開発

サイトマップ

うずら技術ブログ

たませんSNS

rss2.0