Search code examples
mysqljoinindexingexplain

MySQL is doing a full table screen

I am trying to figure out why the following SQL has a type of ALL for the categories c table.

EXPLAIN SELECT
 t.todo_id,
 t.name todo_name,
 c.name category_name
FROM
 todos t,
 categories c
WHERE t.category_id = c.category_id

The todos table has an index on category_id and todo_id is a primary key. The category_id column in the categories table is a primary key.

The EXPLAIN lists PRIMARY as a possible key for the categories table but it isn't using it.

Thanks.

Solution

  • It's simple. Your query selects all data from your tables. If you add the WHERE statement everything be fine.