I am working on a PHP/mysql program that needs its own mysql table and I want to include a sample database for testing/learning purposes.
I want use a PHP installation script to automate the creation of the mysql table and inserting the sample database.
The latest versions of mysql now set the engine type to InnoDB and I can successfully create the mysql database using PHP - which defaults to the InnoDB type.
The problem comes when I try to import the sample database (from a csv file) - out of 1800 records only 500 records are imported before PHP times out.
I have come up with a possible solution.
Create a mysql database with MyISAM type - using CREATE TABLE $table_name ...... ENGINE=MyISAM
Import the records from the csv file into the MyISAM table - using INSERT INTO $table_name .......
Finally change the database type from MyISAM to InnoDB - using ALTER TABLE $table_name ENGINE = InnoDB
This three step process works MUCH faster and completes well before the PHP script times out.
I have checked the InnoDB table and data using phpmyadmin and all appears to be OK.
Can anyone find fault with this method and if so can you offer an easy solution.
The processing would be even faster if you did not do so much work.
LOAD DATA INFILE ...
will load the entire CSV file in one step, without your having to open + read + parse + INSERT each row one by one.
If you need to manipulate any of the columns, then these steps are more general, yet still much faster than either of your methods:
CREATE TABLE tmp ... ENGINE=CSV; -- and point to your file
INSERT INTO real_table
SELECT ... the columns, suitably manipulated in SQL
FROM tmp;
No loop, no OPEN, no parsing.