When i use multiple words in Boolean Full-Text Searches, InnoDB Engine i expect to return relevance results where words appears more times, but for some reason this is not happen.
My question is how to sort results where words appears more times
for example if i have phrase: new car BMW , i expect 1st few results to include all 3 word then next resutls to include any 2 word and at the end results where include only 1 of the word
The problem in query is results with score_name 0.6127969622612 are below when they match 2 word and results with score 3.12283992767334 match only one word
for example you can look at results with:
product_id: 16380, score_name=3.12283992767334, match only one word(повдигаща)
product_id: 29953, score_name=0.6127969622612, match 2 words(разтегателна, маса)
How to sort 1st results where words appears more times ?
Anyone can help to solved this problem ?
Query
SELECT pd.product_id, pd.name,
CASE
WHEN name = 'разтегателна повдигаща се маса' THEN 200
WHEN name LIKE 'разтегателна повдигаща се маса%' THEN 100
WHEN name LIKE '% разтегателна повдигаща се маса %' THEN 50
WHEN name LIKE '%разтегателна повдигаща се маса' THEN 50
ELSE
MATCH(name) AGAINST('разтегателна повдигаща се маса' IN BOOLEAN MODE) END AS score_name
FROM product_description pd
WHERE
MATCH(name) AGAINST('разтегателна повдигаща се маса' IN BOOLEAN MODE)
ORDER BY score_name DESC
LIMIT 80
DB
CREATE TABLE `product_description` (
`product_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `product_description`
ADD PRIMARY KEY (`product_id`),
ADD KEY `name` (`name`);
ALTER TABLE `product_description` ADD FULLTEXT KEY `name_full_text` (`name`);