Hi,
I capture data from an none db source using perl and then every night I
load the mysql table with the new data.
Can someone explain to me the best way to do that? It takes 30 minutes
or more to load the data, so I want to store it in a temporary table
and then load it into the permanent table without losing the
characteristics of the DB.
Jim
Jim wrote:
> Hi,
>
> I capture data from an none db source using perl and then every night I
> load the mysql table with the new data.
>
> Can someone explain to me the best way to do that? It takes 30 minutes
> or more to load the data, so I want to store it in a temporary table
> and then load it into the permanent table without losing the
> characteristics of the DB.
>
> Jim
>
So, is there a problem with loading the data into the primary table directly?
Are you just reading and storing the data "as-is" or are you "editing" the data
in some way (ignoring values, changing values based on some criteria etc...)
because once you move the data into the/a tmp table, you still need to move it
into the real table. Now your processing takes an hour - not 30 minutes.
To create a temporary table (from the docs)
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(] LIKE old_tbl_name [)];
Now you have a temporary table you can load the data into using your tool of
choice.
so your "job" looks something like this:
create temporary table
load today's data
insert into realtable select * from tmptable;
(or is there some processing that must occur before storing it in the real table?)
drop temporary table;
If your datafile is relatively clean, then look at the LOAD FILE command in
Mysql - it can be very fast
At this point you may need to start asking yourself - is this the right database
for the job at hand? Based on licensing fees for using MySQL in a corporate,
for-profit, non-GPL setting (ie in-house applications not available to say, your
competitor), it is almost more cost-effective to buy Oracle (depending on your
discounts with Oracle :) )
http://www.mysql.com/company/legal/licensing/commercial-lice nse.html
--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)