double_join.sql

mysql> select count(*) from communities;
+----------+
| count(*) |
+----------+
|     2451 |
+----------+
1 row in set (0.00 sec)

mysql> explain SELECT `communities`.* FROM `communities` 
                     INNER JOIN `residents` on `residents`.`community_id` = communities.id 
                     INNER JOIN `users` ON `users`.`id` = `residents`.`user_id` 
                      WHERE (users.id = 50713) LIMIT 1;
+----+-------------+-------------+--------+--------------------------------------+-------------------+---------+----------------------------------------+------+-------------+
| id | select_type | table       | type   | possible_keys                        | key               | key_len | ref                                    | rows | Extra       |
+----+-------------+-------------+--------+--------------------------------------+-------------------+---------+----------------------------------------+------+-------------+
|  1 | SIMPLE      | users       | const  | PRIMARY                              | PRIMARY           | 4       | const                                  |    1 | Using index |
|  1 | SIMPLE      | residents   | ref    | by_community_email,residents_by_user | residents_by_user | 5       | const                                  |    1 | Using where |
|  1 | SIMPLE      | communities | eq_ref | PRIMARY                              | PRIMARY           | 4       | prod_2011_11_15.residents.community_id |    1 |             |
+----+-------------+-------------+--------+--------------------------------------+-------------------+---------+----------------------------------------+------+-------------+