MYSQL comma separated IDs vs separate table

Question

Consider a table products which has product details including its category. One product may belong to multiple category so I'm keeping it as a comma separated list of category IDs.

I know this is not normalized approach.

Can any MYSQL expert tell me that which approach will be faster for selecting products of a particular category.

Obviously we have to JOIN products table and products_category_relation table if take normalized approach.

AND

In my approach we have to write a like query to find the products (assume we are searching for category id 10)

SELECT p.* 
FROM products p
WHERE p.category like '10' 
OR p.category like '10,%'
OR p.category like '%,10'
OR p.category like '%,10,%'

Can any one tell me if this approach is faster or the JOIN approach will be faster?

I know about normalization. I know about other risks involved in my approach. But they do not matter in my case. So, I'm concerned with speed.

Any theoretical explanation about its speed or a practical test result are welcome.

UPDATE

I'm using myISAM engine product table has Primary Key product_id FullText index on category column of products table

score 5 · Answer 1 · answered Jun 18 '12 at 12:35

5

Try using FIND_IN_SET function.

SELECT * FROM `products` WHERE FIND_IN_SET('10',`category`)>0;

You can then compare the results vs normalized approach, but this will definitely be more robust than multiple LIKE clauses

answered Jun 18 '12 at 12:35

poncha

7,726
2
34
38

1

It is more robust and work fine, but speaking of performance this is still slower than a separate table – CodeBrauer Mar 03 '17 at 08:25

Emil Vikström · Accepted Answer · 2012-06-18T13:09:22.043

3

A database conforming to the first normal form will be much faster. Your example query cannot use any index and requires a full table scan to resolve. Worse, it must scan the entire text field for all the rows and text work is almost always more expensive than integer work for the computer.

A normalized table can easily use an index on the category column to speed up the query.

Text storage may also require more space on disk since numbers are usually more expensive when saved as characters than as the correct integer type (of course there's some overhead involved in row storage as well).

edited Jun 18 '12 at 13:09

answered Jun 18 '12 at 12:33

Emil Vikström

90,431
16
141
175

"It will also require more space on disk since numbers are usually more expensive when saved as " @EmilVikstrom, I think it is true for big numbers. usually category id do not exceed 3 characters. – Imdad Jun 18 '12 at 12:44
1

Imdad, in that case, use an `UNSIGNED SMALLINT` which uses 2 bytes and have a range from `0` to `65535`. – Emil Vikström Jun 18 '12 at 12:48
As far as now your answer seems to be better than others. +1 – Imdad Jun 18 '12 at 12:50

score 1 · Answer 3 · answered Jun 18 '12 at 12:33

1

take the normalized approach.

you haven't given much information about the tables involved, the keys and indexes set on that tables and the engine you're using, but a JOIN will be faster in almost any case (much faster than the like-mess).

answered Jun 18 '12 at 12:33

oezi

51,017
10
98
115

MYSQL comma separated IDs vs separate table

3 Answers3

Linked