Relacja wiele do wielu, wyszukiwanie w kilku tabelach jednym zapytaniem Opcje

[mowgli91]

Witam serdecznie.

Tworząc panel zamówień z książkami natknąłem się na spory problem z wydajnością.

Dla zawężenia sprawy, przyjmijmy, że mam 3 tabele: ksiazki, autorzy, linkksiazkaautor. Pierwsza zawiera listę książek, druga listę autorów, trzecia powiązania między książką i autorem. Czyli jak widać, zachodzi relacja wiele do wielu (jedna książka może mieć kilku autorów, jeden autor może napisać kilka książek).

Z pobieraniem listy książek nie mam żadnego problemu: do listy książek doczepiam za pomocą podzapytania pole, które zawiera autorów danej książki wymienionych po przecinku (podzapytanie SELECT GROUP_CONCAT()).

Schody zaczynają się, jeżeli chcę wyszukać książki po autorach (wpisuje słowa kluczowe, rozdzielam je, każde z osobna przelatuje po bazie i pobieram wyniki gdzie wszystkie słowa zawierają się w danym autorze). Obecnie rozwiązanie ułożyłem, ale jest dość niewydajne:

[SQL] pobierz, plaintext 
SELECT `ka`.`ksiazkaId` AS `id` FROM `linkksiazkaautor` AS `ka` RIGHT JOIN `autorzy` AS `a` ON a.id = ka.autorId WHERE (a.nazwa LIKE '%slowoKluczowe%')
[SQL] pobierz, plaintext 

Powyższe zapytanie pobiera listę zawierającą ID książek, które są powiązane z autorami pasującymi do danego słowa kluczowego. Z pobranej listy jest tworzony ciąg warunków dla kolejnego zapytania (do tabeli książek), w którym uwzględnione są również inne kryteria wyszukiwania (po wydawnictwie, po ISBN itp.).

A co w przypadku jeżeli prawie każdy autor ma w sobie literę "a" i użytkownik zażąda takiego wyszukania? Stworzony zostanie ciąg kilkudziesięciu tysięcy warunków, i takie zapytanie z miejsca zawiesza bazę danych i serwer.

Nie mogę wykonać żadnego JOIN'a do powyższego zapytania, bo inne kryteria wyszukiwania muszą się odnosić do pełnej listy książek, a nie tylko tej pasującej do autorów.

Mogę włączyć ograniczenie do minimum 4 znaków w słowach kluczowych i wtedy to jakoś znośnie chodzi, ale wiadomo że to jest kombinowanie dookoła...

Spotkał się ktoś z forumowiczów z podanym problemem? Byłbym wdzięczny za jakiekolwiek sugestie.

Pozdrawiam,
Kasper Warguła.



//Dodano po kilku godzinach:


Widzę, że nikt nie ma pomysłu. Ja poszedłem trochę do przodu i użyłem JOINów i GROUP_CONCAT (jednak się da):

[SQL] pobierz, plaintext 
SELECT 
	GROUP_CONCAT( a.nazwa SEPARATOR ', ') AS autorzy, 
	k.id, 
	k.tytul, 
	k.stanDostepny, 
	k.cenaSprzedazyDetal, 
	k.vatSprzedaz, 
	k.opis, 
	k.isbn, 
	k.liczbaStron, 
	k.rokWydania,
FROM
	ksiazki AS k
LEFT JOIN
	linkksiazkaautor AS ka ON k.id = ka.ksiazkaId
LEFT JOIN
	autorzy AS a ON a.id = ka.autorId
GROUP BY k.id
[SQL] pobierz, plaintext 

Zapytanie jest OK. Tylko, że wykonuje się ogromnie długo. po dodaniu klauzuli WHERE k.id < 30 zapytanie trwa 3.4 sekundy, i rośnie w miarę rozrzeszania zakresu w warunku. Dodanie klauzuli LIMIT, nawet 1 nic nie zmienia.

Nie wiem jak Wy, ale ja już nie mam zielonego pojęcia, jak to rozwiązać...

Ten post edytował mowgli91 6.05.2011, 17:25:20

[everth]

A może byłbyś taki dobry i załączył deklaracje tabel?

[pmir13]

Wątek założyłeś w związku z wolnym wyszukiwaniem po zadanej frazie, teraz przedstawiasz zapytanie które tworzy listę książek z autorami, bez żadnej wyszukiwanej frazy, o co w ogóle pytasz? Zapytanie jest wolne, bo najpierw wszystko wrzucasz do wspólnego worka, dostając wielką połączoną tabelę na której później robisz grupowanie, zgaduję tylko że w explainie type jest ALL, rows idzie w kosmos, a w extra siedzi Using filesort, choćbyś nie wiem jak kombinował z indeksami.

[mowgli91]

Wyszukiwanie frazy w drugim przypadku to tylko dołączenie WHERE, chodzi o sam mechanizm wyszukwiania który działa tragicznie wolno.
Deklaracja tabel została w skrócie podana, 3 tabele i opisane połączenia. Nie chodzi mi o wyszukiwanie konkretnych pól tylko na ogólnym problemie.

Wychodzi na to, że do optymalnego wyszukiwania użyję Lucyny w Zendzie.

[everth]

Myślę że odpowiedź na twoje pytanie to 42. Nie pytaj skąd wiem.