improve code xml-mysql for large insert (86k entries) (PHP)

I must insert a large data to mysql from a xml every day, I do this job with cron job, but it takes about 2 hours to insert all the data to my DB,

Is there anyway to decrease that time?

Here is my code:

I use meekroDB to insert

My first code was (It is pretty simple):

for ($i = 0; $i <= count($xml->Table);$i++) {



DB::insert($PreFix."_stock", array(
  'refid' => (string)$xml->Table[$i]->refid,
  'articulo' => (string)$xml->Table[$i]->articulo,
  'modelo' => str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->modelo),
  'metadatos' => str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->metadatos),
  'estado' => (string)$xml->Table[$i]->estado,
  'reffab1' => (string)$xml->Table[$i]->reffab1,
  'reffab2' => (string)$xml->Table[$i]->reffab2,
  'refequiv' => (string)$xml->Table[$i]->refequiv,
  'nota' => str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->nota),
  'precio' => (string)$xml->Table[$i]->precio,
  'numfotos' => (string)$xml->Table[$i]->numfotos,
  'fechamod' => (string)$xml->Table[$i]->fechamod,
  'idarticulo' => (string)$xml->Table[$i]->idarticulo,
  'idversion' => (string)$xml->Table[$i]->idversion

    ));

So my question is: It is normal that long time to insert 86k rows or there is any best way?

Before i began testing with meekroDB I wrote this code, but i was getting always Timed out

for ($i = 0; $i <= count($xml->Table);$i++) {


        $VALUES[] = "( '".
        (string)$xml->Table[$i]->refid."' , '".
        (string)$xml->Table[$i]->articulo."' , '".
        str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->modelo)."' , '".
        str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->metadatos)."' , '".
        (string)$xml->Table[$i]->estado."' , '".
        (string)$xml->Table[$i]->reffab1."' , '".
        (string)$xml->Table[$i]->reffab2."' , '".
        (string)$xml->Table[$i]->refequiv."' , '".
        str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->nota)."' , '".
        (string)$xml->Table[$i]->precio."' , '".
        (string)$xml->Table[$i]->numfotos."' , '".
        (string)$xml->Table[$i]->fechamod."' , '".
        (string)$xml->Table[$i]->idarticulo."' , '".
        (string)$xml->Table[$i]->idversion."' )";

     }


    $stmt = $mysqli->prepare(
    "CREATE TABLE IF NOT EXISTS ".$PreFix."_stock(ID int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `refid` VARCHAR(10),
    `articulo` VARCHAR(200),
    `modelo` VARCHAR(16),
    `metadatos` VARCHAR(500),
    `estado` VARCHAR(100),
    `reffab1` VARCHAR(50),
    `reffab2` VARCHAR(50),
    `refequiv` VARCHAR(50),
    `nota` VARCHAR(200),
    `precio` VARCHAR(15),
    `numfotos` VARCHAR(2),
    `fechamod` VARCHAR(50),
    `idarticulo` VARCHAR(10),
    `idversion` VARCHAR(10) )"
    );
    $stmt->execute();
    $stmt->close();



$temp = "";

foreach ($VALUES as $KEY){

if (!empty($KEY)){
$temp = $temp."  , ".$KEY;}

}


    $sentencia = "
    INSERT INTO ".$PreFix."_stock
    (refid,articulo,modelo,metadatos,estado,reffab1,reffab2,refequiv,nota,precio,numfotos,fechamod,idarticulo,idversion)
     VALUES 
    ";



    if ($stmt = $mysqli->prepare($sentencia.$temp) ){
       $stmt->execute();
        $stmt->close();
    }
    else {

        printf("Errormessage: %s\n", $mysqli->error."<hr/>");
    }

Then I decided to jump to the same script over and over every 500 insert by sending the for loop index by post data, but when I set cron job to do the work it was never jumping over the script.

With meekroDB its a bit slow but I never get PHP timed out

Solution

MySQL has also a LOAD XML INFILE.
According to the provided XML, I think it needs to be something like this:

LOAD XML LOCAL INFILE 'data.xml'
INTO TABLE tablename
ROWS IDENTIFIED BY '<Table>'

If this do not work, you could try to convert it to a CSV format and use LOAD DATA INFILE instead.

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE tablename
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Just remembering that to make LOAD ... LOCAL INFILE to work you need to have your data.xml or data.csv file in the same server as MySQL and use the real path to the file.

You could also import the file to a temp table to do some processing before inserting it into the real table:

INSERT INTO tablename (col1, col2, col3)
SELECT tmp.col1, tmp.col2, replace(tmp.col3, 'foo', 'bar')
FROM tmp WHERE tmp.col1 > 0

Hope it works! :)