Bulk insert

Hi, pll

I am using DBI to insert and update a lot of records on a mysql database
(more than 300 000). I am not an experienced database user, but I think
there must be something I can do so I can do all these operations on
less time than the 12 hours it is taking :-|

I've talked with a friend and he says that normally databases has one
option: bulk insert/update. I can't find anything similar on MySQL.
Another option which can work, is to turn off indexes and keys on the
tables, but I do not know how to do that programatically by perl.

Can anybody help me with this?
Thanks,
Kind regards,
Alberto

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bulk insert

On Thu, 18 Mar 2004, Alberto Manuel Brandao Simoes wrote:

> Hi, pll
>
> I am using DBI to insert and update a lot of records on a mysql database
> (more than 300 000). I am not an experienced database user, but I think
> there must be something I can do so I can do all these operations on
> less time than the 12 hours it is taking :-|
>

With a bulk load file I am able to load about 16M records in 1.5 hours.
And thatis on P2-400x4, so I am sure that you are running on a faster
system.


> I've talked with a friend and he says that normally databases has one
> option: bulk insert/update. I can't find anything similar on MySQL.

load data infile.

> Another option which can work, is to turn off indexes and keys on the
> tables, but I do not know how to do that programatically by perl.

Same you do do that from the mysql shell viz., create and drop index.


-r


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bulk insert

On Thu, 18 Mar 2004, Alberto Manuel Brandao Simoes wrote:

> Hi, pll
>
> I am using DBI to insert and update a lot of records on a mysql database
> (more than 300 000). I am not an experienced database user, but I think
> there must be something I can do so I can do all these operations on
> less time than the 12 hours it is taking :-|
>

With a bulk load file I am able to load about 16M records in 1.5 hours.
And thatis on P2-400x4, so I am sure that you are running on a faster
system.


> I've talked with a friend and he says that normally databases has one
> option: bulk insert/update. I can't find anything similar on MySQL.

load data infile.

> Another option which can work, is to turn off indexes and keys on the
> tables, but I do not know how to do that programatically by perl.

Same you do do that from the mysql shell viz., create and drop index.


-r


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org