Why last table don't using index for joining?

Question

By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, the table is using FULL TABLE SCAN.

Below is that Query:

WITH cte AS (SELECT MAX(m.id) ids
             FROM messages m
                      JOIN messages_thread_user mtu USING (threadId)
             WHERE mtu.userId = 1
             GROUP BY mtu.threadId
             order by ids desc
             limit 50 offset 0)
select m1.*, u.userName
from cte
         inner join messages m1 on cte.ids = m1.id
         inner join user u on m1.userId = u.userId;

Below is Query Explain:

+----+-------------+------------+------------+--------+---------------------------------+----------------------------+---------+---------------------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                   | key                        | key_len | ref                 | rows | filtered | Extra                                              |
+----+-------------+------------+------------+--------+---------------------------------+----------------------------+---------+---------------------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                            | NULL                       | NULL    | NULL                |    8 |   100.00 | Using where                                        |
|  1 | PRIMARY     | m1         | NULL       | eq_ref | PRIMARY,messages_user_userId_fk | PRIMARY                    | 8       | cte.id              |    1 |   100.00 | NULL                                               |
|  1 | PRIMARY     | u          | NULL       | ALL    | PRIMARY                         | NULL                       | NULL    | NULL                |    3 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | mtu        | NULL       | ref    | PRIMARY,messages_thread_user_pk | messages_thread_user_pk    | 3       | const               |    2 |   100.00 | Using index; Using temporary; Using filesort       |
|  2 | DERIVED     | m          | NULL       | ref    | messages_threadId_id_index      | messages_threadId_id_index | 3       | to_let.mtu.threadId |    4 |   100.00 | Using index                                        |
+----+-------------+------------+------------+--------+---------------------------------+----------------------------+---------+---------------------+------+----------+----------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

This is Visual Explain:

This is messages table structure:

CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userId` mediumint(8) unsigned NOT NULL,
  `threadId` mediumint(8) unsigned NOT NULL,
  `status` tinyint(3) unsigned DEFAULT '0' COMMENT '0->sent, 1->deliveried, 2->seen',
  `time` datetime DEFAULT CURRENT_TIMESTAMP,
  `message` text NOT NULL,
  `data_url` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `messages_user_userId_fk` (`userId`),
  KEY `messages_threadId_id_index` (`threadId`,`id`),
  CONSTRAINT `messages_messages_thread_id_fk` FOREIGN KEY (`threadId`) REFERENCES `messages_thread` (`id`),
  CONSTRAINT `messages_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

This is messages_thread_user table structure:

CREATE TABLE `messages_thread_user` (
  `threadId` mediumint(8) unsigned NOT NULL,
  `userId` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`threadId`,`userId`),
  UNIQUE KEY `messages_thread_user_pk` (`userId`,`threadId`),
  CONSTRAINT `message_thread_user_messages_thread_id_fk` FOREIGN KEY (`threadId`) REFERENCES `messages_thread` (`id`),
  CONSTRAINT `message_thread_user_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This is user table structure:

CREATE TABLE `user` (
  `userId` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `userName` varchar(31) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `userNumber` bigint(20) unsigned NOT NULL,
  `userPassword` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `userStatus` tinyint(4) NOT NULL DEFAULT '0',
  `userRegIp` varchar(35) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`userId`),
  KEY `user_userNumber_index` (`userNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

If you see above explain visual or text you saw that user u table don't using any KEY or INDEX. Why this is happening? it's suppose to use INDEX. Please explain the reason.

Answer 1

Not a problem.

"BNL" (Block Nested Loop) is an efficient way to do a JOIN. If effectively loads all the rows that it will need into an in-memory hash. Then rather than using the INDEX or otherwise fetching stuff from the table, it will simply (and very rapidly) do a hash lookup.

If you want to discuss further, run the query with more than a tiny number of rows.