0

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'
0

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.

2
  • what do you mean by "The Optimizer is smart enough to notice the transitive nature of govtId=90, so I removed one mention of it.". Kindly explain
    – Rahul
    Feb 14 '17 at 11:30
  • I added something; hope it helps.
    – Rick James
    Feb 14 '17 at 23:54

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.