Mysql Insert Large data

Question

for now im trying to optimize some codes..

What is a better way to insert a large data into table?

Consider this code is running.

$arrayOfdata = execute query (SELECT c.id FROM table1 )

getting all the data from table1 storing it to array and inserting it on the table.

 private function insertSomeData($arrayOfdata, $other_id){
      foreach($arrayOfdata as $data){
                INSERT INTO table (
                    other_id,
                    other2_id,
                    is_transfered
                ) VALUES ('.$other_id.', '.$data['id'].', 0)'
            }
    }

i know if it have 500k of data in table1 this code is very slow. so i tried something like this.. i put all in one sql query

INSERT INTO 
table (
    other_id,
    other2_id,
    is_transfered
) 
SELECT 
    "other_id", c.id, 0 
FROM table1

I read that to much large of data to insert cause the mysql to slow down or timeout. i tried this code on 500k of data in my local computer and it runs well..

is there any way that it will cause a problem if large data will be insert? other ways for faster insert that would not cause the server to use to0 much resources?

i ve seen on MSsql server they use CTE to make a batch insert..but mysql doesn't support CTE — Snippet, Sep 28 '13 at 05:10
if you switch to Innodb and use transactions, you can speed it up considerably — mvp, Sep 28 '13 at 06:00

Mithun Satheesh · Answer 1 · 2013-09-28T04:56:32.253

3

for inserting huge number of records, you should think of inserting them in batches instead of calling insert for each row.

like

INSERT INTO [Table] ([Column List]) 
       VALUES ([Value List 1])
            , ([Value List 2])
            , [...]
            , ([Value List N]);

updated to comments below

you may use a loop to generate a query with each records appended till the counter reaches the batch size. Then once the batch size is reached trigger a mysql insert and reset the counter.

Also if the data is too much and you just need to migrate it to a new table, you may think of appending the queries into a db.sql file and dump it directly via sql console like

USE newdb
SOURCE db.sql

edited Sep 28 '13 at 04:56

answered Sep 28 '13 at 04:44

Mithun Satheesh

27,240
14
77
101

can you give more examples..:) – Snippet Sep 28 '13 at 04:48
and you can also use none sql dbs like mongodb. But for some kind of data. http://www.mongodb.org/ – Sina R. Sep 28 '13 at 04:51

peterm · Answer 2 · 2013-09-28T05:07:50.437

1

You definitely don't want to fetch all your data from first table to client and then insert row by row into the target table.

I'd suggest to go with INSERT INTO ... SELECT FROM ... syntax for transferring data from one table to another. If you want to transfer data in batches you can always use LIMIT clause in SELECT with OFFSET.

Another approach would be to dump data you need in a file first with SELECT ... INTO OUTFILE and then use LOAD DATA INFILE to load it to another table. You can split your file if it's too much to load at once.

Also make sure that your target table doesn't have any indices while your're transferring your data. Once you finished with transfer create all necessary indices.

edited Sep 28 '13 at 05:07

answered Sep 28 '13 at 05:02

peterm

91,357
15
148
157

what could be the danger if i use `INSERT INTO ... SELECT FROM ...` without transferring by batch? – Snippet Sep 28 '13 at 05:07
I see no danger. It may slow the overall performance of your server. The other thing to mention it can double (at least) your logs in size. – peterm Sep 28 '13 at 05:10
i see..so it is not a good way to use this one...on the old code the server eat to much resources and i need to reduce it.. – Snippet Sep 28 '13 at 05:12
It depends on too many factors that we don't know about your server environment. For most servers today 500K rows of data (if they are not BLOBs) is nothing. Then again knowing your environment if you afraid that you put too much on your server it's anyway `INSERT ... SELECT` but with `LIMIT` clause (e.g. split by 50K or rows at once) and execute it several (10) times. – peterm Sep 28 '13 at 05:22

score 1 · Answer 3 · answered Sep 28 '13 at 05:58

In my experience the fastest way to import massive sizes of data into a MySQL table is via a file. If the data is huge, the "insert into ... select from ..." might time out due to potential memory pressure- I've seen this happen. However, dumping the data from one table into a file and then loading it subsequently into another table via the same file works smoothly and is possibly the fastest way with large datasets.

To select data into a file, see this.

To load data from a file, have a look at this.

Hope this helps.

Praveen D · Answer 4 · 2013-09-28T05:08:14.833

0

Following list of methods you can use for inserting large number of data

Try locking table before starting insertion and then unlock.
Disable MySQL indexes.
Extended insert also can help you here.

edited Sep 28 '13 at 05:08

answered Sep 28 '13 at 05:01

Praveen D

2,337
2
31
43

score 0 · Answer 5 · answered Sep 28 '13 at 05:03

Try to do in as a background job, in smaller portions. For example, limit the number of records to, let's say 100 records. Then run your script from crontab each 2 minutes. It will take much longer, but you will not stuck the server, between your iterations server will run normally. 100 records and 2 minutes are configurable parameters, you have to figure out your optimal values for them.

Md. Sahadat Hossain · Answer 6 · 2013-09-28T05:58:35.840

You can insert all of your data by a single query. this query run on mysql a single times.

just Try this.

private function insertSomeData($arrayOfdata, $other_id){
                $sql1 = "INSERT INTO table (
                                            other_id,
                                            other2_id,
                                            is_transfered
                                           ) VALUES ";  // initial query string  
               $sql2 = '';   
               foreach($arrayOfdata as $data){
                   $sql2 .= "('".$other_id."', '"..$data['id']."', '0'),"; // add value to query string
               }
              $sql2 = mb_substr($sql2, 0, -1); //remove last comma
              $sql = $sql1.$sql2; //build full query
              mysql_query($sql); //execute query. I suggest you to use mysqli_* or PDO. Because mysql_* is deprecated.  
    }

score -1 · Accepted Answer · edited May 23 '17 at 10:28

Assuming that you are using InnoDB engine (which is default in most recent MySQL versions), you should simply use transactions: wrap your insert loop into BEGIN; ... COMMIT; block.

By default, every statement is run as transaction, and server must make sure that data makes it safely to disk before continuing to next statement. If you start transaction, then do many inserts, and then commit transaction, only then server must flush all the data onto the disk. On modern hardware, this could amount only to few disk operations instead of 500k of them.

Another consideration is to use prepared statements. Server has to parse every SQL statement before executing it. This parsing does not come for free, and it is not unusual that parsing time could be more expensive than actual query execution time. Usually, this parsing is done every single time, and for your case it is done 500k times. If you use prepared statements, parsing/preparation is done only once, and cost to execute statement is only disk write (which is enhanced further if you are within active transaction, because server can batch that write by delaying it until transaction commits).

Total improvement from using these methods could be dramatic - I have seen situations when using transactions reduced total run time from 30 minutes to 20 seconds.

This answer should give some idea how to use transactions in PHP with MySQL.

Looping over queries is never a good idea. Not even with prepared statements. — Patrick Savalle, Aug 20 '14 at 10:00
Do you have any other idea how to insert many rows without a loop? My suggestion of using prepared statement together with wrapping into a transaction gives best performance, which could give 1000x speedup — mvp, Aug 20 '14 at 10:03
BTW, I agree that if OP can use `INSERT INTO ... SELECT ...`, he definitely should. But, my answer gives best performance for generic case, when data comes from external sources, not from another table in the same database. — mvp, Aug 20 '14 at 10:18

Mysql Insert Large data

7 Answers7

updated to comments below