CODEWITHSUNDEEP
mysqlsqlperformance
Silviu-Marian
Silviu-Marian

Reputation: 10927

Should I store multiple ids as varchar and use FIND_IN_SET?

I see platforms using this:

- properties table -
id | name | ID_CATEGORY (INT)

- categories table -
id | name

- property_categories -
id_category | id_property

And retrieving properties by category with

SELECT * FROM properties, categories, property_categories
WHERE 1
   AND categories.name = "Some category name here" # match this category name
   AND categories.id = property_categories.id_category # match category
   AND properties.id = property_categories.id_property # and match property

But I recently saw in an application this (with 2 tables instead of 3):

- properties table -
id | name | ID_CATEGORY (VARCHAR(255))

- categories table -
id | name

And retrieving properties by category with this query:

SELECT * FROM properties, categories
WHERE 1
   AND categories.name = "Some category name here" # match this category name
   AND FIND_IN_SET(categories.id, properties.ID_CATEGORY)

Are there any performance benefits? Should the second method be used in favor of the first one? Is it ok if I leave this unchanged?

Sometimes there are many fields combined (including ID_TYPE, ID_STATUS, etc.) and there's a big JOIN gluing all data together for filters.

Upvotes: 1

Views: 606

Answers (1)

Alain Collins
Alain Collins

Reputation: 16362

FIND_IN_SET is for comma-delimited strings, e.g.

SELECT FIND_IN_SET('b','a,b,c,d');

That would be a lousy way to store a bunch of values - for example, you can't use an index to find 'b'.

Use multiple rows.

Upvotes: 2

Related Questions