Fatal error: Allowed memory size exceeded on mysql query-CodePudding

I have the following query:

$query = $conn->prepare("
        SELECT ticker, open, close, high, low, T.vol, polygon_timestamp, avg_vol, dollar_vol, start_date, start_time, end_date, end_time
        FROM (
           SELECT 
              ticker,
              open,
              close,
              high,
              low,
              polygon_timestamp,
              avg_vol,
              dollar_vol,
              start_date,
              start_time,
              date_time,
              end_date,
              end_time,
              vol,
              LAG(date_time, 2) OVER (order by date_time)  AS tmin2,
              LAG(date_time, 1) OVER (order by date_time)  AS tmin1,
              LEAD(date_time, 1) OVER (order by date_time) AS tplus1,
              LEAD(date_time, 2) OVER (order by date_time) AS tplus2
           FROM $table_name
           WHERE 
              dollar_vol > (avg_vol * 5)
              AND start_time > '10:00:00'
              AND end_time < '15:50:00'
           ) T
        WHERE TIME_TO_SEC(TIMEDIFF(T.date_time, T.tmin2)) = 120
           OR TIME_TO_SEC(TIMEDIFF(T.date_time, T.tplus2)) = -120
           OR (TIME_TO_SEC(TIMEDIFF(T.date_time, T.tmin1)) = 60 AND TIME_TO_SEC(TIMEDIFF(T.date_time, T.tplus1)) = -60)
        ORDER BY date_time;
    ");
    $query->execute();
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $clusters = $query->fetchAll();

Here is the table it is querying:

Here is the error that appears: Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in C:\wamp64\www\market-data\options\aggregates\stock_cluster_vol.php

Is there a more efficient way to write the query so that it does no exceed memory limits?

I am running this locally using the windows console and WAMPserver.

Thanks!

CodePudding user response：

It looks to me that you're getting so many rows back that you're trying to read all at once that PHP is running out of memory. Your query returns 477,510 rows. It's not a matter of how the query is written. It's that you're trying to suck up all the rows at once.

You'll need to do the query, then loop through the rows one at a time with fetch rather than doing that massive $query->fetchAll().

You could also return fewer columns in the results. If you don't need all 13 of SELECT ticker, open, close, high, low, T.vol, polygon_timestamp, avg_vol, dollar_vol, start_date, start_time, end_date, end_time, then don't SELECT them all, but ultimately it is probably a function of the number of rows you are getting back at once.