Каково оптимальное решение для тега/поиска по ключевым словам?

Мой первый выстрел был бы чем-то как:

SELECT
    P.product_id,
    COUNT(*)
FROM
    Product_Keywords PK1
INNER JOIN Product_Keywords PK2 ON
    PK2.keyword_id = PK1.keyword_id
INNER JOIN Products P ON
    P.product_id = PK.product_id
WHERE
    PK1.product_id = @product_id
GROUP BY
    P.product_id
ORDER BY
    COUNT(*) DESC

Соединение Product_Keywords к Product_Keywords (PK2 к PK1) могло бы быть грубым, таким образом, я не могу говорить с производительностью. Это - то, где я запустил бы, хотя и затем смотрят на оптимизацию.

Одна вещь рассмотреть, как продолжение комментария Assaf, состоит в том, что Вы могли добавить "вес" к Product_Keywords и СУММЕ (PK1.weight) + СУММА (PK2.weight) для рейтинга. Просто мысль.

Править: Для разработки взвешивание..., можно решить, что хотите позволить ключевым словам быть взвешенными. Фактический метод, используемый для определения взвешивания, был бы бизнес-решением, хотя, таким образом, я не могу действительно дать Вам слишком много указаний там.

Как пример, хотя, этот вопрос о "программировании", "поиске по ключевым словам" и "SQL". Программирование довольно универсально, поэтому если бы два вопроса имели это вместе, то это все еще не могло бы означать, что они - то, который имел отношение поэтому, возможно, Вы только взвешиваете его как 1. SQL немного более конкретен, так, чтобы Вы могли бы взвесить как 5. Поиск по ключевым словам является и основным фокусом вопроса, И это довольно конкретно, таким образом, Вы могли бы взвесить это с 10.

Это - просто пример, конечно, и как я сказал, точное определение весов, а также как Вы выигрываете, это зависит от определенного бизнеса. Вы могли бы решить, что соответствие количеству ключевых слов более важно, чем веса поэтому, возможно, взвешивание используется только в качестве дополнительного времени и т.д. HTH.

Хорошо, возможно, что-то как follwing:

select p.productId, p.name, r.rank
from products p inner join (
/* this inner select should bring in only products that have at least one keyword
=> shared with the requested product, and will count the actual number shared (for ranking)*/
    select related.productId, count(related.productId) as rank
    from
        products_keywords related   inner join 
        products_keywords pk ON (pk.productId = @productId  AND related.keywordId = pk.keywordId)
    where related.productId <> @productId
    group by related.productId
) r on p.productId = r.productId
order by r.rank DESC /* added DESC (not in orignal solution, but needed to put higher ranked on top)*/

Теперь я серьезно сомневаюсь, что это - оптимальный sql оператор, но это должно сделать задание. Я не могу проверить его хотя, так как я просто записал это с нуля без фактических таблиц поддержки или данных для тестирования против.