Wyszukiwarka używająca dwóch tabel - jakie optymalne zapytanie Opcje

[adam_c]

Witam serdecznie,

Tworzę właśnie system do wyszukiwania artykułów i mam problem z napisaniem odpowiedniego, wydajnego zapytania pobierającego dane. Myślę, że rozwiązanie tego mogłoby przydać się również innym użytkownikom forum.

Do wyszukiwania artykułów wykorzystywana jest tabela wyszukiwanie o następujących kolumnach:
id | id_artykulu | element | slowo | waga

Do każdego artykułu przypisane są frazy kluczowe, nazwa oraz treść i w takiej kolejności są dla nas ważne przy wyszukiwaniu. Każdy z tych elementów podzielony jest na pojedyncze słowa. Aby dany artykuł mógł zostać wyświetlony powinniśmy znaleźć wszystkie wyszukiwane słowa w danym elemencie, przy czym każde znalezione słowo otrzymuje punktację wagową. Np. wyszukiwana fraza "piękna zima" otrzyma 2 000 000 pkt. wyszukana wśród fraz kluczowych, 20 000 w nazwie, 200 w treści. Wartości te nie sumują się w razie znalezienia w kilku elementach. Jeśli znajdziemy we frazie kluczowej to dalsze elementy nas nie interesują - tylko jeden, najwyżej oceniany element.

Artykuł w tabeli wyszukiwania może więc wyglądać następująco (dwie frazy kluczowe, nazwa, treść):

id | id_artykulu | element | slowo | waga
------------------------------------------------------
1 | 1 | 1 | piękna | 1000000
1 | 1 | 1 | zima | 1000000
1 | 1 | 2 | ładne | 1000000
1 | 1 | 2 | lato | 1000000
1 | 1 | 3 | jakaś | 10000
1 | 1 | 3 | nazwa | 10000
1 | 1 | 4 | tresc | 100
1 | 1 | 4 | tego | 100
1 | 1 | 4 | artykulu | 100

Dane do wyświetlenia pobieramy z tabeli artykuly o następujących kolumnach:
id | nazwa | tresc | waga_dodatkowa | top | aktywny

Waga dodatkowa powinna być dodawana do obliczonej wagi w znalezionym elemencie. Jeśli artykuł ma 1 w kolumnie "top" to powinien być pozycjonowany wg wag na samej górze. Potem wyświetlamy artykuły o top = 0. Oczywiście bierzemy również pod uwagę kolumnę "aktywny".

Teraz pytanie: jak napisać najbardziej optymalne zapytanie pobierające artykuły (powiedzmy 10 z danego przedziału) jak i również ilość wszystkich znalezionych artykułów (na potrzeby dzielenia na strony)?

Próbowałem wstępnie pobrać aby samą listę artykułów, jednak otrzymują zdublowane wyniki, tzn. jeśli coś zostało znalezione w jednym artykule w 2 miejscach to otrzymuję to dwa razy. Zapytanie, które wykonuję:

[SQL] pobierz, plaintext 
SELECT DISTINCT(t1.id_wpisu) AS s_id_wpisu, SUM(t1.waga) AS s_waga, COUNT(t1.id) AS s_ilosc FROM `wyszukiwanie` t1 WHERE t1.slowo IN(".implode(',',$wyszukiwane).") GROUP BY t1.element HAVING s_ilosc >= ".count($wyszukiwane)." ORDER BY s_waga DESC
[SQL] pobierz, plaintext 

Działa to jednak tylko w przypadku, gdy w kolumnie "element" znajdują się unikalne wartości dla każdego elementu, tak więc musiałbym tam mieć np. "1_1" itd. gdzie pierwsza liczba to id artykułu.

Pozdrawiam serdecznie i dziękuję z góry za pomoc.


EDIT
-----------------------------
Dodam jeszcze, że ilość rekordów może być liczona nawet w milionach tak więc fajnie, gdyby było to dość wydajne rozwiązanie

Ten post edytował adam_c 28.12.2011, 18:09:59