I have a big table with 1,400,000 rows, and I need to insert 3000 rows daily in it.

When I insert 3000 rows daily should

  • I first insert to a temporary table than dump that temporary table to the main table.
  • insert directly to the big table.

Which approach is fast and why?

  • 1
    I changed "14 lakhs" to "1,400,000" as Wikipedia tells me that a lakh is one hundred thousand.
    – ChrisF
    Mar 28, 2011 at 9:50
  • 2
    There's no standard answer. You have the numbers, why don't you actually try both and profile it to see which is faster?
    – sakisk
    Mar 28, 2011 at 10:36
  • Where are the 3000 rows coming from? Are there potential data integrity issues?
    – JeffO
    Mar 28, 2011 at 13:08
  • 1
    Do the 3000 inserts need to be atomic? Mar 28, 2011 at 16:07

5 Answers 5

  1. Are the 3000 rows entered evenly throughout the day? If so, that's a row inserted every 29 seconds -- so inserting data as it comes by is hardly anything to worry about.

  2. You'll only benefit creating a temporary table if that table is in MEMORY and then at the end of the day dump that to the main table.

Edit: in reference to the comment "the data 3000 rows is inserted at once , then what to do ?"

In which case just insert straight into the main table. Adding to another table then dumping that table is just an overhead and completley pointless. Look into LOAD DATA if your host supports it, or just do one big INSERT with LOW_PRIORITY (unrecommend in a high-enviroment server) or DELAYED (recommended). See: http://dev.mysql.com/doc/refman/5.5/en/insert.html

  • the data 3000 rows is inserted at once , then what to do ?
    – WebDev
    Mar 28, 2011 at 10:21
  • is delayed insert is one big insert or single insert each time ?
    – WebDev
    Mar 28, 2011 at 11:05
  • Well it can be one big insert in the format VALUES ('row1'),('row2') which will be the most efficient way of doing it, rather than INSERT, INSERT, INSERT ;-) -- you should read the link I gave.
    – Gary Green
    Mar 28, 2011 at 11:17
  • okay for using load data what is required ?
    – WebDev
    Mar 28, 2011 at 11:22
  • tinyurl.com/46pqcjw
    – Gary Green
    Mar 28, 2011 at 11:45

the first option is batching the inserts this is faster if the database's indexes support batched inserts but if they don't or if there are no indexes it provides no benefit

and if you need the table to be always up-to-date and the rows are inserted over time this would be a bad idea but if you insert 3000 rows all at once like this it wouldn't

  • what you want to say ?
    – WebDev
    Mar 28, 2011 at 10:19
  • @Rahul Mehta i guess you can't read conditionals, what i mean is: if the table has no indexes or if the indexes don't support batched inserts, batching doesn't help.
    – Dan D.
    Mar 28, 2011 at 11:28

When I stage large amounts of data I usually send them to a staging table first if they need clean up or data validation. I can't think of any files I've ever gotten that didn't need that prior to going into my database. If you don't plan to adjust the data in anyway, then you can dump straight to the table unless it is so many records you find you need to process them in batches to avoid blocking.

  • +1, I wouldn't want to do this without a layer of indirection in the middle to clean things up. Mar 28, 2011 at 19:11
  • We find it not only useful for the cleaning (which we can do in SQl Server SSIS without staging tables) but for researching data import issues because we load all files to two tables, one for the raw data and one that is cleaned. Then we can see if the problem is in their data or our clean up. Makes maintenance a lot easier. Of course we load hundreds of huge files every week (many daily). Not such a big deal if you don't need to research issues with several million record import files.
    – HLGEM
    Mar 28, 2011 at 19:17

Generally speaking, it seems more logical to do the regular insert rather than creating temporary table and then at some point in time dump to the main table.

If we're talking about the actual temporary table that MySQL creates with CREATE TEMPORARY TABLE statement, then you might run into various issues when you actually dump the data for permanent storage - since temporary tables are session-based (per connected user), then you might have issues if you experience disconnection during your session so you'll lose data, hence the integrity will be compromised.

You also didn't mention what storage engine you're using and why you're even considering creating a batch job for dumping the data to the main table. Without more insight into what's the actual problem, inserting data directly to the "main" table seems like a better choice.

  • no i am not talking about actual temporary table , and storage engine i am using is myisam , i need to create the batch for transfereing summary data to the table
    – WebDev
    Mar 28, 2011 at 10:36
  • MyISAM is fast with insert operations, 3000 inserts a day shouldn't take so much time that it'd be required for you to do a batch insert job. Gary Green has suggested LOAD DATA type of insert and MySQL documentation says it's usually 20 times faster than regular INSERT statement so that might be the best approach if you want to do a batch insert at high speed - more info at dev.mysql.com/doc/refman/5.1/en/insert-speed.html Mar 28, 2011 at 10:55

I would suggest you to go by the batch insert way of storing data in SQL. I assume that you are having a front application that is interacting with the user and sending the data to the database for storing.

In the previous answer's one suggested you to go for the batch mode of data insertion and the other suggested to use secondary table for data cleaning or structuring. I would suggest you to structure the data and then send it to database for storing. ie do the cleaning and data structuring part in your server side language and use the database for data insertion and retrieval only. More over considering having 3000 rows to insert I would have defined a for loop to loop in data. then I would have first processed 500 rows, structured and cleaned them and then put them into batch insert.

In the same iteration I would have processed next 500 rows and created another batch for insert. So basically while my previous 500 rows are being inserted in database I would have been working to insert next 500 rows. Of course these is a brief overview and requires proper work around on it to be implemented.

I see that these thread pretty old so I am little late for the party. never the less just wanted to share my opinion. Do reply with how did you solve the issue. Will be useful for us in future.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.