how to optimize delete queries from (for) multiple tables (into one)?

Question

ok i am currently deleting successfully from multiple tables with three queries selecting the row where category_id is present, except one, there i use a subquery to get the post_id by category_id. How can do it in a more efficient and comprehensive manner ? Thanks !

/**
 * delete category by id
 * @param $id
 * @return bool
 */
public function delete_category($id){

    $this->db->query("DELETE FROM categories WHERE categories.id = $id");
    $this->db->query("DELETE FROM comments
                      WHERE comments.post_id = ANY (SELECT post_id
                                                    FROM posts
                                                    WHERE category_id = $id)");
    $this->db->query("DELETE FROM posts
                      WHERE posts.category_id = $id
                      AND posts.category_id IS NOT NULL");
}

The code works fine it gives desired results, but it seems kind of gross, how can i optimize it ?

Answer 1

MySQL has a multi-table DELETE syntax.

You essentially take a SELECT query that selects the rows that you want to delete and you change the SELECT * FROM to DELETE <table_name>, <table_name> FROM, with <table_name>, <table_name> being the tables to delete from.

For example if you have these DELETE queries:

DELETE FROM comments WHERE post_id IN (SELECT post_id FROM posts WHERE category_id = 1);
DELETE FROM posts WHERE category_id = 1;
DELETE FROM categories WHERE category_id = 1;

You can rewrite them as a SELECT that selects all rows you want deleted:

SELECT *
FROM categories
JOIN posts USING (category_id)
JOIN comments USING (post_id)
WHERE category_id = 1

Then you can replace the SELECT with the multi-table DELETE:

DELETE categories, posts, comments
FROM categories
JOIN posts USING (category_id)
JOIN comments USING (post_id)
WHERE category_id = 1