MySQL 5 - Where/Order by-Optimierung

[hOk]

Hi,
es wäre nett wenn ihr mir mal auf die Sprünge helfen könntet.
Ich versuche gerade die Indizes einer Datenbank zu optimieren,
klappt auch eigentlich ganz gut, eine Frage habe ich dennoch.

Testaufbau

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.19-nt-log |
+---------------+
1 row in set (0.00 sec)

mysql> create database test_index;
Query OK, 1 row affected (0.02 sec)

mysql> use test_index
Database changed

mysql> create table t1 (
    -> c1 int unsigned not null,
    -> c2 int unsigned not null,
    -> c3 int unsigned not null
    -> ) engine = innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (c1, c2, c3) values
    -> (1, 2, 3),
    -> (4, 5, 6),
    -> (7, 8, 9),
    -> (1, 2, 3),
    -> (4, 5, 6),
    -> (7, 8, 9),
    -> (1, 2, 3);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| 1  | 2  | 3  |
| 4  | 5  | 6  |
| 7  | 8  | 9  |
| 1  | 2  | 3  |
| 4  | 5  | 6  |
| 7  | 8  | 9  |
| 1  | 2  | 3  |
+----+----+----+
7 rows in set (0.00 sec)

Ergebnis 1 - index fehlt noch

mysql> explain select * from t1 where c1=1 and c2=2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1  | SIMPLE      | t1    | ALL  |               |      |         |      | 7    | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Ergebnis 2 - mit index

mysql> alter table t1 add index i1 (c1, c2, c3);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where c1=1 and c2=2;
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
| 1  | SIMPLE      | t1    | ref  | i1            | i1   | 8       | const,const | 3    | Using index |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from t1 where c1=1 and c2=2 and c3=3;
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
| 1  | SIMPLE      | t1    | ref  | i1            | i1   | 12      | const,const,const | 3    | Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+
1 row in set (0.01 sec)

Sieht gut aus und ist schnell.

Ergebnis 3 - order by

mysql> explain select * from t1 where c1=1 and c2=2 order by c3;
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| 1  | SIMPLE      | t1    | ref  | i1            | i1   | 8       | const,const | 3    | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

oder

mysql> explain select * from t1 where c1=1 order by c2;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1  | SIMPLE      | t1    | ref  | i1            | i1   | 4       | const | 3    | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

Ist schnell - aber ich verstehe nicht warum der Server in
der Spalte: "Extra" das Flag: "Using where" setzt.
Der müsste doch alles aus dem mehrspaltigen Index sortiert rauslesen können?

nette Grüße, hOk

PS: Sowie ich die docs verstehe müsste ein Index bei dieser Form der Abfrage
verwendet werden können.
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
(Die zweite Form von Statement ist imho identisch)