MySQL -Query with Union doesn't use index

Question

The following query runs instantly:

SELECT COUNT(*) FROM indicators.fileso
WHERE sha256 IN
(
    SELECT a.sha256
    FROM temp.ananifilesinfo a
    JOIN indicators.filesi fi ON fi.sha256 = a.sha256
    WHERE ((a.VTAmount > 2 AND fi.VTAVAmount <2)
        OR (a.state2 = 2 AND fi.state2 !=2))
)

But this query, takes forever:

SELECT COUNT(*) FROM indicators.fileso
WHERE sha256 IN(
      SELECT a.sha256
      FROM temp.ananifilesinfo a
      JOIN indicators.filesi fi ON fi.sha256 = a.sha256
      WHERE (a.VTAmount > 2 AND fi.VTAVAmount <2)
          UNION
      SELECT a.sha256
      FROM temp.ananifilesinfo a
      JOIN indicators.filesi fi ON fi.sha256 = a.sha256
      WHERE (a.state2 = 2 AND fi.state2 !=2)
 )

If you look at the 2 queries, they are performing the exact same logic.
In both queries the IN sub-query runs instantly!

Here is the explain of Query 1:

Here is the explain of Query 2:

Tables creation:

CREATE TABLE `filesi` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `sha256` BINARY(32) NOT NULL,
    `size` BIGINT(20) NOT NULL DEFAULT '-1' COMMENT 'meire',
    `productId` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle  - done (sock product)',
    `compsInOrg` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onLongIdle - done',
    `cloudUniqueness` TINYINT(4) NOT NULL DEFAULT '-1' COMMENT 'Cloud',
    `certificateStatusEnum` INT(11) NOT NULL DEFAULT '-1' COMMENT 'static',
    `certificateRootEnum` INT(11) NOT NULL DEFAULT '-1' COMMENT 'static',
    `certificateRootThumbPrint` BINARY(20) NULL DEFAULT NULL COMMENT 'todo: remove nullable',
    `certificateThumbPrint` BINARY(20) NULL DEFAULT NULL,
    `datein` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `lastSeen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `metaCompanyEnum` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle - done',
    `metaProductEnum` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle -done',
    `lastRA` TIMESTAMP NOT NULL DEFAULT '1990-01-01 00:00:00',
    `metaDescriptionEnum` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle - done',
    `metaLanguageId` MEDIUMINT(9) NOT NULL DEFAULT '-1' COMMENT 'meir',
    `meta64bitEnum` TINYINT(4) NOT NULL DEFAULT '-1' COMMENT 'meir',
    `trafficStatusEnum` TINYINT(4) NOT NULL DEFAULT '-1' COMMENT 'meir',
    `VTAVAmount` TINYINT(4) NOT NULL DEFAULT '-3' COMMENT 'Cloud',
    `VTRiskLevel` SMALLINT(6) NOT NULL DEFAULT '-1' COMMENT 'Cloud',
    `numOfPESections` SMALLINT(6) NOT NULL DEFAULT '-1' COMMENT 'static',
    `entropyLevel` SMALLINT(6) NOT NULL DEFAULT '-1' COMMENT 'static',
    `filenameUniquenessOrg` INT(11) NOT NULL DEFAULT '-1',
    `filenameUniquenessCloud` INT(11) NOT NULL DEFAULT '-1',
    `hadActions` TINYINT(4) NOT NULL DEFAULT '-1' COMMENT 'Cloud',
    `compilers` INT(11) NOT NULL DEFAULT '-1' COMMENT 'static',
    `packers` INT(11) NOT NULL DEFAULT '-1' COMMENT 'static',
    `importsNTDLL` BIT(1) NOT NULL DEFAULT b'0' COMMENT 'static',
    `importsWinsock` BIT(1) NOT NULL DEFAULT b'0' COMMENT 'static',
    `importsWinInet` BIT(1) NOT NULL DEFAULT b'0' COMMENT 'static',
    `numOfCryptoFunctions` INT(11) NOT NULL DEFAULT '-1' COMMENT 'static',
    `numOfKeyboardFunctions` INT(11) NOT NULL DEFAULT '-1' COMMENT 'static',
    `numOfScreenCaptureFunctions` INT(11) NOT NULL DEFAULT '-1' COMMENT 'static',
    `hasSockets` TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'onIdle - done',
    `hasAutorunOccurances` TINYINT(4) NOT NULL DEFAULT '-1',
    `hasHiddenWindowOccurances` TINYINT(4) NOT NULL DEFAULT '-1',
    `hasTempFolderOccurances` TINYINT(4) NOT NULL DEFAULT '-1',
    `hasSystem32FolderOccurances` TINYINT(4) NOT NULL DEFAULT '-1',
    `hasProgramFilesFolderOccurances` TINYINT(4) NOT NULL DEFAULT '-1',
    `hasStartsAtNightOccurances` TINYINT(4) NOT NULL DEFAULT '-1',
    `dyn_bAntiForensics` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bAutorun` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bInfoStealer` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bInjection` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bLocker` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bADS` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bMalwareSignature` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bSockets` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bICMP` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bIRC` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bSMTP` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bTOR` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bSafeboot` BIT(1) NOT NULL DEFAULT b'0',
    `dyn_bCreatesExe` BIT(1) NOT NULL DEFAULT b'0',
    `state2` TINYINT(4) NOT NULL DEFAULT '-1',
    `lastActionTime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `actionType` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `sha256` (`sha256`),
    INDEX `compsInOrg` (`compsInOrg`),
    INDEX `lastRA` (`lastRA`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=501481;


CREATE TABLE `fileso` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `hostId` INT(11) UNSIGNED NOT NULL COMMENT 'code',
    `sha256` BINARY(32) NOT NULL COMMENT 'meir',
    `fileName` VARCHAR(150) NULL DEFAULT NULL COMMENT 'meir',
    `fullPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
    `datein` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `lastSeen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `lastRA` TIMESTAMP NULL DEFAULT NULL COMMENT '?',
    `riskLevel` SMALLINT(6) NOT NULL DEFAULT '-1' COMMENT '?',
    `policiesViolated` VARCHAR(1000) NULL DEFAULT NULL,
    `NTFSOwner` VARCHAR(255) NULL DEFAULT NULL COMMENT 'meir',
    `NTFSOwnerId` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle -done',
    `p_runningUser` VARCHAR(255) NULL DEFAULT NULL COMMENT 'meir',
    `p_parentUser` VARCHAR(255) NULL DEFAULT NULL,
    `p_commandLineParams` VARCHAR(1000) NULL DEFAULT NULL COMMENT 'meir',
    `p_runningUserId` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle -done',
    `p_ParentPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
    `ar_regPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
    `ar_regKey` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
    `ar_regValue` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
    `s_serviceName` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
    `d_driverName` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
    `hostName` CHAR(20) NULL DEFAULT NULL,
    `extension` CHAR(5) NOT NULL DEFAULT '',
    `uniqueness` BINARY(32) NOT NULL DEFAULT '0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uniqueness` (`uniqueness`),
    INDEX `p_runningUserId` (`p_runningUserId`),
    INDEX `riskLevel` (`riskLevel`),
    INDEX `NTFSOwnerId` (`NTFSOwnerId`),
    INDEX `NTFSOwner` (`NTFSOwner`),
    INDEX `p_runningUser` (`p_runningUser`),
    INDEX `sha256` (`sha256`, `extension`),
    INDEX `lastSeen` (`lastSeen`),
    INDEX `sha256_fileName` (`sha256`, `fileName`),
    INDEX `sha256_fullPath` (`sha256`, `fullPath`(255)),
    INDEX `hostId_p_RunningUserId` (`hostId`, `p_runningUserId`, `lastSeen`),
    INDEX `datein` (`datein`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2569145;


CREATE TABLE `ananifilesinfo` (
    `sha256` BINARY(32) NULL DEFAULT NULL,
    `VTAmount` INT(11) NULL DEFAULT NULL,
    `CloudUniqueness` INT(11) NULL DEFAULT NULL,
    `productId` INT(11) NULL DEFAULT NULL,
    `hadActions` TINYINT(4) NULL DEFAULT NULL,
    `VTRiskLevel` SMALLINT(6) NULL DEFAULT NULL,
    `filenameUniquenessCloud` TINYINT(4) NULL DEFAULT NULL,
    `state2` SMALLINT(6) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Rick James · Accepted Answer · 2016-04-02 21:05:22Z

Don't use IN ( SELECT ... ); turn it into a JOIN for efficiency.

What probably is happening is: New optimizations for IN ( SELECT ... ) failed to account for an old implementation of UNION.

If there is no need for id on filesi and fileso, get rid of it and promote the UNIQUE key to being PRIMARY.

More

In older versions of MySQL, IN ( SELECT ... ) would evaluate the subquery every time a value needed to be tested. In newer versions, a tmp table is created with the result of that subquery, and an index is automatically generated.

In older versions of MySQL, a subquery was generated for any query involving UNION (such as your subquery). Eventually it was realized that some UNIONs could feed the output directly, without creating a tmp table meanwhile. Your UNION probably can use this optimization.

Sometimes, in the evolution of the optimizer, one improvement fails to take into account some other improvement. What I don't know is the interaction between doing a UNION inside an IN. This could be getting so complex that the optimizer decides to generate a query plan without doing a good job of optimizing. (I analyzed thousands of queries; I don't remember one like yours.)

For further discussion, please provide EXPLAIN FORMAT=JSON SELECT ... for each version of the query. Also, if possible, get the "optimizer trace". (This is somewhat complex and requires 5.6.3.)

thx for the answer, can you please clerify about the second sentense about the old implementation of UNiON. As I read an IN statement acts as a correlated subquery, but the first query runs fast, so something about that union inside an IN statement. I am trying to get some conclusions, when should I use union/ when not? when to use IN? are you sure IN is less officient then join, as I remmember I read otherwise — omri, Apr 2, 2016 at 6:45

Øystein Grøvlen · Accepted Answer · 2016-04-03 21:08:20Z

Since MySQL 5.6, MySQL may automatically convert IN-subqueries into a JOIN query. This is called semi-join transformation. By converting the subquery to a join, the MySQL optimizer may be able to process the tables in a different order than for traditional subquery execution. For your query, the amount of data that need to be accessed, will be much less if the tables of the subquery is processed first.

However, as described in the manual, semi-join transformation will not be done if subquery contains UNION.

In your case, it seems straight-forward to avoid the union so that semi-join transformation can be done. AFAICT, this query should be equivalent to yours:

SELECT COUNT(*) FROM indicators.fileso
WHERE sha256 IN(
      SELECT a.sha256
      FROM temp.ananifilesinfo a
      JOIN indicators.filesi fi ON fi.sha256 = a.sha256
      WHERE (a.VTAmount > 2 AND fi.VTAVAmount <2)
         OR (a.state2 = 2 AND fi.state2 !=2)
)

Super interesting, thx alot for the answer!
– omri
Apr 4, 2016 at 11:19 — omri, Apr 4, 2016 at 11:19

n0099 · Accepted Answer · 2023-06-07 11:46:03Z

Try just wraping the UNIONed SELECTs with another plain SELECT:

SELECT COUNT(*) FROM indicators.fileso
WHERE sha256 IN (
   SELECT * FROM (
      SELECT a.sha256
      FROM temp.ananifilesinfo a
      JOIN indicators.filesi fi ON fi.sha256 = a.sha256
      WHERE (a.VTAmount > 2 AND fi.VTAVAmount <2)
          UNION
      SELECT a.sha256
      FROM temp.ananifilesinfo a
      JOIN indicators.filesi fi ON fi.sha256 = a.sha256
      WHERE (a.state2 = 2 AND fi.state2 !=2)
   ) AS T
)

This should get rid of DEPENDENT UNION in the EXPLAIN, and boost the query much faster, see also: https://stackoverflow.com/questions/19190681/what-is-dependent-union-in-select-type-column-from-explain-command/76422959#76422959

Stack Exchange Network

MySQL -Query with Union doesn't use index

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
innodb
union
or ask your own question.

Hot Network Questions

MySQL -Query with Union doesn't use index

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlinnodbunion or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
innodb
union
or ask your own question.