MySQL Deleting rows from the database

Question

I'm storing a simple tag relationship and I'm wondering what would be the better way to delete tags?

The two ways I know of:

1. SELECT tag FROM content_tags WHERE content_id = 10
2. Use php to compare the result with the new tags supplied and if there are any tags from the database result that are not in the new tags, run a delete query on that tag
3. The rest of the tags are either new or already exist so I use INSERT INTO content_tags ... ON DUPLICATE KEY UPDATE

Or simply:

1. DELETE FROM content_tags FROM tags WHERE content_id = 10
2. Insert each tag (no need to use ON DUPLICATE KEY UPDATE here as the tags for this content_id are already deleted at this point)

I'm current doing the first method, but the 2nd way seems to be much less of a hassle. But I'm not sure if it's "bad" or inefficient to use delete in this fashion. Or if you have a better method feel free to share.

Answer 1

You could adjust the first method to do also not do the inserts on tags that already exist. But it could (and probably will) still result in more queries then the second method, especially if you combine those insert statements in a single query.

//small example :)

INSERT INTO content_tags
(content_id, tag_id)
VALUES
(1,1),
(1,2),
(1,3);