MySQL：具有多个类别的产品的SQL和DB [英] MySQL: SQL and DB for product with multiple categories

问题描述

查询（查询）请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询：SELECT * FROM ... .WHERE category LIKE'％，3，％';

但现在它只是为每个查询杀死数据库，具有上述查询的2Mil记录

类别表

ID名称

1个女性

2时尚

3服装

4附件

5顶部

6裙子

7耳环

8短裙

9长裙

10男

产品表格

ID ... ..类别...。其他位

1，1,2,3,6,9，...

2，1,2,4,7，

3，1,2,3,5，

4，10,2,3,4，

你的画面如上所述。现在如果我在产品表中的类别行上做FullText索引，它只给出一个基数：（

我如何克服这个？
我考虑复制行每个类别，但数据库是巨大的目前2 GIG和重复，它将大约10 GIG ...更像一个问题，然后一个解决方案

解决方案

p>我看到的一个解决方案是使用三个表格：

• 类别列出您的类别


• 是一个特殊的表格：每个行链接product_id到category_id

要按类别查找产品，您可以将category_map中的行与产品中的行进行匹配。 / p>

这是一个不完美的例子，但它得到了它的要点：

  SELECT * FROM 
（
 SELECT * FROM category_map 
 WHERE category_id = 1 
）AS映射
 INNER JOIN产品
 ON products.id = map .product_id; 
  

表连接是一个非常强大的工具;你可能想花一些时间阅读他们，如果你是新的使用它们。 编码恐怖有一个视觉化的解释，详细信息。

这是一个好主意，设置外键约束或以其他方式确保 category_map 添加到产品和类别中的现有条目。

I am working on a website which hold millions of records now (apologies cannot reveal which site) initially it had few hundred records so the query below was acceptable

Query: SELECT * FROM….WHERE category LIKE ‘%,3,%’;

But now it just kills the database as for each query it has to go through the entire 2Mil records with above query

Category table
ID NAME
1 Female
2 Fashion
3 Clothing
4 Accessories
5 Top
6 Dress
7 Earring
8 Short dress
9 Long dress
10 Male

Product table
ID…..Category….other bits
1 ,1,2,3,6,9, ……
2 ,1,2,4,7,
3 ,1,2,3,5,
4 ,10,2,3,4,

you have the picture as what is happening above. Now if I do FullText index on category row in product table it gives only 1 cardinality :(

How can I overcome this? I have considered duplicating row with each category but the database is huge currently 2 GIG and with duplicates it will turn roughly 10 GIG… more like a problem then a solution

解决方案

One solution I've seen is to use three tables:

• categories lists your categories
• products lists your products, without any attached category information
• category_map is a special table: each row links a product_id to a category_id

To look up products by category, you can then match rows in category_map against rows in products.

This is an imperfect example, but it gets the gist of it:

SELECT * FROM 
(
    SELECT * FROM category_map 
    WHERE category_id=1
) AS map 
INNER JOIN products 
ON products.id = map.product_id;

Table joins are a very powerful tool; you may want to spend some time reading up on them, if you're new to using them. Coding Horror has a visual explanation that skims over the details.

It would be a good idea to set up foreign key constraints or otherwise make sure that entries in category_map correspond to existing entries in products and categories.

这篇关于MySQL：具有多个类别的产品的SQL和DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！