CODEWITHSUNDEEP
phpmysqlperformancemysqliprepared-statement
ZombieTfk
ZombieTfk

Reputation: 712

Slow prepared statements using php's mysqli

I have around 2000-3000 records that I need to store inside a mysql database, and I'm using php. I'm using prepared statements to do this, but when compared to using normal queries the speed drops to 1/3rd of what it was!

The php showing my use of them is below.

$connection = ...; // a mysqli object
$tile; 
$xPos;
$yPos;
$isPass; //variables used in the prep statement.

$insertPrepS = $connection->prepare("INSERT INTO `foo`.`tiles`(MapId,XPos,YPos,Passable) VALUES(?,?,?,?)");
$insertPrepS->bind_param("iiii",$mapId,$xPos,$yPos,$isPass); //$mapId was set up earlier in the code.

$map = new Map(50,50); //has w and h and an array of Tile objects inside.
$map->generateMap(); //sets up the array of Tile objects inside the map

for($y = 0; $y < $map->h; $y++){
    for($x = 0; $x < $map->w; $x++){
        $tile = $map->getTile(0,0);
        $xPos = $tile->x;
        $yPos = $tile->y;
        $isPass = $tile->passable?1:0;
        $insertPrepS->execute(); 
    }
}

The table 'tiles' has two indexes, a primary key (auto-increment, so left out here) and a foreign key 'MapId', the value of which I declared and defined earlier in the program.

Is my use of prepared statements here correct? Why is it running so much more slowly than without them and what can I do to increase the speed of Inserting records besides this?

Upvotes: 1

Views: 910

Answers (1)

sectus
sectus

Reputation: 15454

  1. Your usage of prepared statement seems legit.
  2. You could apply your insert queries within one transaction.

Simplified example

$connection->begin_transaction();
for($y = 0; $y < $map->h; $y++){
    for($x = 0; $x < $map->w; $x++){
        $tile = $map->getTile(0,0);
        $xPos = $tile->x;
        $yPos = $tile->y;
        $isPass = $tile->passable?1:0;
        $insertPrepS->execute(); 
    }
}
$connection->commit();

Do not forget to rollback on error.

P.S. MySQL transaction size - how big is too big?

Upvotes: 1

Related Questions