Comparing the performance of MySQL stored procedures with cursors and PHP

Other Questions MySQL
1

I have a weather database table with columns for TIMESTAMP, VELOCITY, and BEARING. Each minute, new records are added, and the TIMESTAMP column is indexed.

For calculating hourly wind averages, I use PHP as shown below:

$query = "SELECT * FROM weather_readings WHERE TIMESTAMP BETWEEN startTime AND endTime ORDER BY TIMESTAMP ASC";
$data = $database->query($query)->fetchAll(PDO::FETCH_ASSOC);
foreach($data as $record)
{
   $count++;
   $velocity = $record['VELOCITY'];
   $radians = deg2rad($record['BEARING']);
   $xComponent += $velocity * sin($radians);
   $yComponent += $velocity * cos($radians);
}
if ($count > 0)
{
   $meanVelocity = sqrt($xComponent * $xComponent + $yComponent * $yComponent) / $count;
   $meanBearing = rad2deg(atan2($xComponent, $yComponent));
   if ($meanBearing < 0) $meanBearing += 360;
}

This method is efficient and runs quickly. To expand my database knowledge, I created a stored procedure named GetAverageWind that processes the same data:

DECLARE MEANVELOCITY, MEANBEARING, vel, bear FLOAT;
DECLARE finished BOOL DEFAULT FALSE;
DECLARE dataCursor CURSOR FOR SELECT VELOCITY, BEARING FROM weather_readings WHERE TIMESTAMP >= startTime AND TIMESTAMP <= endTime;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

OPEN dataCursor;

process_loop: LOOP
    FETCH dataCursor INTO vel, bear;
    IF finished THEN
        LEAVE process_loop;
    END IF;
    
    SET @angleRad = RADIANS(bear);
    SET @sinValue = SIN(@angleRad);
    SET @cosValue = COS(@angleRad);
    
    SET @xSum = @xSum + vel * @sinValue;
    SET @ySum = @ySum + vel * @cosValue;
    SET @total = @total + 1;
    
END LOOP;

CLOSE dataCursor;

IF @total > 0 THEN
    SET MEANVELOCITY = SQRT(@xSum * @xSum + @ySum * @ySum) / @total;
    SET MEANBEARING = DEGREES(ATAN2(@xSum, @ySum));
    WHILE MEANBEARING < 0 DO SET MEANBEARING = MEANBEARING + 360; END WHILE;
END IF;

SELECT MEANVELOCITY, MEANBEARING;

While both methods yield the same results, the stored procedure is much slower. I notice that the CPU load approaches 100% when using the stored procedure, while the PHP method barely impacts performance.

Given that I need to analyze each entry for vector computations, can anyone explain the performance discrepancies and suggest ways to optimize the stored procedure?

2

The performance discrepancy you’re seeing is a known issue when contrasting cursors with in-memory processing in applications like PHP. MySQL cursors tend to be much slower due to their row-by-row processing, which incurs a significant overhead for each FETCH operation. In contrast, your PHP solution fetches all the necessary data at once, enabling faster computations in memory without such overhead. Additionally, the mathematical functions in PHP are highly optimized, while MySQL requires processing each calculation on a per-row basis. A more efficient approach for your stored procedure would be to leverage aggregate functions directly in SQL. By calculating velocity components using SUM() in a single SELECT statement, you could eliminate the cursor altogether, likely resulting in improved performance, as this method allows MySQL to optimize the calculations more effectively.

3

Cursors in MySQL are inherently inefficient because they process data row-by-row within the database engine, creating substantial overhead with each iteration. Your PHP approach fetches the entire result set in one operation and processes it in application memory, which is significantly faster for mathematical computations. The CPU spike you observe occurs because MySQL must maintain cursor state, handle row locking, and execute mathematical functions repeatedly within the database context. A better solution would be rewriting your stored procedure without cursors using aggregate functions like SUM(VELOCITY * SIN(RADIANS(BEARING))) directly in the SELECT statement. This eliminates the cursor overhead entirely and leverages MySQL’s optimized aggregate processing. From my experience with similar performance issues, set-based operations in SQL almost always outperform cursor-based approaches by orders of magnitude, especially when dealing with calculations across multiple rows.

4

cursors are basically the worst way to handle bulk calculations in mysql tbh. the reason your php version is faster is becuase it grabs everything at once then does math in memory, while cursors force mysql to process one row at a time which creates tons of overhead. try rewriting without cursors using aggregate functions instead - much more effecient approach.