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 anyKEY
orINDEX
. Why this is happening? it's suppose to useINDEX
. Please explain the reason.
Best Answer
Not a problem.
"BNL"
(Block Nested Loop)
is an efficient way to do aJOIN
. If effectively loads all the rows that it will need into an in-memory hash. Then rather than using theINDEX
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.