よく 検索結果などに『◯◯件 ヒットしました』と表示するWEBアプリがあります。
その時に,
件数を取得するSQL文は
SELECT * FROM data_table; SELECT count(*) AS count FROM data_table;
とすることが多いと思いますが、SELECT時に下記のように「SQL_CALC_FOUND_ROWS」を追加しておいて、その後 FOUND_ROWS() を記述するとかなり高速に取得できます。
SELECT SQL_CALC_FOUND_ROWS * FROM data_table; SELECT FOUND_ROWS() AS count
がSQL文が複雑な場合は逆に遅くなることもあるようです。
http://ma-bank.com/item/998
MySQLのスローログ（実行に時間がかかったSQLクエリ）を取得するには
# 1秒以上かかったクエリを mysql_slow.log に保存する
slow_query_log=ON
long_query_time=1
slow_query_log_file=/var/log/mysql_slow.log
# インデックスを使わない検索を スローログに追加する
# log-queries-not-using-indexes
touch /var/log/mysql_slow.log
chown mysql /var/log/mysql_slow.log
chgrp mysql /var/log/mysql_slow.log
service mysqld stop
service mysqld start
# mysqladmin コマンドで確認する
mysqladmin -uroot -p status
# スローログを表示する
cat /var/log/mysql_slow.log
現在のMySQLの設定をみるには MySQLから
show variables;
MySQLの設定は（ /etc/my.cnf ）を書き換える。
設定例は
key_buffer = 384M sort_buffer_size = 2M read_buffer_size = 2M
key_buffer = 256M sort_buffer_size = 1M read_buffer_size = 1M
key_buffer = 16M sort_buffer_size = 512K read_buffer_size = 0
key_buffer = 16K sort_buffer_size = 64K read_buffer_size = 0
このあたりを参考に必ず設定する。
またMySQL ABでは，「key_buffer」の値はマシンに搭載しているメモリーの1/4を推奨値としている。
http://vine-linux.ddo.jp/linux/sql/mycnf.php
http://www.819410.com/FreeBSD6/shop/-145.html
MySQLにはクエリキャッシュの機能があり、これをONにするとクエリ結果をキャッシュから読み出すことが出来て高速な動作が期待できる。
show variables like 'query_cache_%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec)
【query_cache_size】が【0】なのでクエリキャッシュが有効ではないことがわかる
「query_cache_type」をONに、「query_cache_size」を0より大きな適切な値に設定する必要がある。
/etc/my.cnf の [mysqld] の項目にクエリキャッシュに関する記述を追加する
[mysqld] # (query_cache_limit)これより大きい結果はキャッシュしない # (query_cache_min_res_unit) 4K が推奨値 # (query_cache_size)クエリキャッシュに割り当てるメモリ（Bytes） # (query_cache_type) 0:OFF 1:ON 2:DEMAND query_cache_limit=1M query_cache_min_res_unit=4k query_cache_size=24M query_cache_type=1
/etc/init.d/mysqld restart （または）service mysqld restart
<pre>SHOW STATUS LIKE 'Qcache%';</pre>
でクエリキャッシュの状態を確認する。
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 2 | 空きブロック数 | Qcache_free_memory | 24213616 | 空きメモリのサイズ (バイト) | Qcache_hits | 868 | クエリのヒット数 | Qcache_inserts | 900 | キャッシュに入れられたクエリの数 | Qcache_lowmem_prunes | 0 | メモリが足りないために削除された数 | Qcache_not_cached | 21 | キャッシュに入れられなかったクエリの数 | Qcache_queries_in_cache | 846 | キャッシュ内のクエリの数 | Qcache_total_blocks | 1711 | ブロックの領域の合計 +-------------------------+----------+ 8 rows in set (0.00 sec)
【Qcache_lowmem_prunes】を注意深く見ればいいことがわかります。
キャッシュは SELECT文にのみ適用される。
MySQL4.1以前を使用している場合 SQL文 の 'SELECT' の前に スペースが入っているとクエリキャッシュが有効にならないので注意。
（MySQL は先頭3文字のみを見て SEL であった場合のみクエリキャッシュに格納します。）
show index from 【テーブル名】;
alter table 【テーブル名】 add index 【インデックス名】 (【フィールド名】);
例
テーブル「item_dt」
対象カラム「item_name」
に
インデックス名「item_name_idx」 のインデックスを作成します。
ALTER TABLE item_dt ADD INDEX item_name_idx(item_name);
TEXT型のように可変長テキストにインデックスを作成するには長さを指定します。 例（先頭255バイトまでのテキストにインデックスを貼る）
ALTER TABLE item_dt ADD INDEX item_name_idx(item_name(255));
DROP INDEX 【インデックス名】 ON 【テーブル名】;
EXPLAIN 【調査したいselect文】
表示されるデータの見方
【row】：テーブル読まれた行の数 (少ない方が良い。インデックス作成で激減する)
【type】：次の順番で早い順となる
・system, const(結果が単一行)
・eq_ref(UNIQUE or PRIMARY index使用)
・ref (index使用の単一パス検索)
・range ( indexの範囲検索)
・index(index全体をスキャン)
・ALL (全DBデータを検索)