List:General Discussion« Previous MessageNext Message »
From:Bryan Cantwell Date:July 16 2010 7:17pm
Subject:deletes from a VERY large table
View as plain text  
I have to delete old records from a very large table (1.6billion rows)
in a stored procedure.

CREATE TABLE mytable(
  id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  unix_time INT(11) NOT NULL DEFAULT 0,
  value DOUBLE (20, 4) NOT NULL DEFAULT 0.0000,
  UNIQUE INDEX history_1 USING BTREE (id, unix_time)
)
ENGINE = INNODB;

So I can get the unix time string I use a variable:
DECLARE UnixTime BIGINT(20);
SET UnixTime = UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 30 DAY));

So now I can do my delete query. For now I made a temp table:
CREATE TEMPORARY TABLE historyDropper(
      id BIGINT(20) NOT NULL,
      UNIQUE INDEX index1 USING BTREE (id)
    );

And load it with all the unique id I want to delete, then join that to
my huge table:

SET @sql = CONCAT('DELETE h.* FROM mytable h inner join historyDropper
hd on h.id = hd.id  WHERE unix_time < ', UnixTime, ' ');
    PREPARE s1 FROM @sql;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;

My question is, is this the most efficient way to delete data older than
a certain unix_timestamp out of s huge table?
Would it be better to loop thru each unique id and delete all the items
older? Is there a utility or command line or mysql dump and restore
method that is faster?

Thanks for the tips,
Bryancan

Thread
Why is MySQL always linked to Php?alba\.albetti15 Jul
  • Re: Why is MySQL always linked to Php?mos15 Jul
  • RE: Why is MySQL always linked to Php?Steven Staples15 Jul
  • RE: Why is MySQL always linked to Php?Daevid Vincent16 Jul
    • Re: Why is MySQL always linked to Php?Michael Dykman16 Jul
      • RE: Why is MySQL always linked to Php?Jay Blanchard16 Jul
        • RE: Why is MySQL always linked to PHP?Daevid Vincent16 Jul
          • Re: Why is MySQL always linked to PHP?Michael Dykman16 Jul
            • RE: Why is MySQL always linked to PHP?Jay Blanchard16 Jul
          • deletes from a VERY large tableBryan Cantwell16 Jul
  • Re: Why is MySQL always linked to Php?Rob Wultsch16 Jul
Re: Why is MySQL always linked to PHP?Jan Steinman17 Jul
  • Re: Why is MySQL always linked to PHP?Anirudh Sundar22 Jul
    • RE: Why is MySQL always linked to PHP?Jay Blanchard22 Jul
      • Re: Why is MySQL always linked to PHP?Anirudh Sundar23 Jul
        • Re: Why is MySQL always linked to PHP?Jan Steinman23 Jul
          • Re: Why is MySQL always linked to PHP?Anirudh Sundar23 Jul
          • RE: Why is MySQL always linked to PHP?Martin Gainty26 Jul
    • Re: Why is MySQL always linked to PHP?Jan Steinman22 Jul