Re: Moving data from one MySQL table to another

[Frank Flynn <frank@xxxxxxxxxx>]

First off - is this a solution in search of a problem?   Databases  
are excellent at indexing and caching frequently used data.  Though  
your table has millions of rows and you are only really interested in  
a few hundred you might find that performance is not noticeably any  
different than if you only had a few hundred rows total.  And if  
there is a big difference look at the indexes - really, it seems  
unbelievable but in fact it is true.

But if there is a big difference you can move the data with these two  
statements (and the data does not ever leave the DB).

INSERT INTO newTable (column1, column2, ...)
  SELECT column1, column2, ...  FROM oldTable WHERE column3 < "some  
date" /* or whatever */

DELETE FROM oldTable WHERE column3 < "some date" /* or whatever */

Be sure the two where clauses match exactly.

Good Luck,
Frank

On May 19, 2005, at 3:28 PM, php-db-digest-help@xxxxxxxxxxxxx wrote:

> From: Jeffrey <jeffreyb@xxxxxxxxxxx>
> Date: May 19, 2005 6:19:48 AM PDT
> To: php-db@xxxxxxxxxxxxx
> Subject: Moving data from one MySQL table to another
>
>
> I'm working on a web application and one of the things I am doing  
> is creating an archiving function that would move older data to  
> archive tables in order to minimise the amount of data in the   
> active tables. This so that the data that is being used more  
> frequently can be accessed faster by the users.
>
> My approach in building the archive function is:
>
> 1) SELECT query on the data
> 2) mysql_fetch_array to put the data into an array
> 3) INSERT subqueries to put the data into the archive tables.
>
> My concern is that in some cases, hundreds of rows of data would  
> need to be moved  - which could lead to awfully big arrays.  
> However, the archiving function is likely to be used infrequently -  
> not more than 1 or 2 times per week.
>
> This leads to two questions:
>
> 1) Could such a big array cause performance problems or worse?
> 2) Is there a better way?
>
> Many thanks,
>
> Jeff