问题描述
查询(查询)请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询请求查询: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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!