Hi I have a mysql query
SELECT
`locationMapping`.`keyId` AS `caseId`,
`locationMapping`.`id`
FROM
`locationMapping`
INNER JOIN `locationSubtype` ON (`locationMapping`.`locationSubtypeId` = `locationSubtype`.`id`
AND `locationMapping`.`locationTypeId` = `locationSubtype`.`locationTypeId`
AND `locationMapping`.`govtId` = `locationSubtype`.`govtId`)
AND ((`locationSubtype`.`govtId` = '90')
AND (`locationMapping`.`keyTable` = 'xercase'))
WHERE
locationMapping.govtId = 90
GROUP BY `keyId`
Taking 8 seconds to execute.
Create table command for locationMapping is:
show create table locationMapping;
CREATE TABLE `locationMapping` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`govtId` int(11) NOT NULL DEFAULT '0',
`locationSubtypeId` int(11) NOT NULL DEFAULT '0',
`keyId` int(11) NOT NULL DEFAULT '0',
`keyTable` varchar(100) NOT NULL DEFAULT '',
`locationTypeId` int(11) DEFAULT '0',
`gisCode` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `locationmapping_instance` (`keyId`,`keyTable`),
KEY `locationmapping_subtype` (`locationSubtypeId`),
KEY `locationmapping_govtid` (`govtId`)
) ENGINE=InnoDB AUTO_INCREMENT=3994710 DEFAULT CHARSET=latin1
Create table command of locationSubType is
CREATE TABLE `locationSubtype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`govtId` int(11) NOT NULL DEFAULT '0',
`locationTypeId` int(11) NOT NULL DEFAULT '0',
`name` varchar(100) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
`code` varchar(30) DEFAULT NULL,
`gisCode` varchar(100) DEFAULT NULL,
`sortorder` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `locationsubtype_locationtype` (`locationTypeId`),
KEY `locationsubtype_govtid` (`govtId`),
KEY `locationsubtype_giscode` (`gisCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4319 DEFAULT CHARSET=latin1
Explain statement for query is:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'locationSubtype', 'ref', 'PRIMARY,locationsubtype_locationtype,locationsubtype_govtid', 'locationsubtype_govtid', '4', 'const', '756', 'Using index condition; Using temporary; Using filesort'
'1', 'SIMPLE', 'locationMapping', 'ref', 'locationmapping_instance,locationmapping_subtype,locationmapping_govtid', 'locationmapping_subtype','4', 'comcate_dev6x.locationSubtype.id','333', 'Using where'
Best Answer
Try this rewrite:
In many cases
EXISTS()
is faster thanINNER JOIN
.This 'composite' index is important:
The Optimizer is smart enough to notice the transitive nature of
govtId=90
, so I removed one mention of it.More
The optimizer, if given the first of those, plus either of the other two, will deduce the final one. You have provided all 3, which is OK. But it is 'overkill'.
The first line is important because it tells the optimizer how the two tables are related.