The Advantages and Disadvantages of FIND_IN_SET and JOIN in Mysql in One-to-many Relational Query

  mysql, question

Before I knew the FIND_IN_SET function in Mysql, I would design a relational table in the middle when doing a one-to-many foreign key query. For example, if an article (post) corresponds to multiple tags, I will design a relationship table (post_tag_mapping) in the middle to record the correspondence between the article and the tags, and then use it when querying the article under a tag.JoinStatement, which should also be the standard practice when handling one-to-many relational queries. General statements are like the following

SELECT * FROM post LEFT JOIN post_tag_mapping WHERE tag_id = 123

But when I found outFIND_IN_SETFunction, you can use it completelyJoinWith this query method, we can SET a tag field (tags) of type set in the article table, which stores tag id in a format like this.111,222,333The query statement becomes

SELECT * FROM post WHERE FIND_IN_SET('123', tags)

There is no need to do connection query. I don’t know which of the two is more advantageous in query efficiency. What do they need to pay attention to in their application scenarios?SetCan types be indexed? Yes after they are finishedFIND_IN_SETIs it useful?

In your case, using an intermediate (mapping) table can improve query efficiency with the help of indexes. FIND_IN_SET is a string operation. if it is used on a field, a full table scan will be performed.

The implementation of SET type is 64-bit bitmap (corresponding to 64 members). As the second parameter of FIND_IN_SET, bit operation can be used to improve the efficiency of the function (not equal to the query efficiency). Restrictions include:

  • Members cannot contain’,’ characters
  • There can only be 64 members at most, and it needs to be predefined, so it is inappropriate to use tag.
  • Even if the index is added, it is integral (i.e. the combination state corresponding to 64 bits), and cannot be applied to a member (a bit)
  • The UPDATE operation is also integral, and you need to calculate the new bitmap yourself.

Therefore, SET is most suitable for the state set with small range, fixed value and overall query. For example, record which provinces in China a person has been to: you can directly compare whether two people have been to the same province, or you can directly obtain the difference, and you can efficiently query which people have only been to a province or some provinces, but checking which people have been to a province or some provinces is still sweeping the whole table.