Touch a table

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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.


Re: Touch a table

Jim wrote:

Quoted text here. Click to load it

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)
     [(] LIKE old_tbl_name [)];

Now you have a temporary table you can load the data into using your tool of

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
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 :) )

Michael Austin.
DBA Consultant
Donations welcomed. Http://

Site Timeline