Guys, I know that version 3.23.51 of MySQL does not handle deleting data from multiple tables directly. I am in a situation where i have to delete millions of records from a dozen tables. The delete logic is not simple, i have to check a number of tables before i can actually delete a row from a given table. All of my databases run in a 24x7 env. In order to reduce my scheduled downtime here's what i am planning to do, ofcourse this will be tested against the test env first. Please let me know your feedback. I would surely appreciate to hear about any other better way of accomplishing this task.
1) create temporary (not actually a TEMPORARY) tables for all the tables to be purged using CREATE TABLE ... statement. The structure of the table would be the same as the original table to be purged. I could have used CREATE TABLE ... AS SELECT * FROM .... statement for creating a temp table, but this statement does not create the indexes, PK, auto_increment et al... 2) Load the data from the original purge table into the temp table based upon my delete logic. 3) Drop the original purge table. 4) Rename the temp table that i have created to the name of the purge table that i have dropped. Let me know if this sounds as a viable solution. I would sure like to hear anything about the table locking and grant issues that i might encounter. Thanks Sri --------------------------------------------------------------------- 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
