title: Optimizing MySQL query to reduced runtime
body: <p>Below is the query that will going to run on two tables with 60+ million and 400+ million records. Only the table name will be different, otherwise query is same for both the tables.</p>
  <pre><code>SELECT * FROM 
  (
      SELECT A.CUSIP, A.ISIN, A.SEDOL, A.LocalCode, A.MIC, A.ExchgCD, A.PrimaryExchgCD, A.Currency, A.Open, A.High, A.Low, A.Close, A.Mid, A.Ask, A.Last, 
      A.Bid, A.Bidsize, A.Asksize, A.TradedVolume, A.SecID, A.PriceDate, A.MktCloseDate, A.VolFlag, A.IssuerName, A.TotalTrades, A.CloseType, A.SectyCD,
      row_number() OVER (partition by A.CUSIP order by A.MktCloseDate desc) as 'rank'
      from EDI_Price04 A 
      WHERE A.CUSIP IN (
      &quot;91879Q109&quot;, &quot;583840509&quot;, &quot;583840608&quot;, &quot;59001A102&quot;, &quot;552848103&quot;) AND (A.PrimaryExchgCD = A.ExchgCD) AND A.CloseType='CC'
  ) t WHERE t.rank &lt;= 3; 
  </code></pre>
  <p>When <code>A.CUSIP IN ()</code> condition have 10-15 values, the query complete in 2-3sec. With 400 values it took 28sec. But I want to make <code>A.CUSIP IN ()</code> take 2k-3k value at a time.
  This is my table structure.</p>
  <pre><code>CREATE TABLE `EDI_Price04` (
    `MIC` varchar(6) NOT NULL DEFAULT '',
    `LocalCode` varchar(60) NOT NULL DEFAULT '' COMMENT 'PricefileSymbol',
    `ISIN` varchar(12) DEFAULT NULL,
    `Currency` varchar(3) NOT NULL DEFAULT '',
    `PriceDate` date DEFAULT NULL,
    `Open` double DEFAULT NULL,
    `High` double DEFAULT NULL,
    `Low` double DEFAULT NULL,
    `Close` double DEFAULT NULL,
    `Mid` double DEFAULT NULL,
    `Ask` double DEFAULT NULL,
    `Last` double DEFAULT NULL,
    `Bid` double DEFAULT NULL,
    `BidSize` int(11) DEFAULT NULL,
    `AskSize` int(11) DEFAULT NULL,
    `TradedVolume` bigint(20) DEFAULT NULL,
    `SecID` int(11) NOT NULL DEFAULT '0',
    `MktCloseDate` date NOT NULL DEFAULT '0000-00-00',
    `Volflag` char(1) DEFAULT NULL,
    `IssuerName` varchar(255) DEFAULT NULL,
    `SectyCD` varchar(3) DEFAULT NULL,
    `SecurityDesc` varchar(255) DEFAULT NULL,
    `SEDOL` varchar(7) DEFAULT NULL,
    `CUSIP` varchar(9) DEFAULT NULL COMMENT 'USCode',
    `PrimaryExchgCD` varchar(6) DEFAULT NULL,
    `ExchgCD` varchar(6) NOT NULL DEFAULT '',
    `TradedValue` double DEFAULT NULL,
    `TotalTrades` int(11) DEFAULT NULL,
    `Comment` varchar(255) DEFAULT NULL,
    `Repush` tinyint(4) NOT NULL DEFAULT '0',
    `CloseType` varchar(2) NOT NULL DEFAULT '',
    PRIMARY KEY (`MIC`,`LocalCode`,`Currency`,`SecID`,`MktCloseDate`,`ExchgCD`,`Repush`,`CloseType`),
    KEY `idx_EDI_Price04_0` (`MIC`),
    KEY `idx_EDI_Price04_1` (`LocalCode`),
    KEY `idx_EDI_Price04_2` (`ISIN`),
    KEY `idx_EDI_Price04_3` (`PriceDate`),
    KEY `idx_EDI_Price04_4` (`SEDOL`),
    KEY `idx_EDI_Price04_5` (`CUSIP`),
    KEY `idx_EDI_Price04_6` (`PrimaryExchgCD`),
    KEY `idx_EDI_Price04_7` (`ExchgCD`),
    KEY `idx_EDI_Price04_8` (`CloseType`),
    KEY `idx_EDI_Price04_9` (`MktCloseDate`),
    KEY `idx_EDI_Price04_CUSIP_ExchgCD_CloseType_MktCloseDate` (`CUSIP`,`ExchgCD`,`CloseType`,`MktCloseDate`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  </code></pre>