Ravi Ravi - 4 years ago 100
MySQL Question

Need to speed up MySQL query

I had a query which is using to get various inputs to calculate Asset Ranking. To get the various values to calculate Asset Ranks based on user input, i am using multiple subqueries to a single table. But it is taking too much of time. Can anyone please help me to improvise this query?

SELECT AssetId,
AssetName,
Isin,

(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 12 MONTH
ORDER BY DDate ASC LIMIT 1) AS rafval,

(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 12 MONTH
ORDER BY DDate DESC LIMIT 1) AS ralval,

(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 3 MONTH
ORDER BY DDate ASC LIMIT 1) AS rbfval,

(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 3 MONTH
ORDER BY DDate DESC LIMIT 1) AS rblval,

(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 20 DAY
ORDER BY DDate ASC LIMIT 1) AS rcfval,

(SELECT DClose
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 20 DAY
ORDER BY DDate DESC LIMIT 1) AS rclval,

(SELECT STD(DClose)
FROM eod_data
WHERE Isin=a.Isin
AND DDate >= now()-interval 20 DAY
ORDER BY DDate DESC LIMIT 1) AS vstd
FROM assets a
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
AND a.IsActive=1;


In the above query, All the intervals which are mentioned in the subqueries are variables. They are coming from user input. Assets table containing 1000's of Assets And the eod_data will contain millions of records.

I created indexes also for all the Fields which are involving in where clause.

Table Structure
assets: AssetId,AssetName,Isin,IsActive |
eod_data: Isin,DClose,DDate

The above query is taking almost 11 minutes.

Thanks in advance.

Please find the Sample DB Here http://sqlfiddle.com/#!9/d0a50/3

Answer Source

here is my next try. i have test some different join. This is the fast way (1400 times faster). The STD() Column is not implemented in moment. Can you please check if the other output is correct

..and the final with STD() (i hope).

SELECT 
    a.AssetId
    , a.AssetName
    , a.Isin
    , CAST(COALESCE(ed2.DClose,0) AS DECIMAL(20,4)) AS rafval
    , CAST(COALESCE(ed3.DClose,0) AS DECIMAL(20,4)) AS ralval
    , CAST(COALESCE(ed4.DClose,0) AS DECIMAL(20,4)) AS rbfval
    , CAST(COALESCE(ed5.DClose,0) AS DECIMAL(20,4)) AS rblval
    , CAST(COALESCE(ed6.DClose,0) AS DECIMAL(20,4)) AS rcfval
    , CAST(COALESCE(ed7.DClose,0) AS DECIMAL(20,4)) AS rclval
    , COALESCE(ed.vstd,0) AS vstd
FROM (
    SELECT  
    ed.Isin
        , MIN(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_rafval
        , MAX(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_ralval
        , MIN(IF( DDate >= now()-INTERVAL  3 MONTH, EodDataId, NULL)) AS id_rbfval
        , MAX(IF( DDate >= now()-INTERVAL  3 MONTH, EodDataId, NULL)) AS id_rblval
        , MIN(IF( DDate >= now()-INTERVAL 40 DAY  , EodDataId, NULL)) AS id_rcfval
        , MAX(IF( DDate >= now()-INTERVAL 40 DAY  , EodDataId, NULL)) AS id_rclval
        , std(IF( DDate >= now()-INTERVAL 40 DAY  , NULL, DClose )) AS vstd
    FROM eod_data ed
    WHERE ed.DDate >= now()-INTERVAL 12 MONTH
    GROUP BY ed.Isin
    ORDER BY ed.EodDataId ASC
    ) ed
LEFT JOIN eod_data ed2 ON ed2.Isin = ed.Isin AND ed2.EodDataId = ed.id_rafval
LEFT JOIN eod_data ed3 ON ed3.Isin = ed.Isin AND ed3.EodDataId = ed.id_ralval
LEFT JOIN eod_data ed4 ON ed4.Isin = ed.Isin AND ed4.EodDataId = ed.id_rbfval
LEFT JOIN eod_data ed5 ON ed5.Isin = ed.Isin AND ed5.EodDataId = ed.id_rblval
LEFT JOIN eod_data ed6 ON ed6.Isin = ed.Isin AND ed6.EodDataId = ed.id_rcfval
LEFT JOIN eod_data ed7 ON ed7.Isin = ed.Isin AND ed7.EodDataId = ed.id_rclval
INNER JOIN assets a ON a.Isin = ed.Isin
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
  AND a.IsActive=1
ORDER BY a.AssetId;
SELECT 
    a.AssetId
    , a.AssetName
    , a.Isin
    , CAST(COALESCE(ed2.DClose,0) AS DECIMAL(20,4)) AS rafval
    , CAST(COALESCE(ed3.DClose,0) AS DECIMAL(20,4)) AS ralval
    , CAST(COALESCE(ed4.DClose,0) AS DECIMAL(20,4)) AS rbfval
    , CAST(COALESCE(ed5.DClose,0) AS DECIMAL(20,4)) AS rblval
    , CAST(COALESCE(ed6.DClose,0) AS DECIMAL(20,4)) AS rcfval
    , CAST(COALESCE(ed7.DClose,0) AS DECIMAL(20,4)) AS rclval
FROM (
    SELECT  
    ed.Isin
        , MIN(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_rafval
        , MAX(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_ralval
        , MIN(IF( DDate >= now()-INTERVAL  3 MONTH, EodDataId, NULL)) AS id_rbfval
        , MAX(IF( DDate >= now()-INTERVAL  3 MONTH, EodDataId, NULL)) AS id_rblval
        , MIN(IF( DDate >= now()-INTERVAL 20 DAY  , EodDataId, NULL)) AS id_rcfval
        , MAX(IF( DDate >= now()-INTERVAL 20 DAY  , EodDataId, NULL)) AS id_rclval
    FROM eod_data ed
    WHERE ed.DDate >= now()-INTERVAL 12 MONTH
    GROUP BY ed.Isin
    ORDER BY ed.DDate ASC
    ) ed
LEFT JOIN eod_data ed2 ON ed2.Isin = ed.Isin AND ed2.EodDataId = ed.id_rafval
LEFT JOIN eod_data ed3 ON ed3.Isin = ed.Isin AND ed3.EodDataId = ed.id_ralval
LEFT JOIN eod_data ed4 ON ed4.Isin = ed.Isin AND ed4.EodDataId = ed.id_rbfval
LEFT JOIN eod_data ed5 ON ed5.Isin = ed.Isin AND ed5.EodDataId = ed.id_rblval
LEFT JOIN eod_data ed6 ON ed6.Isin = ed.Isin AND ed6.EodDataId = ed.id_rcfval
LEFT JOIN eod_data ed7 ON ed7.Isin = ed.Isin AND ed7.EodDataId = ed.id_rclval
INNER JOIN assets a ON a.Isin = ed.Isin
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
  AND a.IsActive=1
ORDER BY a.AssetId;

can you pls test it again ?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download