MYSQL PHP:如何限制选择 %like% 逗号列表搜索? [英] MYSQL PHP: How to fasten up a select %like% comma list search with a limit?

问题描述

如何加快 %like 搜索?我需要帮助.想法 1= 限制，想法 2= 拆分逗号关键字以分隔列

How to fasten up the %like search? I need help. Idea 1= limit, idea 2= split comma keywords to seperate columns

1. 它是一个逗号分隔的关键字列表.
2. 在 1000 个结果后，搜索查询可能会停止，以加快搜索查询的速度(如何?).
3. 如果我可以使用限制，我需要让搜索查询以评分最高的产品"开头.=评级"并在大约 1000 个结果后停止(如何?).
4. 我的搜索查询:replace(b.keywords,',',' ') like '% ".$searchvalue."%')";

我的想法(1)是将结果限制在1000个.但是我如何控制选择，以便首先搜索评分最高的产品并且不会错过.一旦找到 1000(最大值)，查询就会停止.但是应该首先搜索评价最高的产品，否则就会错过.在 1000 个结果后，搜索查询应停止，以加快搜索查询.

My idea (1) is to limit the results to 1000. But how can I control the select so that the products with the best rating are searched first and will not be missed. As soon as 1000 (max) are found, the query should stop. But the best-rated products should be searched first or will be missed. After 1000 results the search query should stop, to fasten the search query.

我有一张桌子产品.从 0 到 10.000 的评级.

I have a table PRODUCTS. A RATING from 0 up to 10.000.

ID     RATING PRODUCTS   KEYWORDS
1      1000      p1      keyword1,keyword2,...,keyword100
...    4         ...     ...  
99000  200       p99000  keyword1,keyword99,...,keyword999

评分"和关键字"(多语言)不时发生变化.

我的想法 (2) 是将每个关键字单独列在一个列中.所以我不需要领先的 % $searchvalue%.但我担心，每个产品至少会有 200 个额外的列，这将导致相同的缓慢搜索查询 (?).你怎么看?

My idea (2) is to seperate each keyword in a single column. So i would not need the leading % $searchvalue%. But iam worried, there will be at least 200 additional columns for each product and this will result in the same slowly search query (?). What do you think?

ID     RATING PRODUCTS   KEYWORD1 KEYWORD2 KEYWORD3 ... KEYWORD**200** 
1      1000      p1      red      blue     yellow   ... yellow-orange
...    4         ...     ...  
99000  200       p99000  black    blue     orange   ... yellow-orange

推荐答案

使用单独的表格 product_keywords.

CREATE TABLE product_keywords (
    product_id INT NOT NULL,
    keyword VARCHAR(50) NOT NULL,
    FOREIGN KEY (product_id) REFEREFENCES products (id),
    UNIQUE INDEX (product_id, keyword)
);

加入此表以查找与关键字匹配的产品.

Join with this table to find the products that match a keyword.

如果您需要多语言关键字支持，您可以将keyword替换为带有关键字的表的外键，该表可以包含多种语言的关键字.

If you need multilingual keyword support, you can replace keyword with a foreign key to a table with keywords, and that table can contain keywords in multiple languages.

CREATE TABLE keywords (
    id INT NOT NULL,
    language VARCHAR(10),
    keyword VARCHAR(50),
    UNIQUE KEY (id, language),
    INDEX (keyword, language)
);

CREATE TABLE product_keywords (
    product_id INT NOT NULL,
    keyword_id INT NOT NULL,
    FOREIGN KEY (product_id) REFEREFENCES products (id),
    FOREIGN KEY (keyword_id) REFERENCES keywords (id),
    UNIQUE INDEX (product_id, keyword_id)
);

一个查询可能看起来像

SELECT p.*
FROM products AS p
JOIN product_keywords AS pk ON p.id = pk.product_id
JOIN keywords AS k on k.id = pk.keyword_id
WHERE k.language = 'en' AND k.keyword = 'blue'

这篇关于MYSQL PHP:如何限制选择 %like% 逗号列表搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！