PHP brzi od MySQL-a u izvlacenju lookup vrednosti?!?!?

[zextra]

Apropo indeksa

Kod mene je isti slucaj kao i kod ivanhoe da se index na polju category_id ne koristi, ali obratite paznju sta se desava u sledecim situacijama.

mysql> create index test on master (category_id,master_id);
Query OK, 40000 rows affected (0.31 sec)
Records: 40000  Duplicates: 0  Warnings: 0

mysql> explain SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
|  1 | SIMPLE      | m     | index  | NULL          | test    | 9       | NULL               | 40000 | Using index |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | news.m.category_id |     1 |             |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
2 rows in set (0.01 sec)

mysql> SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;

................results bla bla.......
40000 rows in set (0.15 sec)

mysql> create index test on master (master_id,category_id);
Query OK, 40000 rows affected (0.29 sec)
Records: 40000  Duplicates: 0  Warnings: 0

mysql> explain SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows  | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
|  1 | SIMPLE      | m     | index  | NULL          | test    | 9       | NULL               | 40000 | Using index |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | news.m.category_id |     1 |             |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;
................results bla bla.......
40000 rows in set (0.44 sec)

mysql> drop index test on master;
Query OK, 40000 rows affected (0.22 sec)
Records: 40000  Duplicates: 0  Warnings: 0

mysql> SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;
................results bla bla.......
40000 rows in set (0.37 sec)

Da napomenem da je situacija sa rezultatima identicna bilo da je u pitanju INNER ili LEFT join. Vreme izvrsavanja je bilo identicno i posle vise izvrsavanja istog select-a.

Jasno mi je da do poboljsanja u brzini moze doci zahvaljujuci indeksiranom citanju cele tabele master. Ocigledno je da redosled igra ulogu, ali zasto je zapravo select sporiji kada je master_id na prvom mestu u indeksu (tj kada je category_id na prvom mestu)?

[edit]Ono sto je jos zanimljivije - kako to da explain ne primeti anomaliju koja ocigledno dovodi do usporenja prilikom selecta?[/edit]