Search code examples
mysqlquery-optimization

UPDATE in MySQL / Python with multiple JOIN

First batch of the question was deemed too similar to another post (It wasn't) and closed... So here I am with another try, this time better worded I hope.

Some background: Here we have the relationships. A security (coin) + a currency (basecoin) form a pair. A pair and a broker form an asset (I consider each broker/pair as a specific asset)

enter image description here

I also have a forex table where I store the live currency exchange rates.

In my trade history table (history_price) I have a column with a lot of NULL (the EUR volumes) with I calculate from the Volume. All I want to do is fill it and for that I have to (for each NULL line):

Here is how I have segmented my query:

1- Find currency

SELECT `history_price`.`param_basecoin`.`Symbol` 
FROM `history_price`.`param_asset` 
INNER JOIN `param_pair` ON `history_price`.`param_asset`.`id_pair` = `history_price`.`param_pair`.`pair_id` 
INNER JOIN `history_price`.`param_basecoin` ON `history_price`.`param_pair`.`Coin2_id` = `history_price`.`param_basecoin`.`basecoin_id` 
WHERE `history_price`.`param_asset`.`Ticker` LIKE '???'

2- Find exchange rate

SELECT `Rate` 
FROM `history_price`.`param_forex` 
WHERE `Coin` LIKE '???' AND `Basecoin` LIKE 'EUR'

3- update EUR column

UPDATE `history_price` 
SET `history_price`.`eur_v` = (`history_price`.`Basecoin_v` * ???) 
WHERE `history_price`.`eur_v` IS NULL

4- insert 2 into 3

UPDATE `history_price` 
SET `history_price`.`eur_v` = (`history_price`.`Basecoin_v` * (SELECT `Rate` FROM `history_price`.`param_forex` WHERE `Coin` LIKE '???' AND `Basecoin` LIKE 'EUR')) 
WHERE `history_price`.`eur_v` IS NULL

5- insert 1 into 4

UPDATE `history_price` 
SET `history_price`.`eur_v` = (`history_price`.`Basecoin_v` * (
    SELECT `Rate` 
    FROM `history_price`.`param_forex` 
    WHERE `Coin` LIKE (
        SELECT `history_price`.`param_basecoin`.`Symbol` 
        FROM `history_price`.`param_asset` 
        INNER JOIN `param_pair` ON `history_price`.`param_asset`.`id_pair` = `history_price`.`param_pair`.`pair_id` 
        INNER JOIN `history_price`.`param_basecoin` ON `history_price`.`param_pair`.`Coin2_id` = `history_price`.`param_basecoin`.`basecoin_id` 
        WHERE `history_price`.`param_asset`.`Ticker` LIKE `history_price`.`Ticker`
        ) 
        AND `Basecoin` LIKE 'EUR'
        )
    ) 
WHERE `history_price`.`eur_v` IS NULL;

It does work, but it is so slow... Is there a way to optimise this and make it faster?

Solution

  • Use JOIN rather than a correlated subquery.

    UPDATE history_price AS hp
JOIN param_asset AS pa ON pa.Ticker = hp.Ticker
JOIN param_pair AS pp ON pp.id_pair = pa.id_pair
JOIN param_basecoin AS pb ON pb.basecoin_id = pp.Coin2_id
JOIN param_forex AS pf ON pf.Coin = pb.Symbol
SET hp.eur_v = hp.Basecoin_v * pf.Rate
WHERE pf.Basecoin = 'EUR' AND hp.eur_v IS NULL