LIMIT 条項は SELECT ステートメントに返された行数を制限するのに利用する事ができます。
LIMIT は、負数以外の整数定数でなければいけない、1つか2つの数値引数を取ります。
(準備されたステートメントを利用している時以外)

その2つの引数のうち、最初の物は返される最初の行のオフセットを指定し、2つめの物は返される行の
最高数を指定します。冒頭の行のオフセットは0です。(1ではありません)

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

1つの引数で、その値は結果セットの最初から返される行数を指定します。

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

言い換えると、LIMIT row_count は LIMIT 0, row_count と同等だという事になります。

1行返します

SET @a=1; /* SELECT @a :=1 */
PREPARE STMT FROM 'SELECT * FROM City LIMIT ?';
EXECUTE STMT USING @a;

prepare_limit

2行目から6行目を返します

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM City LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

prepare_limit_row

1行目~5行目まで
prepare_limit_row_0


用途が無いので、業務で利用した事はありませんが以下のように
2つのテーブルをFROM句のあとに置くとデータは以下のようにそれぞれの
列を掛け算した分だけ結果が返ってくる。

以下の例だとそれぞれのテーブルに4件ずつのデータが入っているので
4X4=16件のデータが表示されます。これがもしデータ量が多いテーブルだと
相当時間がかかり重たくなります。

select * from MYSQLIMP,MYSQLIMP2;
select_join

データ量が多いテーブルの場合
mysql> SET @CityCount = (SELECT COUNT(*) FROM City);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @CountryCount = (SELECT COUNT(*) FROM Country);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @CityCount * @CountryCount;
+—————————-+
| @CityCount * @CountryCount |
+—————————-+
| 979440 |
+—————————-+
1 row in set (0.00 sec)

979440件のデータが表示されると重たい…
select_all1

INFORMATION_SCHEMAのテーブルを利用してテーブル結合の検証

information_schema_tables_join

mysql> select count(*) from SCHEMATA;
+———-+
| count(*) |
+———-+
| 9 |
+———-+
1 row in set (0.00 sec)

mysql> select count(*) from TABLES;
+———-+
| count(*) |
+———-+
| 159 |
+———-+
1 row in set (0.01 sec)

mysql> set @SCHEMATA = (select count(*) from SCHEMATA);
Query OK, 0 rows affected (0.01 sec)

mysql> set @TABLES = (select count(*) from TABLES);
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from SCHEMATA,TABLES;
+———-+
| count(*) |
+———-+
| 1431 |
+———-+
1 row in set (0.01 sec)

mysql> select @SCHEMATA * @TABLES;
+———————+
| @SCHEMATA * @TABLES |
+———————+
| 1431 |
+———————+
1 row in set (0.00 sec)

select_total

SCHEMATAには9件のデータがありTABLESテーブルには159件のデータがあるので、
以下のようなwhere句の条件だと多いデータの多いTABLESの列分(159件)だけデータが表示される。
mysql> select count(*) from SCHEMATA,TABLES
-> where SCHEMATA.SCHEMA_NAME = TABLES.TABLE_SCHEMA;

+———-+
| count(*) |
+———-+
| 159 |
+———-+
1 row in set (0.01 sec)

mysql>

例えば、上記2つのテーブルを利用すると以下のような運用で利用出来る確認用クエリーが作成出来る。
desc_tables

SCHEMA_NAME,テーブルのリストを作成したり

select
SCHEMA_NAME,DEFAULT_COLLATION_NAME,
ENGINE,TABLE_TYPE
from SCHEMATA,TABLES
where SCHEMATA.SCHEMA_NAME = TABLES.TABLE_SCHEMA;

schemeta_table
159 rows in set (0.05 sec)


ビュー(更新可能なビューを含む) はMySQL Server 5.1から入手可能。
旧バージョンMySQLを5.1にアップグレードした場合、ビューの使用はビュー関連の
権限を含むようにグラントテーブルもアップグレードが必要です。

———————————
メモ
———————————
mysql_upgrade コマンドは、古い方、つまり mysql_fix_privilege_tables より優先です。
MySQL 5.1.7 では、シェル スクリプト として mysql_upgrade が加えられ、Unix システムだけで機能します。
MySQL 5.1.10 以降は、mysql_upgrade は実行可能なバイナリとして、すべてのシステムで使用できます。
mysql_upgrade をサポートしているものより古いシステムでは、手動で mysqlcheck コマンドを実行し、
システム テーブルのアップグレードを行ないます

ビューの現在の実装には欠点があります。もしユーザがビューの作成に必要な基本権限
( CREATE VIEW と SELECT 権限 ) を取得した場合、SHOW VIEW 権限も取得しない限り、
そのユーザはオブジェクトの SHOW CREATE VIEW を呼び出すことはできない。

PREPARE によって準備されたステートメントがビューを参照する場合、後でステートメントが実行される度
に参照されるビューの内容が、ステートメントが準備できた時のビューの内容になります。
これは、ステートメントが準備された後、実行される前にビュー定義が変更されても同じ。


CREATE VIEW v AS SELECT 1;
PREPARE s FROM 'SELECT * FROM v';
ALTER VIEW v AS SELECT 2;
EXECUTE s;

view11

幾つかのビューは更新可能です。すなわち、基礎をなすテーブルの内容を更新するため、
UPDATE、DELETEもしくはINSERTのようなステートメントの中でそれらを使うことができます。
ビューを更新可能にするため、ビュー中の行と基礎テーブル中の行の間に1対1の関係が存在しなければなりません。
ビューを更新不能にするその他の生成子もあります。もっと具体的に言うと、それが以下のいずれかを含んでいると
ビューは更新可能となりません。

* 集約ファンクション(SUM()、 MIN()、 MAX()、COUNT()等)
* DISTINCT
* GROUP BY
* HAVING
* UNION もしくはUNION ALL
* 選択リスト中のサブ・クエリ
* 特定結合(このセクション中の後の部分に追加した結合の説明参照)
* FROM節中の更新不能ビュー
* FROM節中のテーブルを参照するWHERE 節中のサブ・クエリ
* 文字値だけを参照(この場合、更新する基礎となるテーブルは存在しません)
* ALGORITHM = TEMPTABLE (テンポラリテーブルの使用は常にビューを更新不能にする)

(INSERTステートメントで更新不能となる)挿入性に関して、それがビューカラムに対する
これらの追加条件も満たすと、更新不能ビューが挿入可能になります。

* ビューカラム名に重複があってはなりません。
* ビューには、デフォルト値を持っていないベーステーブル内にある全てのカラムを含んでいなくてはなりません。
* ビューカラムは派生カラムではなく、単純なカラムリファレンスでなければなりません。
派生カラムは単純なカラムリファレンスでなく、表現から派生したものです。これらは派生カラムの例です。

3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

UPDATE,INSERTが可能か確認検証


CREATE VIEW V_Region (CityName, CountryName)
AS SELECT City.Name, Country.Name FROM City, Country
WHERE City.CountryCode = Country.Code
AND City.CountryCode = 'DEU';

VIEW
create_view

    UPDATE確認


update V_Region set CityName = 'HAMBURG'
where CityName = 'Hamburg';

1つのテーブルしか更新されないのでエラーにはならない。
view_update

複数テーブルを更新しようとするとUPDATEもエラーになる

update V_Region set
CityName = 'Hamburg',
CountryName = 'Japan'
where CityName = 'HAMBURG';

複数テーブルを更新しようとしているのでエラーになる
view_update_multi

    INSERT確認

insert into V_Region(CityName,CountryName) values('NewCity','Japan');

※ 複数のテーブルを更新することになるのでエラーになる。
view_insert1

参考サイト
20.2. CREATE VIEW 構文


D.4. ビューの規制


4.5.4. mysql_upgrade — MySQL アップグレードのテーブル チェック


GROUP BY 句に WITH ROLLUP 修飾子を加えると、
クエリがすべてのデータにわたる総合計の値を示す行を生成します :

ROLLUP を使用する場合、ORDER BY 句を同時に使用して結果をソートすることはできません。
つまり、ROLLUP と ORDER BY は互いに排し合うということになります。
しかし、ソートの順番をいくらかコントロールすることは可能です。
MySQL の GROUP BY が結果をソートし、そして 明示的な ASC および DESC キーワードを
GROUP BY 内で名付けられたカラムと使用し、各カラムのソート順を指定することができます。
( しかし、ROLLUP によって加えられた高レベルな要約行は、ソート順に関わらず、
それらが計算された行の後に現れます。)

LIMIT はクライアントに戻される行の数を限定するのに使用できます。LIMIT は ROLLUP
の後に適用され、それによって ROLLUP によって追加された行に対しての制限が適用されます。


mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
+--------+-------+
4 rows in set (0.01 sec)

mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE WITH ROLLUP;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
| NULL | 168 |
+--------+-------+
5 rows in set (0.04 sec)

roll_up

11.11.2. GROUP BY 修飾子


SELECT は1 つ以上のテーブルからレコードを選択して取り出すときに使用します。
各 select_expression は、取り出すカラムを表します。
SELECT は、どのテーブルも参照することなく、計算によって求められたレコードを取り出すときにも使用できます。


mysql> select
-> TABLE_NAME,
-> YEAR(CREATE_TIME) AS Year,
-> MONTH(CREATE_TIME) AS Month,
-> DAY(CREATE_TIME) AS DAY
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> Order by Year DESC,Month DESC, DAY DESC;
+----------------+------+-------+------+
| TABLE_NAME | Year | Month | DAY |
+----------------+------+-------+------+
| A10 | 2009 | 7 | 23 |
| innodbtable | 2009 | 7 | 23 |
| T10 | 2009 | 7 | 23 |
| innodb_monitor | 2009 | 7 | 21 |
+----------------+------+-------+------+
4 rows in set (0.00 sec)

mysql>

table2

COLLATIONによってOrder byの結果も変わってきます。
(CI / CS / Binなど)


mysql> select
-> TABLE_NAME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> order by TABLE_NAME
-> COLLATE utf8_general_ci;

+----------------+
| TABLE_NAME |
+----------------+
| A10 |
| a10 |
| innodbtable |
| innodb_monitor |
| t10 |
| T10 |
+----------------+
6 rows in set (0.00 sec)

mysql> select
-> TABLE_NAME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> order by TABLE_NAME
-> COLLATE utf8_bin;
+----------------+
| TABLE_NAME |
+----------------+
| A10 |
| T10 |
| a10 |
| innodb_monitor |
| innodbtable |
| t10 |
+----------------+
6 rows in set (0.00 sec)

mysql>

cs

LIMIT 句


mysql> select
-> TABLE_NAME,
-> CREATE_TIME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> Order by CREATE_TIME
-> LIMIT 1;
+----------------+---------------------+
| TABLE_NAME | CREATE_TIME |
+----------------+---------------------+
| innodb_monitor | 2009-07-21 18:08:11 |
+----------------+---------------------+
1 row in set (0.00 sec)


choko

GROUP BY


mysql> select
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
+--------+-------+
4 rows in set (0.01 sec)

mysql>

mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> FROM information_schema.TABLES
-> GROUP BY ENGINE
-> HAVING ENGINE='MYISAM' OR ENGINE='INNODB'
-> ORDER BY Total DESC;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| MyISAM | 134 |
| InnoDB | 12 |
+--------+-------+
2 rows in set (0.01 sec)

mysql>

groupby

having

参考サイト:
6.4.1. SELECT 構文


SELECTのパフォーマンスに関係ある3つのシステム変数

sort_buffer_size ——> Order by , Grop byのパフォーマンス
join_buffer_size ——> JOINのパフォーマンス …. MYSQLのパフォーマンス
key_buffer_size ——> index関連するステートメント(MYISAM固有)

buffer_size

———————————————————-
おまけ: テイキメンテナンスを忘れずに
———————————————————-
※ mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

※ Query Cache はSelectのスピードアップに大切です。

mysql> 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.01 sec)

mysql>

——————————————————————————————————-
Key Cache Efficiency = 1 – (KEY_READS / KEY_READ_REQUESTS)
——————————————————————————————————-

select 1 – ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = ‘KEY_READS’)
/ (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = ‘KEY_READ_REQUESTS’))
as ‘Key cache efficiency’;

key_cache_efficiency

もしkey_buffer_sizeが小さくて、メモリーがあまっていたら以下のようにメモリーを
key_bufferに与えて下さい。

mysql> set global key_buffer_size = 5 * 1024 * 1024;

key_buffer_size