CODEWITHSUNDEEP
phpmysqlinserttimeout
ksb86
ksb86

Reputation: 151

Large mysql insert statement

I have a .txt file that looks like this:

    id,lat,lon,sequence,dist
    98372,40.535257,-111.871612,1,0.0
    98372,40.536023,-111.872235,2,0.1001
    98372,40.535629,-111.872599,3,0.1335
    98372,40.535499,-111.872559,4,0.1758
    ...etc

This file can be 863650 lines long! (34MB)
I need to insert each line as a row in a MySQL database using a MyISAM engine. (I'm assuming this would be the fastest)

I'm hosting on GoDaddy's servers so I don't have a lot of control over apache and mysql configurations.

This is what I have currently which would work except it takes about 6+ minutes and the server times out after 2 so it never finishes:

    $raw = file_get_contents('file.txt');
    $lines = explode("\r\n", $raw);
    $tablename = 'tablename';
    $tableHeaders = 'id,lat,lon,sequence,dist';
    foreach($lines as $line) {
        $line_values = explode(",", $line);
        $sqlValues = "VALUES(";
        foreach($line_values as $value) {
            $sqlValues .=  "'$value',";
        }
        $sqlValues = substr($sqlValues, 0, -1) . ")";
        $sqlSyntax = "INSERT INTO $tablename ($tableHeaders) $sqlValues";
        $mysqlcon->query($sqlSyntax);
    }


Can this be optimized better? Or is it possible to build a GIANT insert statement like: 

    "INSERT INTO table (id,lat,lon,sequence,dist)
    VALUES (98372,40.535257,-111.871612,1,0.0),
    (98372,40.536023,-111.872235,2,0.1001),
    (98372,40.535629,-111.872599,3,0.1335),
    (98372,40.535499,-111.872559,4,0.1758),
    ...etc (863647 more lines)"



Any help would be greatly appreciated!

Upvotes: 1

Views: 2725

Answers (1)

elixenide
elixenide

Reputation: 44831

Build the giant INSERT, like 

"INSERT INTO table (id,lat,lon,sequence,dist)
VALUES (98372,40.535257,-111.871612,1,0.0),
(98372,40.536023,-111.872235,2,0.1001),
(98372,40.535629,-111.872599,3,0.1335),
(98372,40.535499,-111.872559,4,0.1758),
...etc (863647 more lines)"

This will be many times faster. When I have faced similar problems, using a single huge query has often been 1,000 times faster than individual queries.

Note: due to memory limitations, you may want to "chunk" the inserts every 1,000 or 5,000 rows; 863,650 rows will likely exceed the maximum available memory on a shared server.

EDIT If you can, do LOAD DATA. This may not be available on a GoDaddy shared hosting plan for security reasons, but you can try it. You will want something like this:

LOAD DATA INFILE '/path/to/file.txt' /* change to fit your path */
INTO TABLE `table` /* Is "table" really your table's name? If so, you should pick something more descriptive... */
FIELDS TERMINATED BY ',' /* because your data is separated by commas */
/* See my note below about LINES TERMINATED BY */
IGNORE 1 LINES /* skip the headers */
(id,lat,lon,sequence,dist) /* the column names */

You may need a LINES TERMINATED BY clause. From the manual:

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

Upvotes: 5

Related Questions