Why last table don't using index for joining?

by Rhidoy   Last Updated July 12, 2019 15:06 PM

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) id
             FROM messages m
                      JOIN messages_thread_user mtu USING (threadId)
             WHERE mtu.userId = 1
             GROUP BY mtu.threadId
             order by id desc
             limit 50 offset 0)
select m1.*, u.userName
from cte
         inner join messages m1 on cte.id = 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:

enter image description here

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.



Related Questions


Minimizing Page Fetches

Updated April 20, 2015 22:02 PM

Possible to rewrite this query as a join?

Updated July 09, 2017 21:06 PM

Optimise MySQL SELECT with LEFT JOIN subquery

Updated March 12, 2016 07:02 AM

MySQL-Query question

Updated November 02, 2018 01:06 AM