Mysqli query in a loop takes longer with each iteration. why?

[nik_jain]

 

$start = 0;

$atOnce = 100;

$database->prepare_getting_ppts() ;

do {

        $pricestimes = get_rows_from_ppt($start, $atOnce); // -> THIS IS WHERE THE TIME TAKEN GRADUALLY INCREASES

        $timer->timeDo(__LINE__) ; 

        rows_to_price_array($pricestimes);

        $timer->timeDo(__LINE__) ;

        $start += $atOnce;

    } while ($pricestimes);

 

In the above loop the time taken by the call to get_rows_from_ppt($start, $atOnce);  gradually (but slowly) increases over each iteration. When the loop starts it takes about .06 sec to execute, but after several thosand iterations the time taken by the same function is .2 seconds. If I let the script continue running, then it increases to .6 seconds.

 

Why ? Here are the other 2 functions being called above:

 

 

function prepare_getting_ppts(){

        $sql = 'select b.* from  productpricetime b  join (SELECT distinct product_id FROM `productpricetime` a order by a.product_id asc limit ?,? )ab ON b.product_id = ab.product_id order by b.product_id';

        $this->ppts_stmt = $this->db->prepare($sql) ;

 

    }

 

 

 

 

function get_rows_from_ppt($start, $end)

{

    global $database;

    $stmt = $database->ppts_stmt ;

    $stmt->bind_param("ii",  $start ,$end) ;

    $stmt -> execute();

 

    $pricestimes = $stmt -> get_result() ;

    return $pricestimes;

}

 

Edit: I forgot to mention the table is of the form :

+------------+---------------------+-------+

| product_id | insertion_timestamp | price |

+------------+---------------------+-------+

|       1123 |          1406719422 | 7323  |

|       1123 |          1407313884 | 7323  |

|       1123 |          1408284527 | 7323  |

|       1123 |          1408381296 | 7323  |

|       1124 |          1406719422 | 1831  |

|       1124 |          1407313884 | 1831  |

|       1124 |          1408284527 | 1831  |

|       1124 |          1408381296 | 1831  |

 

having a index on product_id

[Barand]

Prepare and the bind the parameters once, before the loop.

 

Inside the loop you only need to set the values for the params and execute.

 

That's the idea of prepared statements - prepare once and execute many times.

[nik_jain]

I figured it out! The problem was in this query. 

 

 

$sql = 'select b.* from  productpricetime b  join (SELECT distinct product_id FROM `productpricetime` a order by a.product_id asc limit ?,? )ab ON b.product_id = ab.product_id order by b.product_id';

Mysqli doesn't like large offsets in large tables. So the solution was to simply add a WHERE product_id > ($offset value) and remove the OFFSET

Now the part that at minimum took .06, takes only .005

 

bind the parameters once, before the loop.

Ah thanks for this, I haven't extensively used prepared statements. This was just an attempt to find the culprit.