MYSQL PHP: How to fasten up a select %like% comma list search with a limit?

Question

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

1. It is a comma seperated keyword list.
2. After 1000 results the search query could stop, to fasten the search query (how?).
3. If i could use a limit, i need to let the search query start with the "best rated products" = "rating" and stop after around 1000 results (how?).
4. my search query: replace(b.keywords,',',' ') like '% ".$searchvalue."%')";

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.

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

"Rating" and "keywords" (multilingual) are changing from time to time.

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

Answer 1

Use a separate table 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.

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

A query might then look like

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'