Efficient MySQL INSERT

Question

I have a PHP script and I'm inserting values into a MySQL table. This was working fine when dealing with a few thousand lines of data but as I increase the data only a part of the data is inserted into the MySQL table.

It seems to stop after only about 6000 rows of data. REally I want it to work for 40,000 lines and later it needs 160,000 line. I have to run the script several times to get more data added into the table.

I am new to working with SQL statements and I don't think the way I have set it up is efficient.

Some of my code:

for($x=0;$x<count($array_athlete); $x++){

          $checkuser=mysqli_query($link,"SELECT * FROM `Events_testing2` WHERE `location`='$location'
          AND `barcode`='$array_barcode[$x]' AND `date`='$date'");
          $rowcount=mysqli_num_rows($checkuser); //checks if barcode exists for that user in that location on that date.  Inserts data if doesn't already exist.

          if($rowcount>0){
                          }
          else{
               $queryInsertUser=mysqli_query($link, "INSERT INTO `Events_testing2` (`eventID`,`location`,`date`,`barcode`,`athlete`,`time`,`Run Points`,`Volunteer Points`,`Gender`,`Gender pos`) 
               VALUES (' ','$location','$date','$array_barcode[$x]','$array_athlete[$x]','$array_time[$x]','$array_score[$x]',' ','$array_gender[$x]','$array_gender_pos[$x]') ");

              }
   }

Any advice of how to insert more rows quickly into the database would be appreciated.
Many thanks

Tip1: `INSERT INTO xyz (a,b,c) VALUES (1,2,3),(1,4,5),(5,6,7),(5,6,7)` you can make multi valueset inserts at once. How many depends on the mysql configuration and php memory limit — JustOnUnderMillions, Apr 28 '17 at 10:20
Tip2: `Gender pos` Dont create tablenames witch spaces, use underscore! — JustOnUnderMillions, Apr 28 '17 at 10:22
Thankyou @JustOnUnderMillions I will try to set me code out using Tip1. I like the idea of making multi values set insert at once. To set out VALUES (1,2,3)(etc. etc.etc ) can I use a loop? https://eval.in/784338 Can you split between the INSERT INTO then loop { VALUES } Error messages: Sometimes get a Gateway Time Out error Tip2: Thanks — Jeanclaude, Apr 28 '17 at 10:35
Where does your original data come from? Is it a SQL statement or external file. — Nigel Ren, Apr 28 '17 at 12:21

score -1 · Answer 1 · answered Apr 28 '17 at 11:14

-1

Insert by chunk, like insert first 1000 (1 - 1000) then next 1000 (1001 - 2000). In this way, you will not encounter errors.

answered Apr 28 '17 at 11:14

Vandolph Reyes

622
6
18

score -2 · Answer 2 · answered Apr 28 '17 at 11:19

Try to set the time_limit of php beforehand

<?php
ini_set('max_execution_time', '0'); // infinite
set_time_limit(0); // infinite

/// do your stuff

See:
http://php.net/manual/en/info.configuration.php#ini.max-execution-time
http://php.net/manual/en/function.set-time-limit.php

Efficient MySQL INSERT

2 Answers2