SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Member stew's Avatar
    Join Date
    Apr 2002
    Location
    Durham, UK
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Deleting from VERY large table

    Hi,

    I have a huge table, almost 1GB and I have inherited the job of trimming it a bit by removing all the records that are over 2 months old.

    Easy I hear you all say! Thats what I thought....

    Code:
      DELETE FROM large_table WHERE `date` < NOW() - INTERVAL 2 MONTH
    After about an hour staring at

    Code:
      $ watch 'mysql -Bse "SHOW PROCESSLIST"'
    I gave up

    One way I thought of doing it was selecting the records I want to keep into a temporary table dropping the main table, recreating it and putting the records I want to keep back in.

    This sounds a bit flaky to me so I was wondering if any of you guys had any ideas.

    The table type is InnoDB if this helps.

    Salut

    Stew

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    does the date column have an index?

    no, your idea to copy what you need into a new table is not flakey

    you need not drop the main table, just TRUNCATE it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •