A better way to delete a list of elements from multiple tables
- by manyxcxi
I know this looks like a 'please write the code' request, but some basic pointer/principles for doing this the right way should be enough to get me going.
I have the following stored procedure:
CREATE PROCEDURE `TAA`.`runClean` (IN idlist varchar(1000))
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
START TRANSACTION;
DELETE FROM RunningReports WHERE run_id IN (idlist);
DELETE FROM TMD_INDATA_INVOICE WHERE run_id IN (idlist);
DELETE FROM TMD_INDATA_LINE WHERE run_id IN (idlist);
DELETE FROM TMD_OUTDATA_INVOICE WHERE run_id IN (idlist);
DELETE FROM TMD_OUTDATA_LINE WHERE run_id IN (idlist);
DELETE FROM TMD_TEST WHERE run_id IN (idlist);
DELETE FROM RunHistory WHERE id IN (idlist);
COMMIT;
END $$
It is called by a PHP script to clean out old run history. It is not particularly efficient as you can see and I would like to speed it up. The PHP script gathers the ids to remove from the tables with the following query:
$query = "SELECT id, stop_time FROM RunHistory WHERE config_id = $configId AND save = 0 AND NOT(stop_time IS NULL) ORDER BY stop_time";
It keeps the last five run entries and deletes all the rest. So using this query to bring back all the IDs, it determines which ones to delete and keeps the 'newest' five. After gathering the IDs it sends them to the stored procedure to remove them from the associated tables. I'm not very good with SQL, but I ASSUME that using an IN statement and not joining these tables together is probably the least efficient way I can do this, but I don't know enough to ask anything but "how do I do this better?"
If possible, I would like to do this all in my stored procedure using a query to gather all the IDs except for the five 'newest', then delete them. Another twist, run entries can be marked save (save = 1) and should not be deleted.
The RunHistory table looks like this:
CREATE TABLE `TAA`.`RunHistory` (
`id` int(11) NOT NULL auto_increment,
`start_time` datetime default NULL,
`stop_time` datetime default NULL,
`config_id` int(11) NOT NULL,
[...]
`save` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
