List:General Discussion« Previous MessageNext Message »
From:mos Date:April 14 2010 2:44pm
Subject:Re: Make delete requests without impact on a database
View as plain text  
It looks like you only want to keep the current data, perhaps the current 
day's worth, and delete the old data.

I would store the data in separate MySIAM tables, each table would 
represent a date, like D20100413 and D20100414. Your program will decide 
which table to insert the data into by creating a current date variable and 
now all data gets inserted to the table named by that variable. When the 
older data is no longer needed, just drop the table. If you want to keep 
the last 7 days of data, create a merge table of the last 7 tables. When 
you drop the oldest table, redefine the merge table. You can accomplish all 
this in just milliseconds.

Mike



At 08:08 AM 4/14/2010, you wrote:
>Hi,
>
>
>
>I am using MySQL  version 4.1.12-log. All the databases on it are using
>MyISAM database engine.
>
>
>
>Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
>
>
>
>To delete the rows, I use a request like this : "DELETE QUICK FROM [table]
>WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this request
>until all the rows are delete.
>
>
>
>This works but when I run the request, I can't access to the database (make
>INSERT and SELECT requests) during I do the DELETE.
>
>
>
>How can I do a "DELETE" without impact on INSERT and SELECT requests done on
>the same time?
>
>
>
>Regards,
>
>
>
>David.
>
>

Thread
Make delete requests without impact on a databaseDavid Florella14 Apr
  • Re: Make delete requests without impact on a databaseDan Nelson14 Apr
    • RE: Make delete requests without impact on a databaseDaevid Vincent15 Apr
    • Re: Make delete requests without impact on a databasePerrin Harkins15 Apr
      • RE: Make delete requests without impact on a databaseDavid Florella15 Apr
  • Re: Make delete requests without impact on a databasemos14 Apr
    • Re: Make delete requests without impact on a databaseCarsten Pedersen14 Apr
      • Re: Make delete requests without impact on a databasemos14 Apr
        • Re: Make delete requests without impact on a databaseCarsten Pedersen14 Apr