MySQL Forums
Forum List  »  Newbie

Join query doing a index scan
Posted by: Raghupradeep Aravindakshan
Date: February 02, 2017 07:12AM

Hello,

I have two tables whose structure are as below

CREATE TABLE `tripdetails` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`KVXMLUniqueID` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_tripdetails_KVXMLUniqueID` (`KVXMLUniqueID`)
) ENGINE=InnoDB

CREATE TABLE `messages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`KVXMLUniqueID` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_messages_KVXMLUniqueID` (`KVXMLUniqueID`)
) ENGINE=InnoDB


When explaining below query

EXPLAIN
SELECT MSG.KVXMLUniqueID
FROM messages MSG, tripdetails KV8 WHERE KV8.KVXMLUniqueID = MSG.KVXMLUniqueID


id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ---------- ------ ---------------------------------- ---------------------------------- ------- ----------------------- ------ -------- --------------------------
1 SIMPLE KV8 (NULL) index idx_kv8pttripdetails_KVXMLUniqueID idx_kv8pttripdetails_KVXMLUniqueID 1539 (NULL) 10083 100.00 Using where; Using index
1 SIMPLE MSG (NULL) ref idx_messages_KVXMLUniqueID idx_messages_KVXMLUniqueID 1539 raghu.KV8.KVXMLUniqueID 1 100.00 Using index

Why an index scan is done for tripdetails and ref column is null

With Regards
Raghupradeep

Options: ReplyQuote


Subject
Written By
Posted
Join query doing a index scan
February 02, 2017 07:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.