裏MySQL入門(20) 基礎編12 テンポラリーテーブル

　テンポラリーテーブルはその名のとおり、DB上に一時的にデータを格納するテーブルを作成する機能です。

テンポラリーテーブルは『create temporary table テーブル名』とcreate table文にtemporaryを指定することで作ることができます。
この作成されたテーブルは、同一のDBコネクション内で有効でDBコネクションが切れると自動的に削除されます、かつ指定されたテーブル名も同一DBコネクション内でのみ有効なものとなります。

また、create temporary table テーブル名 select文とすることで指定したSELECT文のデータを作成したtemporary tableに格納することができます。

実は、MySQLのサブクエリーは、内部的にこのテンポラリーテーブルを作っています。
逆に言えば、この低レベルの操作、テンポラリーテーブルの操作を自前で行うことによって劇的に高速化できることがあります。

例えば、MySQLのサブクエリーは、内部的にこのテンポラリーテーブルを作ると同時にテンポラリーテーブル作成中は参照整合性を維持する為に使っているテーブルをすべてロックしてしまいます。

が、テーブルロックして参照整合性を維持する必要がないことがほとんどなので、重いサブクエリーを使用するSQLを自前でテンポラリーテーブルを作るようにすることでテーブルのロックを回避して高速化をはかれることがあります。

また、サブクエリー実行時に生成されるテンポラリーテーブルにはインデックスが張れないので、自前でテンポラリーテーブルを作る場合はさらなる高速化が可能です。

インデックスを貼るにはcreate temporary table テーブル名 (KEY インデックス名 (カラム名) ) select 〜とします。

これを使うと、ページャー付き一覧ページ表示のところで良く使われているphpでクエリー分割とかいったことはする必要がなかったりします......。

尚、ストレージエンジンをMemoryとかにして高速化することもできるのですがメモリを使い果たしたりすることもあって危険です。
ストレージエンジンを指定しない場合は、勝手にデータが少なければメモリー上にでなければディスクを使うので何も指定しない方がいいです。