Zaawansowana wyszukiwarka w php i mysql

W dzisiejszym wpisie skupimy się na wyszukiwaniu danych w bazie MySQL. Jeśli tutaj trafiliście, to zapewne szukacie podpowiedzi jak najlepiej to wykonać. Bez obaw, postaram się wszystko wyjaśnić krok po kroku.

Sposoby wyszukiwania

MySQL oferuje w zasadzie dwa sposoby szukania danych: poprzez dokładne odwzorowanie oraz poprzez dopasowanie częściowe. Jako częściowe mam na myśli warunek zapisany jako LIKE '%szukane_slowo%’ (bądź LIKE 'szukane_slowo%’). Dopasowanie dokładne nie wymaga zbyt wiele uwagi ze strony bazy (o ile jest odpowiedni indeks wstawiony na kolumnę). Natomiast w przypadku dopasowania częściowego zaczynają się pojawiać problemy. Przede wszystkim baza nie jest w stanie skorzystać z indeksów. To oznacza jedno: wszystkie rekordy będą wzięte pod uwagę (dotyczy także REGEXP). Jeśli tabela jest mała nie robi to zbyt wielkiego problemu. Niestety wyszukiwarka zazwyczaj ma działać na większej liczbie elementów i dlatego też rozwiązanie to może dość mocno obciążać serwer. Problem robi się jeszcze większy, gdy wyszukiwarka działa z podziałem na strony oraz gdy za każdym razem przeszukuje zestaw danych w celu dopasowania rekordów spełniających podane przez użytkownika ograniczenia. Z tego powodu rozwiązanie będzie skupiało się na ograniczeniu wpływu wyszukiwania na serwer. Oczywiście można zastosować sphinxa lub fulltext, ale nie zawsze jest ku temu sposobność. Dla sphinxa trzeba mieć dedykowany serwer (lub znajomego admina), a do fulltexta spełniać warunki na ilość szukanych znaków oraz udział dopasowań w ogólnym zestawie danych.

Jedno szukanie, wielokrotne wyświetlanie z podziałem na strony

Użytkownikowi przeważnie nie robi różnicy czy dopasowania mają kilka minut, czy kilka godzin (lub nawet dni). To co jest najnowsze zawsze pojawia się gdzieś na głównej stronie, natomiast cała reszta siedzi gdzieś poukrywana na kolejnych podstronach archiwum. Dlatego też w przypadku tych samych warunków wyszukiwania możemy podsunąć różnym osobom te same zestawy danych (oczywiście pod pewnymi warunkami jak liczba minut/godzin/dni, które minęły od poprzedniego wyszukiwania). W ten sposób dochodzimy do rozwiązania: dwie tabele.

Pierwsza tabela szukaj zawierać będzie warunki wyszukiwania, datę oraz unikalny numer (autoincrement int). Natomiast druga, powiedzmy szukaj_wyniki, składać się będzie z id dopasowanych elementów oraz z id z tabeli szukaj. W ten sposób załatwimy wielokrotne wyszukiwanie tego samego zestawu danych oraz odciążenie serwera.  Podział na strony na danych zawartych w szukaj_wyniki będzie zdecydowanie szybszy, a na upartego możemy tabelę skonstruować tak, żeby ona sama zawierała już podział na strony (ewentualnie umieścić możemy także ocenę dopasowania).

Konstruowanie wyszukiwarki na stronę

Pierwszą myślą jest to, że zapytanie będzie skomplikowane. Trzeba będzie doklejać kolejne fragmenty i upewniać się, że zapytanie nie popsuło się. Na szczęście rozwiązanie nie jest aż tak trudne w realizacji i nie wymaga zbytniej ekwilibrystyki. Po pierwsze: szukanie zazwyczaj nie wymaga stosowania OR. Po drugie, warunki da się połączyć w jedną całość stosując nie tylko where ale także inner join. Where działa niejako „po fakcie”, natomiast inner join pozwala na ograniczenie zestawu danych już na etapie łączenia.

Zasada jest dość prosta. Tworzymy dwie tablice: $where oraz $inner. Do tej pierwszej wstawiamy ogólne warunki, do tej drugiej złączenia z pozostałymi tabelami. Przykładowo może to być:

if( !empty( $x )) $where[] = " t.id = '$x' " ;
if( !empty( $y )) $inner[] = " inner join tabela2 on tabela.a = tabela2.b and tabela2.c = '$y' " ;

if( !empty( $where )) $where_str = " where ".implode( " and ", $where ) ;
if( !empty( $inner )) $inner_str = implode( " ", $inner ) ;

Wystarczy teraz przekazać $where_str oraz $inner_str do zapytania pobierającego dane i sprawdzić czy uzyskany zestaw danych zawiera tylko te dane, które spełniają zadane warunki. Jeśli wszystko jest tak jak trzeba, to możemy przejść do właściwego kroku. Na początku zapisujemy nowe wyszukiwanie do tabeli szukaj, a następnie przy pomocy funkcji mysql_insert_id() pobieramy $id wstawionego rekordu i wstawiamy do tabeli szukaj_wyniki:

$query = "insert into szukaj_wyniki ( id_szukania, id_dopasowania )
select $id, id from tabela $inner_str $where_str" ;

Teraz możemy przeskoczyć do strony prezentującej wszystkie dopasowania. W adresie należy umieścić $id, żeby skrypt wiedział które wyniki ma pokazać.

Pokazywanie wyników

Jesteśmy już prawie u celu. Przeglądarka przeskakuje na stronę z wynikami. Teraz wystarczy odczytać id z adresu url i przekazać ten parametr do funkcji pobierającej dane:

$query = "select kolumny from tabela
inner join szukaj_wyniki as sw on sw.id_dopasowania = tabela.id and sw.id_szukania = '$id'" ;

Inner join spowoduje, że mysql zastosuje zawężenie do danych spełniających warunki wyszukania. Jeśli ma być podział na strony, to albo w linii z inner join wstawiamy numer strony, albo robimy tradycyjne ograniczanie liczby wyników przy pomocy limit start, liczba. W tym drugim przypadku warto zrobić wstępne rozpoznanie i ograniczyć się do 2-3 stron.

Ograniczenie ilości danych siedzących w tabelach szukania i wyników

Samo wyszukiwanie i pokazywanie to nie wszystko. Zadbać należy także o to, żeby tabele służące nam do wyszukiwania danych zajmowały rozsądną ilość miejsca. Za każdym razem, gdy użytkownik wykona operację szukania do tabeli szukaj dodawany jest jeden rekord, a do tabeli szukaj_wyniki tyle rekordów, ile zostało znalezionych dopasowań. Wystarczy odpowiednio dużo osób, lub dość długie funkcjonowanie strony, a obydwie tabele osiągną limit wielkości (czy to nałożony przez firmę hostingową, czy to fizyczny związany z wielkością dysku). Z tego też powodu dobrze jest od razu zadbać o to, żeby tabele były przycinane do rozsądnych rozmiarów.  Możemy posłużyć się następującym kodem:

$data = date( 'Y-m-d', strtotime( 'Today -1 day' )) ;
$query = "delete s, sw from szukaj s
left join szukaj_wyniki sw on s.id = sw.id_szukania
where s.data < '$data'" ;

Kod ten spowoduje usunięcie rekordów starszych niż jeden dzień.  Odpalać możemy go poprzez crona raz na dzień, przy każdym wyszukiwaniu lub po wylosowaniu odpowiedniej liczby przy wyszukiwaniu. Opcja z cronem nie jest dostępna na każdym serwerze, a każdorazowe kasowanie wydaje się być zbytnią przesadą (i niepotrzebnie zajmuje czas bazie danych, nawet jeśli nie ma czego skasować). Dlatego też najrozsądniejszym rozwiązaniem będzie ta trzecia opcja, czyli kasowanie podczas wyszukiwania, ale przy spełnieniu warunku na wartość wylosowanej liczby. Wstawiamy losowanie liczb od 1 do 10 i wykonujemy kasowanie tylko wtedy, gdy wylosowane zostaną liczby 1 lub 2. Mechanizm losujący nie jest idealny, ale takie podejście powinno zapewnić ograniczoną liczbę dziennych kasowań.

• Tweet
• Pin It