CODEWITHSUNDEEP
mysqlforeign-keysinnodb
user3275126
user3275126

Reputation: 3

Find all rows from a table which don't have references in any other tables from the same database

I'm using foreign keys and InnoDB. I have an image table which contains all uploaded images data. Every time i delete a connection to the image table, i usually mark the specific row from image table as deleted. So the garbage collector usually deletes unused images.

There isn't any image used by more than 1 connection at a time.

For optimizations purposes i want to find all rows which remained without connections in image table(column "Id"). Because any unused row is a zombie row.

From our failed tests there remained some rows without connections.

example image table:

CREATE TABLE `image` (
  `Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `FileName` varchar(255) NOT NULL,
  `Is_Deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Disabled` tinyint(1) unsigned DEFAULT '0',
  PRIMARY KEY (`Id`),
) ENGINE=InnoDB AUTO_INCREMENT=816 DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 1758

Answers (1)

ForguesR
ForguesR

Reputation: 3618

You can list all your "zombie" rows with something like this query :

SELECT
  *
FROM
  image
WHERE
  image.`Id` NOT IN (SELECT table1.IdFK FROM table1 UNION 
                     SELECT table2.IdFK FROM table2 UNION
                     SELECT table3.IdFK FROM table3 ... )

Basically it checks if the image.Id is present as a FK in another table. You can add as many UNION that you need.

As a side note maybe should consider adding integrity constraints?

Upvotes: 3

Related Questions