SEO

#1 (**permalink**) 02-28-2008, 06:06 AM

I'm trying to move data between 2 tables.

> INSERT INTO new_table SELECT * FROM old_table LIMIT 50000;
> DELETE FROM old_table LIMIT 50000;

This is the only process that deletes data from old_table, can I be
*sure* that the limit in these 2 queries will address the same data set?

(if I don't limit to small numbers in the LIMIT, I/O gets too high, so
I'm moving data slowly in batches)

Thanks,

--
Ian P. Christian ~ http://pookey.co.uk

#2 (**permalink**) 02-28-2008, 06:06 AM

Hi Christian,
Before delete teh data from old_table, just have a backup.
Create table new_table_bck select * from old_table limit 50000;
But i feel, instead of using limit, try to get data based on some date or
other condition, so that you are sure that same data gets insert and also
deleted from old table

regards
anandkl

On 3/30/07, Ian P. Christian <pookey@pookey.co.uk> wrote:
>
> I'm trying to move data between 2 tables.
>
> > INSERT INTO new_table SELECT * FROM old_table LIMIT 50000;
> > DELETE FROM old_table LIMIT 50000;
>
> This is the only process that deletes data from old_table, can I be
> *sure* that the limit in these 2 queries will address the same data set?
>
> (if I don't limit to small numbers in the LIMIT, I/O gets too high, so
> I'm moving data slowly in batches)
>
> Thanks,
>
> --
> Ian P. Christian ~ http://pookey.co.uk
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

#3 (**permalink**) 02-28-2008, 06:06 AM

No, you can't assure the same data will be addressed without at least including an order by. Even then you would need to make sure
that the first X records in the order would not change. For instance, if you order by entered_date DESC, then the data set would
change because any new records would get included in the LIMIT.

----- Original Message -----
From: "Ian P. Christian" <pookey@pookey.co.uk>
To: <mysql@lists.mysql.com>
Sent: Friday, March 30, 2007 8:18 AM
Subject: a 'safe' way to move data?

> I'm trying to move data between 2 tables.
>
> > INSERT INTO new_table SELECT * FROM old_table LIMIT 50000;
> > DELETE FROM old_table LIMIT 50000;
>
> This is the only process that deletes data from old_table, can I be *sure* that the limit in these 2 queries will address the same
> data set?
>
> (if I don't limit to small numbers in the LIMIT, I/O gets too high, so I'm moving data slowly in batches)
>
> Thanks,
>
> --
> Ian P. Christian ~ http://pookey.co.uk
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>

#4 (**permalink**) 02-28-2008, 06:07 AM

Brent Baisley wrote:
> No, you can't assure the same data will be addressed without at least
> including an order by. Even then you would need to make sure that the
> first X records in the order would not change. For instance, if you
> order by entered_date DESC, then the data set would change because any
> new records would get included in the LIMIT.

Will it not always use the natural order of the table in
selects/deletes, and therefore return results in the order in which they
were inserted?

--
Ian P. Christian ~ http://pookey.co.uk

#5 (**permalink**) 02-28-2008, 06:07 AM

In the last episode (Mar 30), Ian P. Christian said:
> Brent Baisley wrote:
> >No, you can't assure the same data will be addressed without at
> >least including an order by. Even then you would need to make sure
> >that the first X records in the order would not change. For
> >instance, if you order by entered_date DESC, then the data set would
> >change because any new records would get included in the LIMIT.
>
> Will it not always use the natural order of the table in
> selects/deletes, and therefore return results in the order in which
> they were inserted?

If you know that no-one else has inserted or deleted records between
your two commands, the commands will return the same records.

To be completely safe, you would want to use innodb tables, then select
only the primary key of the 50,000 records you're interested in, using
the "FOR UPDATE" keyword (to keep others from modifying those records
while you're doing the move). Then "INSERT INTO newtable SELECT * FROM
oldtable WHERE primarykey in ( your 50,000 keys )", then "DELETE FROM
oldtable WHERE primarykey in ( your 50,000 keys )", then COMMIT, which
will cause your insertions and deletions to be truly atomic.

--
Dan Nelson
dnelson@allantgroup.com

#6 (**permalink**) 02-28-2008, 06:07 AM

Dan Nelson wrote:
> To be completely safe, you would want to use innodb tables, then select
> only the primary key of the 50,000 records you're interested in, using
> the "FOR UPDATE" keyword (to keep others from modifying those records
> while you're doing the move). Then "INSERT INTO newtable SELECT * FROM
> oldtable WHERE primarykey in ( your 50,000 keys )", then "DELETE FROM
> oldtable WHERE primarykey in ( your 50,000 keys )", then COMMIT, which
> will cause your insertions and deletions to be truly atomic.

Ah of course - a far better idea.

Thanks

--
Ian P. Christian ~ http://pookey.co.uk

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode