履歴系テーブルは基本的にページャーでページ分割することがほとんどだと思います。前回のエントリーでもLIMIT句を使って30行だけ取ってきている例となっています。今回の問題点は、巨大なテーブル×巨大なテーブルのJOINをしようとするから重いのであって、…

まずは簡単な履歴系のテーブルから実体化してみましょう例としてユーザのテーブルと紐づいているコメント機能を提示します。

MySQLの著名な方々にブックマークされてしまって戦々恐々としております名無しのIT系です。 今さらですが本BLOGを見ていただくより先に見ていただきたい方々のサイトを紹介致します。 sh2さんのSH2の日記 sakaikさんのsakaikの日々雑感〜(T)編 言わずと知れ…

さてMySQLでOracleのマテリアライズドビューを再現するにあたり、マテリアライズドビューのモードによってそれぞれ再現方法が異なります。元となるクエリーが、集計クエリーなのか、複合クエリーなのか等によって、それぞれ内容が変わってきてしまいますが、…

Oracleではマテリアライズドビューというビューを実体化させる機能があります。MySQLのビューにはマテリアライズドビューはありませんが、サマリーテーブルと(トリガー、イベントスケジューラー等)を組み合わせることによって同様な機能を実現可能です。この…

場面によっては、参照整合性が必要ない場合がある。その場面が、長時間のロックを要する場合、ダーティーリードにモードを切り替えることによってパフォーマンスを向上させることができることがある。要するにKVSと同じように参照整合性を犠牲にすることによ…

MySQLでブログを書いていてパフォーマンス系の記事を書こと思った時にダミーデータの用意をするのが面倒です。generate_series*1があれば簡単なのですがMySQLにはないのでストアドプロシージャで汎用的にダミーデータを生成できるものを作ってみました。以後…

MySQLでは、関数インデックスが使えません。そこで擬似的に関数(式)インデックスを作ってみることにしましょう。ユーザテーブルでメールアドレスを小文字にした時の関数インデックスを作る場合を例としてみます。メールアドレスをユニークにしたものの、大文…

今更GROUP BY？というのはごもっともな話です。普通のGROUP BYはマニュアルを見ていただくとして、ここでは裏MySQL クエリー入門的なGROUP BYのサンプル例をいくつか紹介していきましょう。 日毎に集計するには SELECT DATE(access_time), count(access_id) …

以下のようなピタゴラスの定理を使った指定した経度緯度に最も近いデータを取得するSQLは結構ありがちですが、CPU負荷が高く効率も悪いのでMySQLに標準搭載となった空間情報(geometry)を使ってみることにします。 SELECT * FROM loc ORDER BY power(abs(lati…

今回はクライアントから、そんなの簡単じゃないの？という程度に依頼されるが、実は結構めんどうな うっとぉしい依頼の解決方法ついて解説します。 UPDATE event e1,event e2 SET e1.event_name=e2.event_name, e1.event_flg=e2.event_flg WHERE e1.event_id…

MySQLネタもそろそろ尽きてきたので、趣向を変えてMySQLの実践Tipを紹介していこうと思います。 まずは怪盗ロワイヤルのように3分に1ポイント回復する実装について以下のように3分に一度バッチを回して、全ユーザの体力を+1ずつアップデートするのは効率が悪…

CREATE TABLE `lv` ( `lv` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'レベル', `exp` INT NOT NULL COMMENT '必要経験値' ) ENGINE = MYISAM COMMENT = 'レベルテーブル'; INSERT INTO `lv` (`exp`) VALUES ('100'),('200'),('350'),('600'),('900'…

ユーザ変数を使う代表例としてランキング系のテーブルで順位表示をしたい場合 以下のようなランキングテーブルを用意します。 CREATE TABLE `ranking` ( `ranking_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'ランキングID', `category_id` INT N…

テンポラリーテーブルはその名のとおり、DB上に一時的にデータを格納するテーブルを作成する機能です。テンポラリーテーブルは『create temporary table テーブル名』とcreate table文にtemporaryを指定することで作ることができます。 この作成されたテーブ…

CSVのインポートなどでカラム(列)の順番を間違ってしまったときなどに以下のようなSQLで値を入れ替えることができそうですが、 set @str = 0; update `charactor` set `str` = `grd`, `grd` = `int`, `int` = `agi`, `agi` = `dex`, `dex` = `vit`, `vit` = …

前回のエントリーでINSERT ON DUPLICATE KEY UPDATEを紹介しましたが、これを使うとeltやfieldを使ったバルクアップデートではなく、重複するキーが既に存在するという前提であれば、バルクアップデートとして使用できます。用途としては、CSVアップロードや…

MERGE,UPSERTと言われる、UPDATE文とINSERT文を条件によって自動的に切り替えることができるSQL文は、MySQLでは2種類用意されています。 REPLACE、INSERT ON DUPLICATE KEY UPDATEです。それぞれの違いは REPLACE INSERT文と同じ構文でINSERTの部分をREPLACE…

最近は、必ずといってもいいほどあるプログラムの実技試験。 phpでFizzBuzzをおさらい。 といってもただ単にやっても面白く無いので、forを使わないで配列を用いた天邪鬼ver

カテゴリーの最大数を64以下にすることが可能であれば、カンマ区切りのvarchar型のように非効率な方法ではなく、ビットをフラグとして使うことによって効率よく格納できます。 ALTER TABLE `platform` ADD `set_program_id` BIGINT NOT NULL COMMENT 'プログ…

postgresには連番の仮想表を作成できるgenerate_seriesという便利な関数があります。これは存在しない日付などを作るときに便利な関数です。MySQLではこのような関数はないのでユーザ変数を用いて擬似的に再現してみましょう。 SELECT 0 generate_series FRO…

裏MySQLクエリー入門(13)で出したサンプルですがデータ構造としては最悪です。これは実際に遭遇したデータ構造なのですが、これは、プログラマーがメインの人が良くやってしまうデータ構造ですね。カテゴリーIDを文字列として、カンマ区切りで格納...。気持…

前回の裏MySQLクエリー入門(13) でINT型のprogram_idとVARCHAR型でprogram_idがカンマ区切りのprogram_idsを結合すると一見すると動作しているよう*1に見えます。 SELECT * FROM `platform` pf, `program` pg WHERE pg.program_id = pf.program_ids platform…

先週は、仕事で燃え尽きて萌えかす*1程の気力しか残っていなかったIT7Cです。今日はMySQLの文字列関数FIND_IN_SETについて紹介したいと思います。FIND_IN_SETは、カンマ区切りの文字列から任意の文字列を取り出す、GROUP_CONCATの逆の関数みたいなものです。…

MySQLの集約関数にはBIT_AND,BIT_OR,BIT_XOR等、ビット演算子系の集約関数があります。具体的な使用例をあまりみることがないので、今回はこの関数の使いどころを紹介したいと思います。例えばユーザーのアイテムテーブルからアイテムID 1,3,5,7の全ての取得…

仮想表の作成、わざわざ新規にテーブルを作るまでもないようなデータ量の場合以下のようにUNION ALLを使うことにより、仮想的にテーブルを作ることが可能です。プライマリキーと値(Key value)しかない場合にはeltまたは、field + eltで十分です。こちらを使…

MySQLで動的クエリーを実行する為にプリペアド ステートメントを使います。PREPAREでSQLを定義し、EXECUTEでPREPAREで定義したSQLを実行します。 PREPARE stmt_name FROM preparable_stmt EXECUTE stmt_name [USING @var_name [, @var_name] ...] UPDATE型の…

GROUP_CONCATは、グループ内の文字列を、指定された区切り文字で結合する。集約(GROUP)関数です。文字列版のSUMのようなものですね。phpで言えばimplodeみたいなものです。ここでは、裏MySQL クエリー入門 (7) のサンプルをカンマ区切りにしてみましょう。 …

InformationSchemaとは、テーブル名やカラム名などのシステム情報をテーブルの形でみせるシステムテーブルでSQLでいろいろ情報を取得できます。unixで言うところのprocシステムですね。SQLでシステム情報にアクセスできることで、システム情報と既存のテーブ…

このTIPSはRDBMSすべてで共通で使えるテクニックなので、知っている人も多くいると思いますが、今後の応用編で必須になってきますのでおさらいしておきましょう。 例えば結合するテーブルが多すぎる場合など、何個かのSQLに小分けすることでJOINを回避するこ…