Update using multiple JOINs in MySQL/Python

Question

The first batch of questions were deemed too similar to another post (it wasn't) and were closed... so I've made another attempt here, hopefully better worded this time.

Some background: We have a relationship here. Securities (coins) and currencies (base coins) form a pair. A pair and a broker form an asset (I treat each broker/pair as a specific asset)

I also have a foreign exchange table that stores real-time currency exchange rates.

In my transaction history table (history_price), there is a column that contains a lot of NULL (Euro volume), and I do calculations based on the volume. What I want to do is populate it, and to do this I have to (for each NULL row):

Here's how I segment the 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 Euro 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's too slow... Is there any way to optimize it and make it faster?

Answer 1

Use JOIN instead of 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