Join query doing a index scan
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
Subject
Written By
Posted
Join query doing a index scan
February 02, 2017 07:12AM
February 02, 2017 08:17AM
February 02, 2017 01:11PM
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.