PHP + MySQL: Big CSV file import -

i'm trying speed this script importing csv file mysql. 1000 rows takes 130 seconds load it. tried use 30 000 rows , after 20 minutes timed out , loaded 8681 rows.

csv header looks (could number of columns in order):

email;param1;..;paramx test@test.com;something;..;value  

mysql create mail_queue:

create table if not exists `mail_queue` (   `mail_queue_id` int not null auto_increment,   `mailer_batch_id` int not null,   `to` varchar(100) not null,   `priority` int not null default 0,   `created` datetime not null default now(),   `mail_status_id` int not null,   primary key (`mail_queue_id`),   index `fk_mail_queue_mailer_batch1_idx` (`mailer_batch_id` asc),   index `fk_mail_queue_mail_status1_idx` (`mail_status_id` asc),   constraint `fk_mail_queue_mailer_batch1`     foreign key (`mailer_batch_id`)     references `mailer_batch` (`mailer_batch_id`)     on delete cascade     on update no action,   constraint `fk_mail_queue_mail_status1`     foreign key (`mail_status_id`)     references `mail_status` (`mail_status_id`)     on delete no action     on update no action) engine = innodb; 

mysql create mail_param:

create table if not exists `mail_param` (  `mail_param_id` int not null auto_increment,  `mail_queue_id` int not null,  `param_key` varchar(45) not null,  `param_value` varchar(45) not null,  primary key (`mail_param_id`),  index `fk_mail_param_mail_queue1_idx` (`mail_queue_id` asc),   constraint `fk_mail_param_mail_queue1`   foreign key (`mail_queue_id`)   references `mail_queue` (`mail_queue_id`)   on delete cascade   on update no action)  engine = innodb; 

code (zend framework). works well, slow:

    if (($handle = fopen($this->filepath, 'r')) !== false)     {         // db         $mailqueuetable = new application_model_dbtable_mailqueue();         $mailparamtable = new application_model_dbtable_mailparam();          // header         $header = \forceutf8\encoding::toutf8(fgetcsv($handle, 0, ';'));          while(($data = fgetcsv($handle, 0, ';')) !== false)         {             // save e-mail e-mail queue              $mailqueuerow = $mailqueuetable->createrow();             $mailqueuerow->mailer_batch_id = $mailerbatchid;             $mailqueuerow->to = $data[$this->emailcolumn];             $mailqueuerow->priority = 0;             $mailqueuerow->created = $created->tostring('yyyy-mm-dd hh:mm:ss');             $mailqueuerow->mail_status_id = 1;             $mailqueueid = $mailqueuerow->save();              // save e-mail params             foreach ($data $key => $value) {                 $mailparamrow = $mailparamtable->createrow();                 $mailparamrow->mail_queue_id = $mailqueueid;                 $mailparamrow->param_key = $header[$key];                 $mailparamrow->param_value = \forceutf8\encoding::toutf8($value);                 $mailparamrow->save();             }              unset($data);         }         fclose($handle);      } 

i experimented load data into, can't use because of mail_param table structure.

1) create temporary table (ok)

    $columns = "";     foreach ($this->header $item) {         if ($columns == "") {             $columns = "`" . $item . "` varchar(45)";         } else {             $columns .= ", `" . $item . "` varchar(45)";         }     }      $query = 'create temporary table `tmp_csv_import` (     `id` int auto_increment,     ' . $columns . '     ) engine myisam;';    

2) load data infile (ok)

    $query = "load data infile '" . $this->filepath . "'     table `tmp_csv_import`     fields terminated ';'     enclosed '\"'     lines terminated '\n'     ignore 1 rows;"; 

3) insert mail_queue (ok)

// $mailerbatchid last_insert_id() $query = "insert `mail_queue` (`mailer_batch_id`, `to`, `priority`, `created`, `mail_status_id`) select " . $mailerbatchid . ", `email`, 0, now(), 1 `tmp_csv_import`"; 

4) insert mail_param (???)

i don't know write here. need insert new row each column in table tmp_csv_import. , need mail_queue_id - foreign key table mail_param.

$query = "insert mail_param (mail_queue_id, param_key, param_value)  select ??? `tmp_csv_import`"; 

is possible in mysql? or should different way?

i found solution problem. there no need use temporary table.

1) data "mail_queue" loaded using code:

$query = "load data infile '" . $this->filepath . "' table `mail_queue` fields terminated ';' enclosed '\"' lines terminated '\n' ignore 1 rows (@dummy, @dummy, email, @dummy) set `mailer_batch_id` = " . (int) $mailerbatchid . ",     `priority` = 0,     `created` = now(),     `mail_status_id` = 1;"; 

for import 1 row, generated header

(@dummy, @dummy, email, @dummy) 

other values set set.

2) select "mail_queue_id" inserted values , insert array style:

array('to' => 'mail_queue_id'); 

3) create temporary csv file source file. structure:

mail_queue_id;key;value 

4) data loaded "mail_param" table:

$query = "load data infile " . $tmpfilepath . "     table `mail_param`     fields terminated ';'     enclosed '\"'     lines terminated '\n'     (`mail_queue_id`, `param_key`, `param_value`);"; 

5)

unlink($tmpfilepath) 

6) it's done. tried load csv 30 000 rows , faster. (< 1s).