Why does explain say that it uses type ALL for contests table though contest table has chid as primary key?

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)

My guess, because there are very few rows.

The query optimizer will probably make different decisions when you have many thousands of rows.

My (late) advice is to add a key on contests table, it has to include the column used for join (chid) and also the columns you want from that query.

This will cause mysql to actually use the index to get the data instead of scanning the whole table (type="index", extra="use index" in explain result).

Since chid is in the resultset and also in the join condition, in your case it will look like this:

alter table contests add key `chid_diffLevel` (chid, diff_level);

I hope it works for you. I've tried it on one of my databases and I know it works for me.