サブクエリーとアウタークエリーの関連性について

Non-correlated subqueryの例
サブクエリーはouter queryを意識せずとも成り立つので、
Correlationはありません。

SELECT DISTINCT Continent
FROM Country
WHERE Code IN (SELECT CountryCode
FROM CountryLanguage
WHERE Language='English'
AND Percentage>50
);


SELECT CountryCode
FROM CountryLanguage
WHERE Language='English'
AND Percentage>50;

subquery_sample

以下のQueryもCorrelationはありません。

SELECT * FROM Country
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country
WHERE Continent = 'South America')\G


SELECT MIN(Population) FROM Country
WHERE Continent = 'South America';

subquery_correlation

上記QueryをCorrelationがあるqueryに書き換えてみる。
変更前

SELECT * FROM Country
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country
WHERE Continent = 'South America')\G

変更後

SELECT * FROM Country as COUNT01
WHERE Continent = 'South America'
AND Population =
(SELECT MIN(Population) FROM Country COUNT02
WHERE COUNT01.Continent = COUNT02.Continent)\G

——————————————————————————–
上記クエリーは以下のように先頭にAliasを付けてもOK
——————————————————————————–

SELECT COUNT01.* FROM Country as COUNT01
WHERE COUNT01.Continent = 'South America'
AND COUNT01.Population =
(SELECT MIN(COUNT02.Population) FROM Country as COUNT02
WHERE COUNT01.Continent = COUNT02.Continent)\G

——————————————————————–
subquery_change_relation

    サンプルその2


SELECT C.Continent, C.Name, C.SurfaceArea
FROM Country C
WHERE SurfaceArea =(
SELECT MAX(SurfaceArea)
FROM Country C2
WHERE C2.Continent = C.Continent);

select_sub_query

ALLをサブクエリーに利用する
ALL という語は比較演算子の後に指定するもので、“サブクエリが返すレコードの
ALL(すべて)に対して比較が TRUE の場合 TRUE を返す” ことを表します。
次に例を示します。
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

テーブル t1 に {10} という値を含むレコードがあるとします。 テーブル t2 に含まれている値が {-5,0,+5}
の場合、この式は TRUE になります。なぜなら t2 に含まれている値はすべて 10 より小さいからです。
テーブル t2 に含まれている値が {12,6,NULL,-100} の場合、この式は FALSE になります。
なぜなら t2 に、10 よりも大きい 12 という値が 1 つ存在するからです。 テーブル t2 に含まれている
値が {0,NULL,1} の場合、この式は UNKNOWN になります。

(例)Countryテーブル
all_t1

以下のサブクエリーは全ての結果からPopulationが “>=” の値を算出しているので、
必然的にPopulationの値が一番多い”=”大陸が選択されます。
全部の値の中から ”>” にあたる値は無く “=” のみが最大値に適用される。

SELECT Continent, Name
FROM Country c1
WHERE Population >= ALL (SELECT Population
FROM Country c2
WHERE c1.Continent=c2.Continent
);

all

ANYをサブクエリーに利用する
ANY という語は比較演算子の後に指定するもので、“サブクエリが返すレコードの
ANY(いずれか)に対して比較が TRUE の場合 TRUE を返す” ことを表します。 次に例を示します。

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

テーブル t1 に {10} という値を含むレコードがあるとします。 テーブル t2 に含まれている値が
{21,14,7} の場合、この式は TRUE になります。なぜなら t2 に、10 よりも小さい 7 という値が
存在するからです。 テーブル t2 に含まれている値が {20,10} の場合や、テーブル t2 が空の場合、
この式は FALSE になります。 テーブル t2 に含まれている値が {NULL,NULL,NULL} の場合、
この式は UNKNOWN になります。

IN という語は = ANY のエイリアスです。したがって、次の 2 つのステートメントは同じです。

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

SOME という語は ANY のエイリアスです。したがって、次の 2 つのステートメントは同じです。

SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

SOME はめったに使用されませんが、上の例では、この語がどのような場合に役立つかを示しています。
“a is not equal to any b” という英語のフレーズを、ほとんどの人は “a と等しい b はまったく存在しない
” という意味に受け取ります。しかし、SQL 構文では意味が異なります。ANY の代わりに <> SOME
を使用することによって、このクエリの本当の意味を誰もが確実に理解できるようにすることができます。

以下のクエリは、面積がAVGより大きい大陸と名前を選択してます。
このサブクエリーは、アウタークエリーとは関係なく実行可能です。(not correlated subquery)


SELECT Continent, Name
FROM Country
WHERE SurfaceArea > ANY (SELECT AVG(SurfaceArea)
FROM Country
GROUP BY Continent
);

any

上記クエリをALLとANYで比較
all_any

INをサブクエリーに利用する


select CountryCode,Language from CountryLanguage
where CountryCode IN(select Code from Country where GovernmentForm = 'monarchy');

in_select

EXISTS をサブクエリーに利用する


select A.Code,A.name from Country A
where EXISTS (
select * from CountryLanguage B
where A.Code = B.CountryCode
AND B.Language = 'Japanese');

exsists

——————————–
Foot Note
——————————–
Subqueries in the FROM clause of a query cannot be correlated with the outer query.
FROM句のサブクエリーは外部クエリーと依存関係を設定する事が出来ない。

mysql> SELECT Name, Language
-> FROM Country AS c, (SELECT Language
-> FROM CountryLanguage
-> WHERE CountryCode = c.Code
-> ) AS tmp;

ERROR 1054 (42S22): Unknown column ‘c.Code’ in ‘where clause’
mysql>

from_and_subquery

もし上記クエリーを修正すると以下のような感じでしょうかね。


SELECT C.Name, CL.Language FROM Country AS C,
(SELECT CountryCode,Language FROM CountryLanguage) AS CL
WHERE CL.CountryCode = C.Code;

subquery_fix

参考サイト
6.4.2. サブクエリ構文
6.4.2.4. ALL とともに使用したサブクエリ
6.4.2.3. ANY、IN、SOME とともに使用したサブクエリ


MYSQLにおけるUNION,UNION ALL,LEFT JOIN,RIGHT JOINについて

UNION and UNION ALL
mysql> select * from MYSQLIMP;
mysql> select * from MYSQLIMP2;
mysql> select id,n from MYSQLIMP union select id,n from MYSQLIMP2;

union

mysql> select id,n from MYSQLIMP union all select id,n from MYSQLIMP2;

union_all

mysql> select * from MYSQLIMP union select * from MYSQLIMP2;
mysql> select * from MYSQLIMP union all select * from MYSQLIMP2;

union_confirm

列数が違うとエラーになったが、列数を同じにしたら結合は問題無く出来ます。
union_confirm_other

LEFT JOIN
mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP LEFT JOIN MYSQLIMP2 ON MYSQLIMP.id = MYSQLIMP2.id;
mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP LEFT JOIN MYSQLIMP2 USING(id);

leftjoin


SELECT
c.name AS CLIENT,
p.name AS PROJECT,
p.start AS START,
p.end AS END
FROM client AS c
LEFT JOIN project AS p
USING (id)
WHERE p.id IS NULL
ORDER BY CLIENT;

left_join_check


SELECT
c.name AS CLIENT, p.name AS PROJECT,
p.start AS START, p.end AS END
FROM client AS c
LEFT JOIN project AS p
USING (id)
WHERE p.start BETWEEN '2009-01-00' AND '2009-12-31'
ORDER BY START;

project

RIGHT JOIN
mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP RIGHT JOIN MYSQLIMP2 ON MYSQLIMP.id = MYSQLIMP2.id;

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP RIGHT JOIN MYSQLIMP2 USING(id);

right_join_on

LEFT JOIN & RIGHT JOINの結果比較

もし LEFT JOIN 内の ON か USING 部分内に右側のテーブルに一致する行がなければ、
全てのカラムが NULL に設定されている行が右側のテーブルに利用されます。
この事実は、別のテーブル内に対応する物を持たないテーブル内の行を見つける為に利用する事ができます。

RIGHT JOIN は LEFT JOIN と同じように機能します。コードがデータベース全体に移植できる状態を保つ為に、
RIGHT JOIN の代わりに LEFT JOIN を利用する事をお勧めします。

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP LEFT JOIN MYSQLIMP2 ON MYSQLIMP.id = MYSQLIMP2.id;

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP RIGHT JOIN MYSQLIMP2 ON MYSQLIMP.id = MYSQLIMP2.id;

left_join_right_join

LEFT JOIN & RIGHT JOINで同じ結果を出す

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP2 RIGHT JOIN MYSQLIMP ON MYSQLIMP.n = MYSQLIMP2.n;

mysql> select MYSQLIMP.id,MYSQLIMP.n from MYSQLIMP LEFT JOIN MYSQLIMP2 ON MYSQLIMP.n = MYSQLIMP2.n;

left_right_join

INNER JOIN
以下のINNER JOINは同じ結果を返します

SELECT Name, Language
FROM Country, CountryLanguage
WHERE Code = CountryCode
AND IsOfficial = 'T';


SELECT Name, Language
FROM Country INNER JOIN CountryLanguage
ON Code = CountryCode
WHERE IsOfficial = 'T';

inner_join

以下のQueryも同じ結果を返します

select Country.Name,City.Name,City.Population
from Country,City
where Country.Code = City.CountryCode
AND Country.IndepYear IS NULL;


select Country.Name,City.Name,City.Population
from Country INNER JOIN City
ON Country.Code = City.CountryCode
WHERE Country.IndepYear IS NULL;


select Country.Name,City.Name,City.Population
from Country CROSS JOIN City
ON Country.Code = City.CountryCode
WHERE Country.IndepYear IS NULL;

    Additional Sample

追加のINNER JOINのサンプルですが、INNER JOINを利用した方がすっきりしてます。
※ここでは、自己結合してます。


SELECT C2.Name AS 'Country',
C1.Name AS 'Other Countries',
C1.Region AS 'Continent',
C1.SurfaceArea
FROM Country as C1,Country as C2 WHERE
C1.SurfaceArea > (select SurfaceArea from Country where Name = 'Paraguay')
and C2.Name = 'Paraguay'
and C1.Continent = C2.Continent
and C1.Region = 'South America';


SELECT
c1.Name AS 'Country',
c2.Name AS 'Other Countries',
c2.Continent AS 'Continent',
c2.SurfaceArea AS 'Surface Area'
FROM Country AS c1
INNER JOIN Country AS c2
USING (Continent)
WHERE c2.SurfaceArea > c1.SurfaceArea
AND c1.Name = 'Paraguay';

inner_join_on_select

※ その他INNER JOINサンプル(この例だとID列は同じ名前なので、結合部分は、USING(ID)でもOK。

select client.Name,project.Name,project.START,project.END
from client inner join project on client.ID = project.ID
where project.NAME = 'Project2';

inner_join_two_tables

自己結合

SELECT c1.Name, c2.Name, c1.Population
FROM Country AS c1, Country AS c2
WHERE c1.Population = c2.Population
AND c1.Name != c2.Name AND c1.Population > 0;

self_join

上記テーブルは自己結合して、人口が同じ国を抽出してます。
当然自分と同じ国名を省いているのと、人口が0以上の国を選択してます。
上位4カ国を通常どおりselectすると以下のような結果になります。
mysql> select Name,Population from Country where Population in(68000,78000)
-> order by Population;

+————————–+————+
| Name | Population |
+————————–+————+
| American Samoa | 68000 |
| Antigua and Barbuda | 68000 |
| Andorra | 78000 |
| Northern Mariana Islands | 78000 |
+————————–+————+
4 rows in set (0.01 sec)

mysql>

参考サイト
12.2.7.1. JOIN 構文


用途が無いので、業務で利用した事はありませんが以下のように
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)


LIMIT 節を使用すると、SELECT ステートメントで返されるレコード数を制限することができる。
LIMIT は 1 つまたは 2 つの数値引数を取る。これらの引数は整数定数でなければならない。

引数が 1 つの場合、その値は、戻り値として返す、結果セットの冒頭からのレコード数を表す。
引数が 2 つの場合、最初の引数は戻り値として返す最初のレコードまでのオフセットを表し、
2 つ目の引数は戻り値として返す最大レコード数を表す。最初のレコードのオフセット値は 0(1 ではない)。

mysql> select * from City limit 10; #Retrieve rows 0-10;
limit0

mysql> select * from City limit 5,10; #Retrieve rows 5-10;
limit5-10

全ての行を一定のオフセットから結果セットの最後まで検索するには、
2つめのパラメータに大きい数字を利用する事ができます。
このステートメントは96番目の行から最後まで全ての行を検索します。
mysql> select * from City LIMIT 95,18446744073709551615;

用意されたステートメントには、プレースホルダを利用する事ができます。
次のステートメントは tbl テーブルから行を1つ返します。


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

set

次のステートメントは tbl テーブルから2行目から6行目を返します。

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

limit_1

参考サイト
12.2.7. SELECT 構文


以下のテーブルのようなENUM列がある列にNOT NULLを設定しPKを設定
した場合にどのくらいのデータが入るか確認。


CREATE TABLE `Country001` (
`Code` char(2) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
PRIMARY KEY (`Continent`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

mysql> CREATE TABLE `Country001` (
-> `Code` char(2) NOT NULL DEFAULT '',
-> `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
-> PRIMARY KEY (`Continent`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.01 sec)

mysql> desc Country001;
+———–+—————————————————————————————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———–+—————————————————————————————+——+—–+———+——-+
| Code | char(2) | NO | | | |
| Continent | enum(‘Asia’,’Europe’,’North America’,’Africa’,’Oceania’,’Antarctica’,’South America’) | NO | PRI | Asia | |
+———–+—————————————————————————————+——+—–+———+——-+
2 rows in set (0.01 sec)

mysql> insert into Country001 (Code,Continent) values('AAA','Asia');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('EEE','Europe');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('NNN','North America');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('NNN','Africa');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('NNN','Oceania');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('NNN','Antarctica');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('AAA','South America');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('AAA','South A');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql>

country001

下記の列には、PKがありNULLを許容していないのでENUMのデータ7件とエラー時の空文字が入るので、
データとしては、最大8行のデータが入る。(ENUM+空文字)
enum(‘Asia’,’Europe’,’North America’,’Africa’,’Oceania’,’Antarctica’,’South America’)

country001_1

8行のデータ以外はもうInsertする事が出来ません。これは、PKの設定をせず、NULL
許容していないのでUNIQUE KEYをPK代わりに利用しても同じ結果になる。
t_enum

ストレージエンジンによる結果の違い

以下のINSERTはENUMに無い値を2ついれようとしているので、
空文字に変換された値が2つありPKエラーになっています。


CREATE TABLE `T_enum` (
`Col` enum('first','second','third') NOT NULL DEFAULT 'first',
PRIMARY KEY (`Col`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

MYISAM(エラーまでインサートされます)
enum


CREATE TABLE `T_enum` (
`Col` enum('first','second','third') NOT NULL DEFAULT 'first',
PRIMARY KEY (`Col`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INNODB(トランザクション処理なので、エラーで処理がROLLBACKされてます)
enum_innodb


インデックスをテーブルに対して付与すると、容易にパフォーマンスをしてくれますが
慣れてしまうと、あまり考えずに流れ作業のように作成してしまうので、
少し気にしながら作成した方がいいかもしれません。

一般的にSELECTオペレーションはインデックスを利用すると早くなりますが(Where句やOrderby利用時)、
INSERT,DELETE,UPDATEオペレーションは、インデックスも同時に更新しなければいけないので遅くなります。
UPDATE,DELETEの処理は

    データを早く見つける事が出来るので早くなる事もありますが

データとインデックスの両方を更新しなければいけないので遅くなる場合もあります
where句、Group by, Order byを利用しない場合や列のデータの種類が少ない場合はインデックスを付けても
パフォーマンスの向上は期待できない可能性があります。

「確認用テーブル」 

CREATE TABLE `T_index` (
`i1` char(10) NOT NULL default '',
`i2` char(10) NOT NULL default '',
KEY `i1` (`i1`(3)),
KEY `i2` (`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8


mysql> CREATE TABLE `T_index` (
-> `i1` char(10) NOT NULL default '',
-> `i2` char(10) NOT NULL default '',
-> KEY `i1` (`i1`(3)),
-> KEY `i2` (`i2`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.19 sec)

mysql> desc T_index;
+——-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+———-+——+—–+———+——-+
| i1 | char(10) | NO | MUL | | |
| i2 | char(10) | NO | MUL | | |
+——-+———-+——+—–+———+——-+
2 rows in set (0.00 sec)

t_index

mysql> INSERT INTO T_index(i1,i2) values('abcdefg','abcdefg');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_index(i1,i2) values('hijklmn','hijklmn');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO T_index(i1,i2) values('opqrstu','opqrstu');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T_index(i1,i2) values('vwxyz','vwxyz');
Query OK, 1 row affected (0.00 sec)

mysql> select * from T_index;
+———+———+
| i1 | i2 |
+———+———+
| abcdefg | abcdefg |
| hijklmn | hijklmn |
| opqrstu | opqrstu |
| vwxyz | vwxyz |
+———+———+
4 rows in set (0.00 sec)

mysql>

mysql> SELECT i1 FROM T_index WHERE i1 LIKE 'abc%';
+———+
| i1 |
+———+
| abcdefg |
+———+
1 row in set (0.00 sec)

mysql> SELECT i2 FROM T_index WHERE i2 LIKE 'abc%';
+———+
| i2 |
+———+
| abcdefg |
+———+
1 row in set (0.00 sec)

mysql> explain SELECT i1 FROM T_index WHERE i1 LIKE 'abc%';
+—-+————-+———+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | T_index | range | i1 | i1 | 9 | NULL | 1 | Using where |
+—-+————-+———+——-+—————+——+———+——+——+————-+
1 row in set (0.05 sec)

mysql> explain SELECT i2 FROM T_index WHERE i2 LIKE 'abc%';
+—-+————-+———+——-+—————+——+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——-+—————+——+———+——+——+————————–+
| 1 | SIMPLE | T_index | index | i2 | i2 | 30 | NULL | 4 | Using where; Using index |
+—-+————-+———+——-+—————+——+———+——+——+————————–+
1 row in set (0.00 sec)

mysql>

同じデータとインデックスですが、インデックス列に含まれるデータ量が違う事によりプランが変わっています。
key_index

ここでは、CityテーブルのName列の最初の5文字を利用して
インデックスをCreate→Drop→Createしてます。

mysql> create index idx_City_Name on City(Name (5));
Query OK, 4081 rows affected (0.15 sec)
Records: 4081 Duplicates: 0 Warnings: 0

mysql> drop index idx_City_Name on City;
Query OK, 4081 rows affected (0.10 sec)
Records: 4081 Duplicates: 0 Warnings: 0

mysql> alter table City add index idx_City_Name(Name (5));
Query OK, 4081 rows affected (0.14 sec)
Records: 4081 Duplicates: 0 Warnings: 0

index_create

参考サイト:
12.1.2. ALTER TABLE 構文
MySQLクイック・リファレンス


関数 perror() は、システムコールやライブラリ関数の呼び出しにおいて、
最後に発生したエラーに関する説明メッセージを生成し、標準エラー出力に出力する。

perror_h

殆どのシステムエラーでは、内部テキストメッセージに加えて、
MySQLは以下のスタイルでシステムエラーコードを表示します。

message … (errno: #)
message … (Errcode: #)

システムのドキュメンテーションを確認するか、perrorユーティリティを使用することで
エラーコードの意味を割り出すことができます。

Can’t find file: ‘./mysql/host.frm’ (errno: 13)
ERROR 1064 (42000)

[root@colinux ~]# perror 13
OS error code 13: Permission denied
[root@colinux ~]#

perror

MYSQLのエラーレベルは、Error , Warning, Noteの3つに分類されていて、
SHOW WARNINGSにて確認することが出来ます。

SHOW WARNINGS は、メッセージを作成した最後のステートメントから生じたエラー、警告、そしてノート
メッセージを表示、または、もしテーブルを利用した最後のステートメントが何のメッセージも作成しなければ、
何も表示しません。関連ステートメントである SHOW ERRORS はエラーだけを表示します。

warnings

エラー警告、そしてノート メッセージの最高格納数は max_error_count システム変数によって
コントロールされています。デフォルトにより、その値は64です。格納するメッセージ数を変更したければ、
max_error_count の値を変更してください。次の例では ALTER TABLE ステートメントは3つの警告
メッセージを発生しますが、max_error_count が1に設定されている為、そのうちの1つしか格納されません。

mysql> DROP TABLE IF EXISTS no_such_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+——-+——+——————————-+
| Level | Code | Message |
+——-+——+——————————-+
| Note | 1051 | Unknown table ‘no_such_table’ |
+——-+——+——————————-+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_error_count';
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_error_count | 64 |
+—————–+——-+
1 row in set (0.01 sec)

mysql>

警告を無効にするには、max_error_count を0に設定してください。この場合、
warning_count はいくつの警告が起きたか指示しますが、どのメッセージも格納はされません。

SQL_NOTES セッション変数を0に設定して、Note レベルの警告が記録されないようにできます。

MySQLクラスタエラーコードのエラーメッセージを取得するためには、
perrorを–ndbオプションと起動してください。

perror_sql

参考サイト
7.18. perror — エラーコードの説明
4.11.2. エラー ログ
12.5.4.31. SHOW WARNINGS 構文
MySQL Server 5.0 管理監視ガイド


mysqlshowクライアントは、どのデータベース、そのテーブル、あるいはテーブルカラムのインデックスが
存在するか確認するために利用する事ができます。

データベースをリスト表示
mysqlshow -u root -p
特定のデータベース内のテーブルをリスト表示
mysqlshow STUDY -u root -p

mysqlshow

特定のデータベース内の特定のテーブルなどの表示
mysqlshow STUDY "MYSQLI%" -u root -p
mysqlshow STUDY "MYSQLI*" -u root -p

mysqlshow_2

補足(Indexの確認も可能/同時に一つのみ)

[root@colinux]$ mysqlshow --keys test innodbtable -u root -p
Enter password:
Database: test Table: innodbtable
+——-+———+———–+——+—–+———+——-+———————————+———+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+——-+———+———–+——+—–+———+——-+———————————+———+
| id | int(11) | | YES | | | | select,insert,update,references | |
+——-+———+———–+——+—–+———+——-+———————————+———+
Table has no keys
[root@colinux]$

参考サイト

7.15. mysqlshow — データベース、テーブル、カラム情報を表示


トリガは名称を持つ、テーブルに付属するデータベース オブジェクトで、
テーブルに特定イベントが発生すると有効化されます。

ストアド・プロシジャは,必要な時に呼び出すサブルーチン的な位置付けですが、
それに対してトリガーは,テーブルのデータが更新されたときに自動的に実行する。
テーブルに対して,INSERT,DELETE,UPDATEの各ステートメントが実行される際に,
指定した処理を自動実行する。

1つのテーブルには最大6つのトリガーを作成する事が出来ます。
内訳は、INSERT,UPDATE,DELETEにBEFORE,AFTERの2つずつ作成する事が出来ます。

BEFORE INSERT ON table_name
AFTER INSERT ON table_name
BEFORE UPDATE ON table_name
AFTER UPDATE ON table_name
BEFORE DELETE ON table_name
AFTER DELETE ON table_name

    トリガーを利用してTAXを自動計算してくれるテーブルを作成してみました。


CREATE TABLE `TAX` (
`id` int(11) NOT NULL DEFAULT '0',
`price` decimal(9,2) DEFAULT NULL,
`price_with_tax` decimal(9,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TRIGGER ins_sum AFTER INSERT ON TAX
FOR EACH ROW SET @sum = (0.05 * NEW.price) + NEW.price;

insert into TAX(id,price,price_with_tax) values(1,100,@sum);

エイリアス OLD とエイリアス NEW を使う事によって、問題のテーブル(トリガに添付されたテーブル)
中のカラムを参照する事ができます。OLD.col_name は既存の行の
カラムが更新または削除される前にチェックします。 
NEW.col_name挿入すべき新しい行あるいは
更新された既存の行のカラムを参照します。

OLD. カラム名 イベント実行前のカラム値
NEW. カラム名 イベント実行後のカラム値

trigger

トリガーの確認

SELECT TRIGGER_NAME, EVENT_MANIPULATION,
EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='dbname';

show triggers;

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

tax

参考サイト
18.1. CREATE TRIGGER 構文
A.5. MySQL 5.1 FAQ — Triggers

21.16. INFORMATION_SCHEMA TRIGGERS テーブル

[MySQLウォッチ]第16回 MySQL 5.0ベータ版リリース,新機能トリガーとビューを試す