- I am trying to import particular columns of large CSV file which contain 1026 columns and 86400 rows to mysql table using PHP.
- The File size is 180MB.
- I am using
LOAD DATA INFILE
method.It is taking 39 seconds.I want to reduce the time to less than 2 seconds.
MySQL Table contains only 3 columns of ID,TiME,status.The code is as follows:
$servername = "localhost";
$username = "root";
$password = "signion";
$db = "In_band";
$conn = mysqli_connect($servername, $username, $password,$db);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$x = "(ID,TIME";
for ($num=1;$num<=1024;$num++){
$x = $x.",@status".$num;
}
$x = $x.")";
$sql = "CREATE TABLE IF NOT EXISTS table_7(ID INT,TIME TEXT,status TEXT)";
mysqli_query($conn, $sql);
$sql = "LOAD DATA INFILE '/var/lib/files/data86400_1024.csv' REPLACE INTO TABLE table_7 FILEDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '
' IGNORE 1 LINES ".$x." set status=@status1024";
$query = mysqli_query($conn, $sql);
mysqli_close($conn);
The complete query is like this:
LOAD DATA INFILE '/var/lib/files/data86400_1024.csv' REPLACE INTO TABLE table_7 FILEDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '
' IGNORE 1 LINES (ID,TIME,@status1,@status2,@status3,.......@status1024) set status=@status1024";
In the above code i am importing last column(status1024
) of csv file to mysql table status
column.
I have used fgetcsv
method also.But it took 1 minute nearly.
How can i optimize importing of particular columns to mysql table in less than 2 seconds?