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.
Kôd:
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]