0

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> bp2.lastPrice,bp1.lastPrice,bp2.lastPrice)),
(SELECT (((ABS(bp1.lastPrice - bp2.lastPrice))*100)/(SELECT IF (bp1.lastPrice> 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 < 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.

1
  • 1
    I can say a t2.micro is definitely way under-provisioned for the amount of data you're trying to process (and probably at the rate that's acceptable to process it, seeing that it's financial data). You'll really likely want at least a t2.large (maybe you can get away with a t2.medium). In AWS you don't actually get the performance the same way as if you built the server in-house, and probably are generally better off adding a 25-50% buffer to what you think you need when comparing to their server lists. I've worked with big (10s of billions of rows) financial data in AWS. – J.D. Apr 4 at 13:48
1

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 < 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 < 10000;
INSERT ... SELECT ... JOIN ... WHERE idRequest BETWEEN 10001 AND 20000;
....
1
1

It's overkill to use a SELECT for just an expression. And there is a simpler function:

(SELECT IF (bp1.lastPrice> bp2.lastPrice,bp1.lastPrice,bp2.lastPrice)),

-->

GREATEST(bp1.lastPrice, bp2.lastPrice)

and

SELECT  (((ABS(bp1.lastPrice - bp2.lastPrice))*100)/
         (  SELECT  IF (bp1.lastPrice > bp2.lastPrice,bp1.lastPrice,bp2.lastPrice))
        )

-->

100 * ABS(bp1.lastPrice - bp2.lastPrice) /
       GREATEST(bp1.lastPrice, bp2.lastPrice)

And add this index, with the columns in the given order:

INDEX(idExchangePair, idRequest, idExchangePlatform, lastPrice)

(Depending on what version you are using, adding the index may take a long time, but I think it will significantly speed up the query.) Recommend you remove the index on just idExchangePair at the same time,

This puzzles me:

UNIQUE KEY `idExchangePair` 
          (`idExchangePair`,`idRequest`,
           `idExchangePlatform_A`,`idExchangePlatform_B`),

The columns are NULLable; perhaps they should not be? What is an "exchange pair" if it is A and B, then the columns seem redundant?

There is no PRIMARY KEY for this table. If those 4 columns were NOT NULL, they could be the PK.

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.