lang
1explain extended select `SID`,`Last_Name`,`First_Name`,`age`,`type`,`Job` from ((select `SID`,`Last_Name`,`First_Name`,`age`,`area`,`type`,`Job` from Customer left join Teletype on Customer.SID = Teletype.ID where SID > 60) union all (select `SID`,`Last_Name`,`First_Name`,`age`,`area`,`type`,`Job` from Customer left join Teletype on Customer.SID = Teletype.ID where Job like '%r%')) as uni order by SID DESC;
結果:
+----+--------------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 14265300 | 100.00 | Using filesort |
| 2 | DERIVED | Customer | ALL | primary_idx | NULL | NULL | NULL | 908283 | 30.88 | Using where |
| 2 | DERIVED | Teletype | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 3 | UNION | Customer | ALL | NULL | NULL | NULL | NULL | 908283 | 100.00 | Using where |
| 3 | UNION | Teletype | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
6 rows in set, 1 warning (0.44 sec)
mysql> show index from Customer; +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Customer | 1 | simple_idx | 1 | Telephone | A | 46 | NULL | NULL | YES | BTREE | | |
| Customer | 1 | primary_idx | 1 | SID | A | 204 | NULL | NULL | | BTREE | | |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> show index from Teletype;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Teletype | 1 | simple_idx | 1 | telephone | A | 12 | NULL | NULL | YES | BTREE | | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
上記コマンドをたたくと、CustomerテーブルのCustomerテーブルにインデックスは張っているにもかかわらず、上記のようにフルスキャンとなって応答に30秒以上(explain extendedを取り除いたクエリで)かかってしまいます。(出力される行数は38万件程度)
indexを効かせる方法を教えてください。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2015/06/14 18:03