MYSQL逗号分隔的ID与分隔的表 [英] MYSQL comma separated IDs vs separate table

问题描述

考虑一个表products，该表包含产品详细信息，包括其类别.一种产品可能属于多个类别，因此我将其保留为用逗号分隔的类别ID列表.

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.

任何MYSQL专家都可以告诉我，哪种方法选择特定类别的产品会更快.

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

很显然，如果采用规范化方法，我们必须联接products表和products_category_relation表.

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

AND

在我的方法中，我们必须编写一个类似的查询来查找产品(假设我们正在搜索类别ID 10)

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,%'

有人可以告诉我这种方法是否更快，或者JOIN方法会更快?

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.

更新

我正在使用myISAM引擎 产品表具有主键product_id products表的category列上的全文索引

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

推荐答案

符合第一个范式的数据库将更快.您的示例查询不能使用任何索引，并且需要全表扫描才能解决.更糟糕的是，它必须扫描整个文本字段中的所有行，并且对于计算机而言，文本工作通常总是比整数工作昂贵.

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.

标准化表可以轻松地使用category列上的索引来加快查询速度.

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).

这篇关于MYSQL逗号分隔的ID与分隔的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！