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

[ShadowD]

Szczerze to nie zajmuję się wyszukiwaniem i niczego oprócz pełno tekstowego nie używałem, ale to co proponujesz wydaje mi się przekombinowane.

Co na pewno jest do zmiany to pole |waga| podmień je na |idWaga| i stwórz tabelę z wagami |id|title|waga|.

Cały kod rozumiem działa tak, że podczas dodawania nowego arta rozwalasz każdą jego część na słowa i dodajesz je do tej tabeli - jej rozmiary będą ogromne, nie wiem jak z prędkością tej metody, ale strata powierzchni występuje na pewno, a edycja arta lub czegokolwiek innego spowoduje sporo pracy nad tą tabelą. (Zakładając, że arty mają komentarze które też w taki sposób traktujesz to już w ogóle).

Pomyślał bym nad przerobieniem tego, nie wiem jak z słowami kluczowymi (tagami), tytułami - może Twój sposób i jest optymalny, ale przy treści skorzystał bym z wyszukiwania pełnotekstowego - nie sądzisz, że to lepszy sposób?

[adam_c]

Nie chciałem aż tak mieszać na forum więc uprościłem nieco metodę. U mnie działałoby to tak, że artykuł dzielę na słowa, każde słowo sprowadzam do podstawowej formy, a następnie wrzucam do tablicy globalnej ze słowami, jeśli jeszcze go tam nie ma. Każde słowo ma przypisane ID i właśnie to ID jest zapisywane w tabeli wyszukiwania. Fraza wpisana do wyszukiwarki również zostaje podzielona na słowa i sprowadzona do podstawowej formy. W tym momencie bardzo łatwo znaleźć w tabeli słowa, których szukamy bez potrzeby sprawdzania każdego artykułu po kolei. Wiemy także, czy w ogóle takie słowo istnieje w serwisie, sprawdzając to w tabeli ze słowami zaindeksowanymi. Czy lepiej byłoby jednak nie rozdzielać tego w tabeli wyszukiwania i użyć fulltext search?

[elektrrrus]

Na obecnym etapie rozwoju i łatwości implementacji silników wyszukiwania takich jak apache solr/sphinx/xapian moim zdaniem budowanie większych wyszukiwarek w bazie danych nie ma najmniejszego sensu.

[ShadowD]

Wszystko to działa na serwerze, a co jeśli nie mamy dedyka?

@adam_c wybacz, że się w trącam, ciekaw jestem tego tematu, a jeśli uda się coś znaleźć przy okazji będziesz mieć łatwiej. :-)

[adam_c]

Witam,
Dzięki za odpowiedzi

Skrypt będzie działał na hostingu współdzielonym, tak więc mam ograniczone możliwości.

Zaktualizowałem nieco bazę i zapytanie i jest prawie dobrze. W tej chwili tabele bazy wyglądają tak:

Tabela "artykuly":
id | nazwa | tresc | dodatkowa_waga | top | aktywny

Tabela "search_indeksy":
id | slowo

Tabela "search_rodzaje_elementow":
id | nazwa | waga

Tabela "search_wyszukiwanie":
id | id_matki | numer_elementu | rodzaj_elementu | indeks

Samo zapytanie brzmi tak (pomijam wyszukiwanie indeksów, wcześniej pobrałem je do tablicy $wyszukiwane):

[SQL] pobierz, plaintext 
SELECT DISTINCT(t2.id) AS id, COUNT(t2.id) AS s_ilosc,SUM(t3.waga) + t2.dodatkowa_waga AS s_waga, t2.nazwa, t2.tresc FROM `search_wyszukiwanie` AS t1, `artykuly` AS t2, `search_rodzaje_elementow` AS t3 WHERE t2.aktywny = 1 AND t3.id = t1.rodzaj_elementu AND t1.id_matki = t2.id AND t1.indeks IN(".implode(',',$wyszukiwane).") GROUP BY t1.numer_elementu HAVING s_ilosc = ".count($wyszukiwane)." ORDER BY t2.top DESC, s_waga DESC
[SQL] pobierz, plaintext 

Wszystko byłoby super, gdyby nie pierwsza część czyli DISTINCT(t2.id). Wydawało mi się, że powinno to działać tak, że nie pobierze mi np. artykułu 1, a tak się nie dzieje, a raczej dzieje się ale nie do końca. Powiedzmy, że znaleziono wyszukiwane słowa w dwóch tagach artykułu 1 oraz w jego nazwie. Logiczne byłoby pozostawienie wyszukania z taga (wyższa waga), pominięcie drugiego oraz tego z nazwy. W praktyce jednak drugi wyszukany element (drugi tag) zostaje pominięty, jednak element wyszukany po nazwie pozostaje. Wygląda na to, że zapytanie odrzuciło drugi wyszukany element po tagu, bo był całkowicie identyczny, a w trzecim zmieniła się waga (nazwa ma mniejszą) więc jest już coś innego i nie wywala go. Nieco namieszałem ale mam nadzieję, że wiadomo o co chodzi.

Jak to rozwiązać? Macie pomysły?



EDIT
------------------------

Witam ponownie,

Udało mi się napisać odpowiednie zapytanie. Nie jest może ono za krótkie, ale zdaje egzamin i pobiera co trzeba:

[SQL] pobierz, plaintext 
SELECT t4.id, t4.nazwa, t4.tresc, t3.s_waga + t4.dodatkowa_waga AS s_waga FROM (SELECT COUNT(t1.id) AS s_ilosc, MAX(t2.waga) AS s_waga, t1.id_matki AS s_id FROM `search_wyszukiwanie` AS t1, `search_rodzaje_elementow` AS t2 WHERE t1.indeks IN(".implode(',',$wyszukiwane).") AND t2.id = t1.rodzaj_elementu GROUP BY t1.numer_elementu HAVING s_ilosc = ".count($wyszukiwane).") AS t3, `artykuly` AS t4 WHERE t4.id = t3.s_id AND t4.aktywny = 1 GROUP BY t3.s_id ORDER BY t4.top DESC, s_waga DESC 
[SQL] pobierz, plaintext 

Co o tym sądzicie? Nie wspomniałem wcześniej, że "numer_elementu" w tabeli "wyszukiwanie" składa się z id_matki i kolejnego numeru elementu (np. 1_1 dla pierwszego taga artykułu 1).

Teraz wystarczy tylko LIMIT i można dzielić na strony. Czy pobranie ilości wszystkich znalezionych artykułów drugim zapytaniem z COUNT ale bez ORDER będzie najbardziej odpowiednie, czy jest jakieś lepsze rozwiązanie?

Pozdrawiam

Ten post edytował adam_c 29.12.2011, 10:21:45