Archive table rows

[Sam32]

Hello,

I'd like to decrease the size of a table by archiving a set of rows.
How can I do this in MySQL?

Also, how do you handle it in terms of programming?

Regards,
Sam32

[cpradio]

You would need to create an archive table and do a INSERT INTO archive_table SELECT columns FROM table WHERE criteria_here, then delete the rows out of table.

Then run that in a cronjob on a weekly, monthly, yearly, basis (whatever you need).

If you ever need to reference the archive table, you can then perform a UNION statement

SELECT columns FROM table WHERE criteria_here
  UNION SELECT columns FROM archive_table WHERE criteria_here

Why do you want to archive rows? Are you having performance problems? How many rows do you have in the table? Have you looked at your indexes?

[rcashell]

Have a look at partitioning or merge tables in MySQL. For example, each month if you store a set of records in a new partition or table when it comes to archiving off these it is just a matter of dropping the tables or partitions containing the to be archived data.

[Sam32]

Why do you want to archive rows? Are you having performance problems? How many rows do you have in the table? Have you looked at your indexes?

Because I want to improve performance, although it is not a big concern at the moment.
This is a table that will increase rapidly and it's extremely used for searching.
The old rows do not have much relevance and will probably slow down the queries as the size of the table increases.

[r937]

... will probably slow down the queries as the size of the table increases.

this is a problem only if you have neglected to declare the proper indexes to optimize your queries

index searches use binary lookups so performance will remain good no matter how many rows you have

[Sam32]

this is a problem only if you have neglected to declare the proper indexes to optimize your queries

index searches use binary lookups so performance will remain good no matter how many rows you have

What about searches based on non-indexed fields? My form has more than 10 search fields and sometimes only non-indexed are used.

[Lemon Juice]

this is a problem only if you have neglected to declare the proper indexes to optimize your queries

index searches use binary lookups so performance will remain good no matter how many rows you have

But in some cases this is not feasible to index all the columns especially if some of them are TEXT/BLOB type. Also, very often searches have to be done via LIKE, REGEXP, etc. and then indexes don't work. Therefore, decreasing table size helps a lot.

However, I usually tend to use a different approach - instead of archiving rows I have a separate table that only holds data for searching - a soft of search index table. Then I update the table at frequent enough intervals to be up to date. This way all kinds of searches are fairly fast because table scans don't involve unnecessary data.