Mysql join query taking too much time even though indexes are implemented

Question

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'

Answer 1

Try this rewrite:

SELECT  m.`keyId` AS `caseId`,
        m.`id`
    FROM  `locationMapping` AS m
    WHERE  m.`keyTable` = 'xercase'
      AND  m.govtId = 90
      AND  EXISTS (
        SELECT  *
            FROM  locationSubtype
            WHERE  m.`locationSubtypeId` = `id`
              AND  m.`locationTypeId` = `locationTypeId`
              AND  m.`govtId` = `govtId`
                  )
    GROUP BY  m.`keyId` 

In many cases EXISTS() is faster than INNER JOIN.

This 'composite' index is important:

locationMapping: INDEX(govtId, keyTable)  -- in either order

The Optimizer is smart enough to notice the transitive nature of govtId=90, so I removed one mention of it.

More

AND   `locationMapping`.`govtId` = `locationSubtype`.`govtId`)
AND ((`locationSubtype`.`govtId` = '90')
...        locationMapping.govtId = 90

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.