1

What's the efficient or best way on updating a multiple tags or categories?

Currently I have posts and posts_meta tables.

I have this structure on posts_meta table:

+----+---------+----------+------------+---------------------+---------------------+
| id | post_id | meta_key | meta_value | created_at          | last_update_at      |
+----+---------+----------+------------+---------------------+---------------------+
| 94 |     111 | tags     | art        | 2016-07-05 15:01:05 | 2016-07-05 15:01:05 |
| 95 |     111 | tags     | books      | 2016-07-05 15:01:05 | 2016-07-05 15:01:05 |
| 96 |     111 | tags     | comics     | 2016-07-05 15:01:05 | 2016-07-05 15:01:05 |
+----+---------+----------+------------+---------------------+---------------------+

Currently what I was thinking is when I update the tags on a single post, I delete the current tags and then insert the new ones.

What I think the problem on that is when I update a post without changing the tags, the function will still going to happen(deleting the current tags and inserting new ones). And I think that is not efficient.

Another solution I thought was just updating the current tags row by just changing the meta_value.

So from:

+----+---------+----------+------------+---------------------+---------------------+
| id | post_id | meta_key | meta_value | created_at          | last_update_at      |
+----+---------+----------+------------+---------------------+---------------------+
| 94 |     111 | tags     | art        | 2016-07-05 15:01:05 | 2016-07-05 15:01:05 |
+----+---------+----------+------------+---------------------+---------------------+

to:

+----+---------+----------+------------+---------------------+---------------------+
| id | post_id | meta_key | meta_value | created_at          | last_update_at      |
+----+---------+----------+------------+---------------------+---------------------+
| 94 |     111 | tags     | magazine   | 2016-07-05 15:01:05 | 2016-07-05 15:01:05 |
+----+---------+----------+------------+---------------------+---------------------+

But another problem here is that, what if the current post have 3 tags and the update has 4 new tags or vice versa. I know we can do some workaround for that but, not sure if that's efficient or worth to do it for just a simple update.

Now I'm really not sure if this is how I should do it.

Any suggestions?

Thanks

2
  • Is this your software? Is it possible to alter the schema? Jul 5, 2016 at 7:31
  • @JamesAnderson Yep it is mine. Yes possible to alter the schema Jul 5, 2016 at 7:36

1 Answer 1

1

Usage?

You have not listed what kind of queries will hit this table. You can't really design a table, much less optimize it, until the SELECTs are tentatively written. More later.

First, some simplification

Get rid of id; make PRIMARY KEY(post_id, meta_key, meta_value). After all, that has to be unique. The id is clutter and slows things down for the normal operations which involve WHERE post_id = constant AND meta_key = constant.

Because of the 3 tags vs 4 tags you mentioned, the only reasonable way to do it is

DELETE FROM meta WHERE post_id = ? AND meta_key = ?;
INSERT INTO meta (post_id, ... )
    VALUES
    (...), (...), (...), (...);

Then some more simplification

The EAV schema (which you have) sucks. You may as well have a table called Tags. Now you can get rid of meta_key, and everything gets simpler and shorter.

You may choose to keep the EAV stuff for where there is only one value for a meta_key. That is a separate discussion. I'm arguing that it is wrong to fit multi-valued "tags" into that mold.

Further

Do you really need those timestamps? WP may be omnipresent, but it is not necessarily 'right'.

Ordering?

When you fetch everything for some tag, don't you want to put them is some order? And might you paginate it? So, maybe you need one of those timestamps for this?

Bottom line

CREATE TABLE `tags` (
    post_id ... NOT NULL,
    tag varchar(55) NOT NULL,
    sort_order ... NOT NULL,
  PRIMARY KEY (tag, sort_order, post_id),  -- for the main select, plus dedup
  INDEX(post_id)  -- for maintenance
) ENGINE=InnoDB;

Further discussion

5
  • Wow thanks, very informative post. Will update and create new tags table. Just one thing regarding the updating tags query. I'm planning to use these instead: Delete from tags where post_id = 1 AND not tag in ("art", "books", "comics", "magazines"); then INSERT IGNORE INTO tags (post_id, tag) VALUES (1, "notebook");. Can you let me know if it's not good to use this? Thanks Jul 12, 2016 at 7:22
  • That pair of queries should work fine if you have PRIMARY KEY(post_id, tag). (I don't like it because of how much work to set it up.) In my implementation, I do DELETE FROM tags WHERE post_id=1; INSERT INTO tags (...) VALUES (1, 'art'), (1, 'notebook'), ...; Your approach is probably faster.
    – Rick James
    Jul 12, 2016 at 15:16
  • You implied there was to be some ordering. The 'Bottom Line' is optimized for showing, for example, the "latest 10 news articles". If you don't have ordering, then some things probably need changing.
    – Rick James
    Jul 12, 2016 at 15:18
  • Ah sorry for the confusion, no I won't be needing ordering for the tags. If you don't mind, can you let me know why there's still need to change? Was thinking it's already a great approach. Jul 12, 2016 at 19:15
  • 1
    I'm suggesting you need only 2 columns, not 6. And you have a 'natural' PRIMARY KEY, so don't include an AUTO_INCREMENT.
    – Rick James
    Jul 12, 2016 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.