Using index with left join (11 replies)

I am trying to perform a left join on two tables ('hymnText' and 'subjectToEntry') using indexes, but the results are not what I'm expecting. I have added indexes to the 'number' field of both tables, yet according to the explain query below, all of the rows of 'subjectToEntry' are being scanned for each row in 'hymnText'. What I think I'm looking for is a type of 'ref' and a ref of 'hymnText.number' in the 'subjectToEntry' row in the explain query. Any suggestions on what to do would be appreciated. Thanks!

- Jon

mysql> explain select hymnText.textID from hymnText left join subjectToEntry on subjectToEntry.number = hymnText.number;
+----+-------------+----------------+-------+---------------+--------+---------+------+-------+-------------+
| id | select_type | table          | type  | possible_keys | key    | key_len | ref  | rows  | Extra       |
+----+-------------+----------------+-------+---------------+--------+---------+------+-------+-------------+
|  1 | SIMPLE      | hymnText       | ALL   | NULL          | NULL   | NULL    | NULL | 27663 |             |
|  1 | SIMPLE      | subjectToEntry | index | NULL          | number | 52      | NULL | 45473 | Using index |
+----+-------------+----------------+-------+---------------+--------+---------+------+-------+-------------+
2 rows in set (0.00 sec)