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?

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

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.