[SOLVED] MySql PHP insert performance

[RichardRotterdam]

I was wondering if there is a big difference in performance with multiple insert queries in one and insert queries in a loop. for example

 

insert 1

$sql ="INSERT INTO images(image_name,gallery_id) VALUES 
    ('image1',1),
    ('image2',2),
    ('image3',3);";
    mysql_query($sql)or die(mysql_error()) ;

insert 2

for($i=0;$i<3;$i++){
    $sql ="INSERT INTO images(image_name,gallery_id) VALUES 
      ('image".$i."',".$i."),
      ('image".$i."',".$i."),
      ('image".$i."',".$i.");";
      mysql_query($sql)or die(mysql_error()) ;
}

 

is the second way much worse or doesnt it really which way to choose?

notice that these examples are not ment to be working and only for comparison purposes

[RichardRotterdam]

where is the edit button when you need it

but anyway insert2 has to be like this

for($i=0;$i<3;$i++){
    $sql ="INSERT INTO images(image_name,gallery_id) VALUES 
      ('image".$i."',".$i.");";
      mysql_query($sql)or die(mysql_error()) ;
}

[fenway]

First way has less for the server to parse; but then you don't get back the ID of the inserted records.  Go with "insert2" -- if you're doing bulk inserts, use LOAD DATA INFILE, or the IGNORE attribute.

[RichardRotterdam]

I currently are using the second insert ints not a huge amount of insert querries max 10 but can you explain a bit more about

use LOAD DATA INFILE, or the IGNORE

[fenway]

the former is for bulk inserts, directly from from a file, for example; the latter just allows for PK collisions; DELAYED is actually what I meant, if you don't need the UID back.

[RichardRotterdam]

Thank you fenway I'll look into that for more detail that might come in handy some day