0

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:

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.

  • Did you forget to add a criterion for u.userId = 1? – mustaccio Jul 12 at 15:01
  • @mustaccio did it required? as i'm joining the table with userId on left tabe? also the userId will not be 1, it's depend on messages tables, by which i'm joining. – Rhidoy Jul 12 at 15:08
  • Which id did you want in order by id desc? As it stands it is ambiguous. – Rick James Jul 12 at 19:59
  • Probably the CTE is overkill -- Consider replacing cte with a derived table containing the subquery. – Rick James Jul 12 at 20:01
  • @RickJames the id is from max(m.id) without it it's the query do't run. and aslo subquery work same as cte. – Rhidoy Jul 12 at 20:45
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.

  • if this is not a problem than it's ok. actually i'm just in the learning process, that's why do't have lot of data. – Rhidoy Jul 12 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.