1

This question is more theoretical. Let's say there is an entity in a one-to-many relationship. For example, a blog post can have zero or more tags. Those tags are stored in another table (that contains just 2 columns - the post_id and the tag). In a web app, the logic might be to fetch and show all the tags for each post when a post gets fetched. Is having a has_tags boolean column to be used conditionally to know whether to query the post tags (saving a query) a bad micro-optimization practice?

Edit: I removed the statement about multivalued attribute tags because it was incorrect, misleading, and caused confusion. I am sorry for this. I did not plan to store multiple values in one column but in a separate table for the same concerns listed by the answers to this question.

Edit 2: I thought of using a JOIN before asking this question but was afraid that it might lead to lower query performance than having a second query once the tables start to grow (probably because of creating a Cartesian product between the 2 tables before filtering, but I am not sure if it is implemented in such a way). That is why I posted this question.

3
  • The first thing you need to do is be specific about which query you want to optimize for. You haven't described any specific query. Storing a has_tags column doesn't do anything, it just wastes some space. Once you identify which queries you're trying to optimize, then you can start to evaluate whether this column is helpful. Aug 5 at 3:33
  • I want to optimize for fetching posts with their attributes, along with their tags (if any). I was thinking of first fetching a specific post with a basic query like SELECT id, title, content FROM posts WHERE id = 1 and then query for the post tags (all rows) with something like SELECT tag FROM post_tags WHERE post_id = 1. My goal is to not query the post_tags table at all if I can know in advance that there are no tags for a post. Is that more clear, or should I elaborate more?
    – kataba
    Aug 5 at 8:25
  • 1
    You mentioned in a comment below that you use MariaDB, not MySQL. Although MariaDB started as a fork of MySQL in 2010, and they still have mostly code in common, both products continue to change, and you shouldn't assume they are compatible anymore. They should now be treated as different RDBMS products, and you should tag your question appropriately. Aug 5 at 16:00

3 Answers 3

2

It smells a little bit like a premature optimization and probably doesn't have much added benefit since every Post that has a Tag will already be in the PostTags table. So filtering on the Posts.has_tags column won't reduce the search space any more than the JOIN between the Posts table and the PostsTags table would.

Furthermore, maintaining the additional index on Posts.has_tags would require more write work from the database. So in a sense it may even be a net loss in overall performance.


By the way, after re-reading your post, I noticed you specifically said "the table representing the post's multivalued attribute tags". As Rick James mentioned, don't store multiple values in one field. Rather, the PostTags table should have 1 row per Tag per Post thus making it a one-to-many relation with the Posts table.

This will allow you to manage and query the data easier. It also will potentially be more performant should you ever want to query for specific Posts that share the same specific Tag, and makes indexing on the Tag column better.

Even with the scenario where if you wanted to remove a Tag from the database, it would be more performant because you can search by Tag = 'SomeTag' directly to get all rows that have that Tag rather than having to parse the multivalued Tags column of every row of the table.

1
  • 1
    I think I caused confusion because I used the term multivalued incorrectly, sorry for that. I meant that the attribute is multivalued but not in database design but conceptually. As I mentioned in the original question, I was planning to store the tags in a separate table (for that same performance reason that you mentioned).
    – kataba
    Aug 5 at 15:20
1

Storing a posts.has_tags column has a risk: it might be incorrect. If someone might add a tag without setting the has_tags to true, or delete the last tag without setting has_tags to false, then it's not a reliable boolean flag. You have to check the post_tags table anyway.

Could you run a periodic verification job to make sure the boolean flags are correct? How often should you run this? I.e. how long can your application tolerate the data being wrong?

If you find a discrepancy, for example the flag is true but there are no tags in the post_tags table, then which is correct? Should you set the flag to true? Or was it intended to set it to false and delete the tags, and someone just forgot or their attempt to delete the tags failed? How can you know what to do to correct the data?

I suggest you learn about outer joins:

SELECT id, title, content, GROUP_CONCAT(tag) AS tags
FROM posts 
LEFT OUTER JOIN post_tags on posts.id = post_tags.post_id
WHERE posts.id = 1

This is the way to query optional data. If tags exist, they'll be included in the query result. If no tags exist, the list of tags will be NULL, but the data from posts will still be returned. That's how an outer join works.

If post_tags.post_id is indexed, this will probably be good enough with respect to efficiency.

Then you don't need the has_tags boolean column, and all the difficulties disappear.

8
  • Wouldn't using an OUTER JOIN be slower in terms of performance than making a second query? One of the reasons I did not want to group the tags into one value using a JOIN was this fear. Doesn't the OUTER JOIN require more processing because it has to make a cartesian product of the two tables first and then filter out rows? stackoverflow.com/a/3468666/11481924
    – kataba
    Aug 5 at 15:10
  • As for maintaining the has_tags boolean, my goal was to have it updated automatically by a PHP script/backend API each time a post gets edited. Something like checking if a post has tags listed in a UI on post edit/save. If yes, then has_tags gets to true, otherwise false.
    – kataba
    Aug 5 at 15:14
  • So maintaining the boolean column's accuracy is dependent on application code. How confident are you that your code doesn't have bugs? How confident are you that another programmer who works on your project also writes perfect, bug-free code? These can never be 100% confidence. But if you store your data in a normalized way, you don't have to worry. Normalized data can't have discrepancies. Aug 5 at 15:20
  • 2
    It's certainly not common practice to store data in two separate locations; the list of tags attached to a post should be the only source of truth concerning whether or not there are tags attached to the post, just as Bill mentions in his answer. In fact, many people would suggest the DRY mantra (don't repeat yourself) applies very well here.
    – Hannah Vernon
    Aug 5 at 15:53
  • 2
    Denormalization is a reasonably common solution if the joins do turn out to be a problem for performance. But joins are fine for most applications, and you should design using normalization first, then refactor to denormalized only if and when that's the best way to meet performance requirements. Aug 5 at 15:57
1

If you ever need to find all Posts with tag = 'xyz', you will regret having a commalist of tags. (The query will be slow.)

If you ever need to add or remove a tag for a Post, you will regret having a commalist of tags. (The code is clumsy.)

CREATE TABLE PostsTags (
    post_id ..., 
    tag VARCHAR(..) NOT NULL,
    PRIMARY KEY(post_id, tag),
    INDEX(tag)
) ENGINE=InnoDB;

Note: having id AUTO_INCREMENT would get in the way of performance.

Get rid of has_tags, simply assume there are some tags, then discover that there aren't any. LEFT JOIN lets you find zero tags without causing the entire query to fail.

See

GROUP_CONCAT(tag ORDER BY tag SEPARATOR ', ')

as a way to build a commalist at SELECT time.

Example 1

(Note: I am assuming that PostTags has the indexes above.)

Find info about post #123:

SELECT p.*,
       GROUP_CONCAT(pt.tag) AS tags
    FROM Posts AS p
    LEFT JOIN PostTags AS pt ON p.id = pt.post_id
    WHERE p.id = 123;

Or, and this might a little clearer, but maybe the same speed:

SELECT p.*,
       ( SELECT GROUP_CONCAT(pt.tag)
             FROM PostTags AS pt
             WHERE p.id = pt.post_id
       ) AS tags
    FROM Posts AS p
    WHERE p.id = 123;

To find all posts with tag='xyz', it becomes more obvious that the JOIN is going to be efficient.

SELECT p.*
    FROM Posts AS p
    JOIN PostTags AS pt ON p.id = pt.post_id
    WHERE pt.tag = 'xyz';

In either formulation, the column tags will come back NULL if there are no tags. To turn that into, say, "(no tags)":

... COALESCE( (...) , "(no tags)") AS tags, ...

Example 2

This will start with INDEX(tag) and then join to Posts.

2
  • I think I caused confusion because I used the term multivalued incorrectly, sorry for that. I meant that the attribute is multivalued but not in database design but conceptually. As I mentioned in the original question, I was planning to store the tags in a separate table (for that same performance reason that you mentioned).
    – kataba
    Aug 5 at 15:38
  • @kataba - Thanks for the clarification. Do not worry about the JOIN. Do not "collect ids, then run another query"; instead, do a JOIN. I added some specific examples.
    – Rick James
    Aug 5 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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