SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast Sam32's Avatar
    Join Date
    Sep 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Archive table rows

    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

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,813
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    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?
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Enthusiast Sam32's Avatar
    Join Date
    Sep 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Sam32 View Post
    ... 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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast Sam32's Avatar
    Join Date
    Sep 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  7. #7
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    925
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •