CODEWITHSUNDEEP
mysqlcascading-deletesdescendant
Horse
Horse

Reputation: 3063

Mysql delete descendants

I have a table of images, and those images will have various descendants such as thumbnails, different sized versions, and crops (and thumbs of those crops), etc.

This means each original image can potentially have quite a few descendants. These descendants are referenced by a parent_id field that gives the direct parent.

What is going to be the best, most efficient way to do this, as obviously selecting all children for each image / level and then deleting them is going to be pretty hefty if there is a batch image delete?

Upvotes: 0

Views: 99

Answers (2)

SergeS
SergeS

Reputation: 11779

Use InnoDB foreign keyes - and link parent_id to id with cascade delete option

Or use different tree indexes, to avoid recursion. For example Nested Set Model, as explained in Managing Hierarchical Data in MySQL.

Upvotes: 1

diagonalbatman
diagonalbatman

Reputation: 18002

What you need to be looking at.. is cascading deletes.

This article from mysql website should help you out.

http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html

Upvotes: 1

Related Questions