select insert query optimization on large volume MYSQL 8.x Im trying to run this SELECT query from table bidPrice that has over 160 million rows into a second table called spreads_v2
INSERT INTO spreads_v2(SELECT bp1.timestamp, bp1.idExchangePair, bp1.idRequest, bp1.idExchangePlatform AS idEp_a, bp2.idExchangePlatform AS idEp_b, bp1.lastPrice as lastPrice_a, bp2.lastPrice AS lastPrice_b,  
(SELECT IF (bp1.lastPrice&gt; bp2.lastPrice,bp1.lastPrice,bp2.lastPrice)),
(SELECT (((ABS(bp1.lastPrice - bp2.lastPrice))*100)/(SELECT IF (bp1.lastPrice&gt; bp2.lastPrice,bp1.lastPrice,bp2.lastPrice)))) AS spread_percentage,
ABS(bp1.lastPrice - bp2.lastPrice) AS spread_A_B
 FROM bidPrice AS bp1
JOIN bidPrice AS bp2
ON bp1.idRequest = bp2.idRequest AND bp1.idExchangePair = bp2.idExchangePair AND bp1.idExchangePlatform &lt; bp2.idExchangePlatform)

The query works fine, i tested on a test db with the right output, but the issue comes when i run this into the production db that has large amounts of data, i ran into canceled query after 12 hours of execution.
heres the SHOW CREATE table for both
CREATE TABLE `bidPrice` (
  `idBidPrice` int NOT NULL AUTO_INCREMENT,
  `idRequest` int NOT NULL,
  `idCurrencyPair` int NOT NULL,
  `idExchangePlatform` int DEFAULT NULL,
  `idExchangePair` int DEFAULT NULL,
  `bidPrice` double DEFAULT NULL,
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `wholeApiResponse` longtext,
  `askPrice` double NOT NULL,
  `lastPrice` double NOT NULL,
  `lowPrice` double NOT NULL,
  `highPrice` double NOT NULL,
  `volumePrice` double NOT NULL,
  `platformTimestamp` double DEFAULT NULL,
  PRIMARY KEY (`idBidPrice`,`idRequest`,`idCurrencyPair`),
  KEY `idExchangePlatform` (`idExchangePlatform`),
  KEY `idExchangePair` (`idExchangePair`),
  CONSTRAINT `bidPrice_ibfk_1` FOREIGN KEY (`idExchangePlatform`) REFERENCES `exchangePlatform` (`idExchangePlatform`),
  CONSTRAINT `bidPrice_ibfk_2` FOREIGN KEY (`idExchangePair`) REFERENCES `exchangePair` (`idExchangePair`)
) ENGINE=InnoDB AUTO_INCREMENT=176759983 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


CREATE TABLE `spreads_v2` (
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `idExchangePair` int DEFAULT NULL,
  `idRequest` int DEFAULT NULL,
  `idExchangePlatform_A` int DEFAULT NULL,
  `idExchangePlatform_B` int DEFAULT NULL,
  `bidPrice_A` double DEFAULT NULL,
  `bidPrice_B` double DEFAULT NULL,
  `high_value` double DEFAULT NULL,
  `spread_percentage` double DEFAULT NULL,
  `spread_A_B` double DEFAULT NULL,
  UNIQUE KEY `idExchangePair` (`idExchangePair`,`idRequest`,`idExchangePlatform_A`,`idExchangePlatform_B`),
  KEY `idExchangePlatform_A` (`idExchangePlatform_A`),
  KEY `idExchangePlatform_B` (`idExchangePlatform_B`),
  CONSTRAINT `spreads_v2_ibfk_1` FOREIGN KEY (`idExchangePair`) REFERENCES `exchangePair` (`idExchangePair`),
  CONSTRAINT `spreads_v2_ibfk_2` FOREIGN KEY (`idExchangePlatform_A`) REFERENCES `exchangePlatform` (`idExchangePlatform`),
  CONSTRAINT `spreads_v2_ibfk_3` FOREIGN KEY (`idExchangePlatform_B`) REFERENCES `exchangePlatform` (`idExchangePlatform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As an aditional comment, i have this database in a AWS RDS t2.micro, im considering on upgrading the db instance for query speed up.
The first thing about your query is the indexing on bidPrice needs to map to your join criteria:
Hence the index on bidPrice should be:
idRequest, idExchangePair, idExchangePlatform

The first two can be in either order as both are referenced with an = and idExchangePlatform is a range &lt; based search so leave this last.
Extend one of the existing indexes that begins with the same element(s).
The subquery SELECT statements in the query are ugly.
SELECT ...,
   GREATEST(bp1.lastPrice, bp2.lastPrice),
   (ABS(bp1.lastPrice - bp2.lastPrice))*100)/GREATEST(bp1.lastPrice, bp2.lastPrice)

will make it simpler (but probably won't help with speed significantly) - ref: GREATEST.
To help with such a large scan, batch this up based on the first element of the index above.
So:
INSERT ... SELECT ... JOIN ... WHERE idRequest &lt; 10000;
INSERT ... SELECT ... JOIN ... WHERE idRequest BETWEEN 10001 AND 20000;
....