PDO & MySQL: Subquery using dynamic time? Way to s

问题:

In trying to pull down some stock data after a certain set of criteria is triggered from a database I am getting a slow response mostly based around using dynamic data. I have forced to go into a while loop to decern calculations. I was wondering if there is a way to roll this up all into one query if possible. The following is a simplified example of the code I'm using, and maybe a possible solution.

The major problem is the query is super slow, and is only going at a rate of about 29 rows per second. (11000 rows takes roughly 6 mins to query.)

$sql_while_0 = "
SELECT 
    k.*, 
    y.exchange
FROM stocks k
JOIN symbols y
    ON k.id = y.id
WHERE   
    (
    y.exchange = 'NASDAQ CM' OR
    y.exchange = 'NASDAQ GM'
    )
    AND k.t BETWEEN ? AND ?
ORDER BY t ASC
";

$t_test_begin = 20150101;
$t_test_end = 20150110;

$stmt_while_0 = $pdo->prepare($sql_while_0);
$stmt_while_0->execute([$t_test_begin,$t_test_end]);
$affected_rows = $stmt_while_0->rowCount();
echo "<br /> Rows Affected: ".$affected_rows."<br /><br />";

while ($row_while_0 = $stmt_while_0->fetch())
{

    $t_0 = $row_while_0['t'];

    // FIND t FROM 20 DAYS AGO  
    $sql_while = "
    SELECT t
    FROM usa_stocks_daily_bar
    WHERE 
        t < ?       
    ORDER BY t DESC
    LIMIT 20
    ";  

    $stmt_while = $pdo->prepare($sql_while);
    $stmt_while->execute([$t_0]);       
    while ($row_while = $stmt_while->fetch())
    {
        $t_20 = $row_while['t'];
    }


    // SELECT close_adj from TWO days ago
    // FIND t FROM 20 DAYS AGO
    $sql_while = "
    SELECT close, close_adj
    FROM stocks
    WHERE 
        t = ?
        AND id = ?  
    LIMIT 1
    ";


    $stmt_while = $pdo->prepare($sql_while);
    $stmt_while->execute([$t_2,$id_0]);     
    while ($row_while = $stmt_while->fetch())
    {
        $close_20 = $row_while['close'];
        $close_adj_20 = $row_while['close_adj'];
    }



}

So you can see where the problem is... I using two while loops. This works, but is insanely slow. I'm sure the solution is something like this:

SELECT 
    k.*, 
    y.exchange,
        (
        SELECT 
            close AS close_20
            FROM stocks k
            WHERE 
                t = (
                    SELECT z.t
                    FROM usa_stocks_daily_bar z
                    WHERE 
                        t < k.t     
                    ORDER BY z.t DESC
                    LIMIT 1 OFFSET 19                      
                    )
                AND id = k.id
            LIMIT 1     
        )        
FROM stocks k
JOIN symbols y
    ON k.id = y.id
WHERE   
    (
    y.exchange = 'NASDAQ CM' OR
    y.exchange = 'NASDAQ GM' 
    )
    AND k.t BETWEEN 20150101 AND 20150102
ORDER BY t ASC
LIMIT 2

So you can see from the second query is what I want to accomplish. I tried this before and got an error saying that the subquery pulled more than one row (as you can see from the limit 20, to get 20 days ago). The table usa_stocks_daily_bar is simply a table with the dates listed -> 20150101,20150102,...

Edit: Fuzzy recommended the 2nd tier of code, and it is able to be entered, but freezes up in MySQL for some reason.

Is this because you cannot do two or more layers of subqueries?

Thanks for your help.

回答1:

Just quickly, you seem to have one too many "OR"

WHERE   
    (
    y.exchange = 'NASDAQ CM' OR
    y.exchange = 'NASDAQ GM' OR
    )
    AND k.t BETWEEN 20150101 AND 20150110

Should be ...

WHERE   
    (
    y.exchange = 'NASDAQ CM' OR
    y.exchange = 'NASDAQ GM'
    )
    AND k.t BETWEEN 20150101 AND 20150110