> > > I'm trying to delete 5 million rows... Can you drop the table, then re-create it for further use? Anything besides the 5,000,000 you want to keep?
> > > > Do you have to do this often? > > Sort of. I have a process that logs to MySQL, and I want to delete old > log entries. I haven't been deleting old entries at all recently > because it will freeze up everything, though. > How long do you need to keep any of these log entries? Are you using any of the entries for anything useful, or are they just accumulating? > If I was deleting things regularly, I'd have to delete maybe a couple > hundred thousand rows every day. > > > > ...access to the other tables in the database are blocked... > > > > How do you mean "blocked"? Do you mean that you're getting an error > > message someplace, or that other operations take way too long, or > > something else? > > When I say "blocked", I mean e.g. another process tries to SELECT from > another table in the database, but it takes way too long. > Do these log entries have to be stored in the same database? > > > Any suggestions on how I can delete those rows without causing a lot > > > of downtime? > > > > Do the big operations at a time when your site isn't busy. Like at night. > > My site is always busy. People don't use it as much at night, but > there's still many people using it. > Consider using the drop method or writing these entries to a different database - maybe even a different machine altogether. This seems to be a problem amenable to separating busy databse functions from each other. If there really are some records in that log table you need to keep, then you might consider dumping the records to a flat file for later procesing and then just drop that table and recreate it for further logging. Should be a lot faster than trying to do a massive delete from a production server. Personally, I would never write those logging entries to the same database server relied upon for responding to user requests. Are the log entries being produced by the same script that is serving users? I bet they are - if true, then rewrite the script to send those log records somewhere else besides the critical database/server. hope this helps --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
