Mysql 5.6 optimizer doesn't use indexes in small tables joins

Question

We have two tables - the first is relatively big (contact table) 250k rows and the second is small(user table, < 10 rows). On mysql 5.6 version I have next explain result:

EXPLAIN SELECT  
  o0_.id AS id_0,  
  o8_.first_name, 
  o8_.last_name 
FROM  
  contact o0_  
  LEFT JOIN user o8_ ON o0_.user_owner_id = o8_.id  
LIMIT  
  25 OFFSET 100

+----+-------------+-------+-------+---------------+----------------------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key                  | key_len | ref  | rows   | Extra                                              |
+----+-------------+-------+-------+---------------+----------------------+---------+------+--------+----------------------------------------------------+
|  1 | SIMPLE      | o0_   | index | NULL          | IDX_403263ED9EB185F9 | 5       | NULL | 253030 | Using index                                        |
|  1 | SIMPLE      | o8_   | ALL   | PRIMARY       | NULL                 | NULL    | NULL |      5 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+----------------------+---------+------+--------+----------------------------------------------------+

2 rows in set (0,00 sec)

When i use force index for join:

EXPLAIN SELECT  
  o0_.id AS id_0,  
  o8_.first_name, 
  o8_.last_name 
FROM  
  contact o0_  
  LEFT JOIN user o8_ force index for join(`PRIMARY`) ON o0_.user_owner_id = o8_.id  
LIMIT  
  25 OFFSET 100

or adding indexes on fields which appears in select clause (first_name, last_name) on user table:

alter table user add index(first_name, last_name);

Explain result changes to this:

    +----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key                  | key_len | ref                     | rows   | Extra       |
+----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
|  1 | SIMPLE      | o0_   | index  | NULL          | IDX_403263ED9EB185F9 | 5       | NULL                    | 253030 | Using index |
|  1 | SIMPLE      | o8_   | eq_ref | PRIMARY       | PRIMARY              | 4       | o0_.user_owner_id |      1 | NULL        |
+----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
    2 rows in set (0,00 sec)

On mysql 5.5 version I have same explain result without additional indexes:

    +----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key                  | key_len | ref                     | rows   | Extra       |
+----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
|  1 | SIMPLE      | o0_   | index  | NULL          | IDX_403263ED9EB185F9 | 5       | NULL                    | 255706 | Using index |
|  1 | SIMPLE      | o8_   | eq_ref | PRIMARY       | PRIMARY              | 4       | o0_.user_owner_id |      1 |             |
+----+-------------+-------+--------+---------------+----------------------+---------+-------------------------+--------+-------------+
2 rows in set (0.00 sec)

Why i need force use PRIMARY index or add extra indexes on mysql 5.6 version? Same behavior occurs with other selects, when join small tables.

Answer 1

If you have a table with so few rows, it may actually be faster to do a full table scan, than going to an index, locate the records and then go back to the table. If you have other fields in the user table apart from the 3 in the query, then you may consider adding a covering index, but franly, I do not think that any of this would have significant affect on the speed of the query.