List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 28 2002 3:16pm
Subject:Re: Speed of Bulk INSERT or many single INSERT + Replication
with different indexes on Master/Slave
View as plain text  
At 9:57 -0500 6/28/02, mos wrote:
>At 07:02 AM 6/28/2002, you wrote:
>>Hi!
>>
>>We have a chat system and we are logging everything to a mysqlserver but
>>my other tech guy says that its ok to insert a row for every line a chatuser
>>writes but i want to save all this lines to textfiles and make a external
>>script
>>that does a extended bulk insert instead.
>>
>>I know that everytime a chatuser writes a line it can take from 0.1s to 2s
>>becuse of the many inserts we have and a bulk insert would be better
>>but i would be very happy to get some comments from users with more
>>experience.
>>
>>My colleauge's argument is that the overhead for doing multiple inserts
>>one_by_one is nothing compared with the time taken to update the index'es.
>>There is about 3million(!) records and 5 indexes.
>>
>>So the bottom question is: with the above facts, would multiple inserts
>>really provide a big gain?
>
>Don't use "Insert into ". Use "Load Data Into Table" instead. It is 
>much faster. It reads a tab delimited file (delimiters can be 
>changed).

It's true that LOAD DATA is faster than INSERT for loading many rows.
On the other hand, MySQL supports a multiple-row INSERT syntax:

INSERT INTO tbl_name (column_list) VALUES(value_list),(value_list), ...;

This form of insert can also be much faster than a bunch of individual
single-row INSERT statements.  Think of it as a middle ground between
single-row INSERT and LOAD DATA.
Thread
Speed of Bulk INSERT or many single INSERT + Replication with different indexes on Master/SlaveAnders Eliasson28 Jun
  • Re: Speed of Bulk INSERT or many single INSERT + Replication with different indexes on Master/SlaveHarald Fuchs28 Jun
  • Re: Speed of Bulk INSERT or many single INSERT + Replicationwith different indexes on Master/Slavemos28 Jun
    • Re: Speed of Bulk INSERT or many single INSERT + Replication with different indexes on Master/SlavePaul DuBois28 Jun