mysql 动态join,PDO和MySQL：使用动态时间的子查询？通过避免while循环来解决使用Join的方法？...

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 "
Rows Affected: ".$affected_rows."
";

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.

解决方案

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