INSERT DATA INTO TABLE

--001636e0a4ece98a1b04795a6e20
Content-Type: text/plain; charset=ISO-8859-1

Hi Experts,

load data local infile and insert into are the two methods of
inserting data into a mysql table.

Out of the above two method. Is there any faster method of inserting data
into mysql tables.

Thanks,
Krishna Ch. Prajapati

--001636e0a4ece98a1b04795a6e20--

Re: INSERT DATA INTO TABLE

At 07:40 AM 11/27/2009, Krishna Chandra Prajapati wrote:
>Hi Experts,
>
>load data local infile and insert into are the two methods of
>inserting data into a mysql table.
>
>Out of the above two method. Is there any faster method of inserting data
>into mysql tables.

No. Load Data is the fastest method, unless the data is already stored in
another table.

Tip: Remember when using Load Data, it will be faster if the table is empty
and optimized (no holes) than with a table that has data in it. That's
because the non-unique indexes are built after all of the data has been
loaded. The alternative is to disable the non-unique indexes prior to using
Load Data. Unfortunately there is no way to disable the building of unique
indexes during this process unless you remove the unique index prior to
loading the data and building it later.

Tip: Using Insert will be much slower than Load Data but you can speed it
up by loading dozens of rows using one Insert statement.

Hope these tips help.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: INSERT DATA INTO TABLE

--00504502962517de5b0479684238
Content-Type: text/plain; charset=ISO-8859-1

Hi Mos,

In the below two command does 1 is faster than 2.

*
1. LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;*

2. *LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet* *LINES
TERMINATED BY '\r\n' enclosed by '"';*

Thanks,
Krishna Ch. Prajapati


On Sat, Nov 28, 2009 at 3:50 AM, mos wrote:

> At 07:40 AM 11/27/2009, Krishna Chandra Prajapati wrote:
>
>> Hi Experts,
>>
>> load data local infile and insert into are the two methods of
>> inserting data into a mysql table.
>>
>> Out of the above two method. Is there any faster method of inserting data
>> into mysql tables.
>>
>
> No. Load Data is the fastest method, unless the data is already stored in
> another table.
>
> Tip: Remember when using Load Data, it will be faster if the table is empty
> and optimized (no holes) than with a table that has data in it. That's
> because the non-unique indexes are built after all of the data has been
> loaded. The alternative is to disable the non-unique indexes prior to using
> Load Data. Unfortunately there is no way to disable the building of unique
> indexes during this process unless you remove the unique index prior to
> loading the data and building it later.
>
> Tip: Using Insert will be much slower than Load Data but you can speed it
> up by loading dozens of rows using one Insert statement.
>
> Hope these tips help.
>
> Mike
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com
>
>

--00504502962517de5b0479684238--

Re: INSERT DATA INTO TABLE

At 12:09 AM 11/28/2009, Krishna Chandra Prajapati wrote:
>Hi Mos,
>
>In the below two command does 1 is faster than 2.
>
>
>1. LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
>
>2. LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED
>BY '\r\n' enclosed by '"';
>
>
>Thanks,
>Krishna Ch. Prajapati

Krishna,
They are the same speed. #2 only gives additional information on how
the CSV file is formatted. The Load Data, in case you weren't already
aware, imports comma delimited data from a text file into a MySQL table.
The LINES TERMINATED BY '\r\n' enclosed by '" ' only tells MySQL how the
data is formatted.

Before we delve into this any further, how many rows of data are you
loading? And what table type: MyISAM or InnoDb?

Mike



>On Sat, Nov 28, 2009 at 3:50 AM, mos
><mos99@fastmail.fm> wrote:
>>At 07:40 AM 11/27/2009, Krishna Chandra Prajapati wrote:
>>>Hi Experts,
>>>
>>>load data local infile and insert into are the two methods of
>>>inserting data into a mysql table.
>>>
>>>Out of the above two method. Is there any faster method of inserting data
>>>into mysql tables.
>>
>>No. Load Data is the fastest method, unless the data is already stored in
>>another table.
>>
>>Tip: Remember when using Load Data, it will be faster if the table is
>>empty and optimized (no holes) than with a table that has data in it.
>>That's because the non-unique indexes are built after all of the data has
>>been loaded. The alternative is to disable the non-unique indexes prior
>>to using Load Data. Unfortunately there is no way to disable the building
>>of unique indexes during this process unless you remove the unique index
>>prior to loading the data and building it later.
>>
>>Tip: Using Insert will be much slower than Load Data but you can speed it
>>up by loading dozens of rows using one Insert statement.
>>
>>Hope these tips help.
>>
>>Mike
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To
>>unsubscribe:
>>http://lists.mysql.com/mysql?unsub=prajapatikc@gmail.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org