Why does explain say that it uses type ALL for contests table though contest table has chid as primary key?
mysql> explain SELECT contests.chid, contests.diff_level from contests, contest_users where contests.chid=contest_users.chid;
+----+-------------+-----------------+------+---------------------+---------------------+---------+-------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------------+---------------------+---------+-------------------------------+-------+-------------+
| 1 | SIMPLE | contests | ALL | PRIMARY | NULL | NULL | NULL | 37660 | |
| 1 | SIMPLE | contest_users | ref | contest_users_idx | contest_users_idx | 4 | fbtable.contests.chid | 2 | Using index |
+----+-------------+-----------------+------+---------------------+---------------------+---------+-------------------------------+-------+-------------+
2 rows in set (0.00 sec)
mysql> desc contests;
+------------+------------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+----------+----------------+
| chid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| puzzle | char(81) | YES | | NULL | |
| solution | char(81) | YES | | NULL | |
| isComplete | tinyint(1) | YES | | 0 | |
| diff_level | char(7) | YES | | NULL | |
| time | time | YES | | 00:00:00 | |
+------------+------------------+------+-----+----------+----------------+
6 rows in set (0.00 sec)
mysql> desc contest_users;
+------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| chid | int(10) unsigned | NO | MUL | NULL | |
| uid | bigint(20) unsigned | YES | | NULL | |
| gamestate | char(81) | YES | | NULL | |
| score | int(10) unsigned | YES | | 0 | |
| no_correct | smallint(5) unsigned | YES | | 0 | |
| no_wrong | smallint(5) unsigned | YES | | 0 | |
| time | time | YES | | NULL | |
| isComplete | tinyint(1) | YES | | 0 | |
+------------+----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)