Inteligentne wyszukiwanie z kilku tabel, + stronicowanie Opcje

[Sztef89]

Witam, mam problem z zapytaniem. Chciałbym jednym zapytaniem przeszukać 7 tabel, wyniki posegregować po najbardziej trafnych i ustawić LIMIT x,10. Na razie zrobiłem SELECT'a dla dwóch tabel (bez stronicowania i sortowania wyników):

[SQL] pobierz, plaintext 
SELECT uniqid, tytul, tytul_oryg, rok FROM media_filmdata
WHERE MATCH(tytul,tytul_oryg) AGAINST('słowo szukane' IN BOOLEAN MODE)
UNION ALL
SELECT uniqid, tytul, tytul_oryg, rok FROM media_serialdata
WHERE MATCH(tytul,tytul_oryg) AGAINST('słowo szukane' IN BOOLEAN MODE)
[SQL] pobierz, plaintext 

Jakbym dodał limit na końcu to wyniki z drugiej tabeli by się wcale nie wyświetliły (nawet jakby słowo szukane było trafione w 100%) dlatego chciałbym posortować wyniki od najlepiej pasujących i wtedy dopiero ograniczyć wyświetlanie.

Przeszukałem już sporo stron, i sporo się dowiedziałem ale nie mogę znaleźć rozwiązania na własnie ten problem.
Z góry dzięki !


-- EDIT --

Trochę pokombinowałem i uzyskałem coś takiego:

[SQL] pobierz, plaintext 
SELECT uniqid, tytul, tytul_oryg, rok, MATCH(tytul,tytul_oryg) AGAINST('house') AS score FROM media_filmdata
WHERE MATCH(tytul,tytul_oryg) AGAINST('house')
UNION ALL
SELECT uniqid, tytul, tytul_oryg, rok, MATCH(tytul,tytul_oryg) AGAINST('house') AS score FROM media_serialdata
WHERE MATCH(tytul,tytul_oryg) AGAINST('house')
 
ORDER BY  `score` DESC
[SQL] pobierz, plaintext 

Ale od razu można zauważyć, że score wyników jest zależny od ilości wyników z danej tabeli. Chciałbym każdą tabelę ograniczyć aby nie "przytłumiła" wyników z drugiej tabeli ("sprawiedliwość"). Już prawie jest to co chce uzyskać ale to tylko prawie... :/

Czyli jak zrobić aby waga nie była uzależniona od ilości wyników TYLKO o samej trafności słowa ?

Ten post edytował Sztef89 7.11.2011, 14:30:48

[Sephirus]

Słyszałeś o czymś takim jak WIDOK w sql? jeśli nie zapoznaj się z tym i zastosuj (IMG:style_emoticons/default/wink.gif) (tyle powinno Ci wystarczyć)

Mały komentarz:

Jak już poczytasz to zrób widok złączenia tych 7 - miu tabel o których była mowa. Bez żadnych sprawdzań/szukań itp... po prostu zadbaj o to by widok stał się jedną wielką tabelą połączoną z tych 7. Wtedy do takiego widoku odwołujesz się jak do tablicy (IMG:style_emoticons/default/wink.gif)

Pamiętaj jednak - widok nie jest zapisywany oddzielnie jako tabela to tylko taki skrót - co oznacza, że to łączenie jest wykonywane najpierw a następnie twoje szukaniu w widoku więc postaraj się aby w widoku było jak najmniej pól - ze względów wydajności - tylko te, które potrzebujesz.

[Sztef89]

Szczerze jeszcze nie słyszałem o czymś takim (IMG:style_emoticons/default/smile.gif) Ale po lekturze to może być bardzo przydatne w moim projekcie.

Co do zastosowania widoku to mam główne pytania:

1. Przeszukiwanie z użyciem widoku polegałoby na zrobienie widoku z wszystkich 7 tabel, a następnie ten widok należałoby przeszukać metodą MATCH... AGAINST... ?
2. Nie trzeba robić indeksu do tego widoku aby go przeszukiwać powyższa metodą ?
3. Czy takie rozwiązanie byłoby wydajne ?
4. Czy stworzony widok jest tymczasowy czy raz stworzony można używać wiele razy ?
5. Co jak zawartości tabel się zmienią (dojdą nowe rekordy) - widok wtedy będzie zawsze "zaktualizowany" ?

Trochę dużo pytań wyszło ale odpowiedzi na nie rozwiążą moje wszystkie wątpliwości.

[Sephirus]

1. Przeszukiwanie z użyciem widoku polegałoby na zrobienie widoku z wszystkich 7 tabel, a następnie ten widok należałoby przeszukać metodą MATCH... AGAINST... ?

Dokładnie tak - rekordy w widoku będą traktowane jak z jednej tabeli - musisz jedynie zapewnić jednolitość pól.

2. Nie trzeba robić indeksu do tego widoku aby go przeszukiwać powyższa metodą ?

Index w widoku... hmm... Można - ale powiem szczerze nigdy tego nie robiłem (nie robiłem tak zaawansowanych widoków) - teoretycznie powinien to przyspieszyć.

3. Czy takie rozwiązanie byłoby wydajne ?

Nie całkiem. Dlaczego? To działa podobnie (albo nawet tak samo) jak podzapytania. Może nieco lepiej bo to jednak po stronie serwera się dzieje ale mimo wszystko polega na tym samym. Kwestia dobrania odpowiednich pól (po co wszystkie), pokombinuj z indexem jak wspomniałeś i powinno działać względnie. Musisz też zrozumieć, że takie zapytanie jest samo w sobie bardzo skomplikowane i czasowo i zasobowo.

Czy warto?
Tak. Dlaczego? Nie zrobisz tego inaczej - jeżeli chcesz wyszukiwać w wielu tabelach naraz tak by tabele nie były od siebie zależny ani nie były ustawione w jakiejś kolejności to musisz to wszystko wrzucić do jednego wora. Ten wór najłatwiej zastąpić widokiem - inaczej dałoby się napisać zapytanie szukające po tych tabelach ale byłoby cholernie długie i wydaje mi się - mniej wydajne (IMG:style_emoticons/default/wink.gif)

4. Czy stworzony widok jest tymczasowy czy raz stworzony można używać wiele razy ?

Widok to taka jakby wirtualna tabelka - istnieje w bazie i ma swoją nazwę. W wielu przypadkach można go traktować jak tabelkę (oczywiście tylko odczyt).
Pamiętaj tylko że jak bierzesz select z tabelki to masz z tego rekordy jak bierzez select z widoku to masz najpierw wywołanie zapytania widoku (zebranie rekordów) następnie dopiero z tej puli wykonywany jest select.

5. Co jak zawartości tabel się zmienią (dojdą nowe rekordy) - widok wtedy będzie zawsze "zaktualizowany" ?

Jeśli widok to tak naprawdę grupowanie wyników to jest zawsze aktualny - co prawda są chyba jakieś opcje bardziej zaawansowane ale nigdy ich nie używałem.

A teraz niespodzianka (IMG:style_emoticons/default/wink.gif)

Bo na pewno zastanawiasz się teraz: "Kurde tyle zachodu a i tak nie będzie super wydajne" - owszem - bo ogólnie tego się tak nie robi (IMG:style_emoticons/default/smile.gif)

Nie łączy się tabel z różnymi rzeczami jak się po nich wyszukuje tak po prostu - są do tego specjalne systemy.

A jak się to robi bez systemów - opcje są 2:

1. Można zrobić tabelkę główną z treściami w stylu ID | TYP | ID_WLASCICIELA | TRESC - wówczas masz wszystkie dane dotyczące wszystkiego - typ określa czy to news/post/artykuł itp, ID_WŁAŚCICIELA określa ID elementu do którego treść jest przypisana, no i sama TREŚĆ (IMG:style_emoticons/default/wink.gif)

2. Jeżeli masz wiele rodzajów rzeczy na stronie tak jak u Ciebie widzę seriale i filmy - można również zamiast 1 zapytania (bardzo kombinowanego) obejść problem szybciutko dodając select obok wyszukiwania z wyborem gdzie szukamy - kto powiedział że musi tam się znaleźć opcja "szukaj wszedzie" (IMG:style_emoticons/default/wink.gif)

3. Można zamiast jednego zapytania dać tyle ile mamy rodzaji szukanych elementów i wyświetlić je na przykład tak:

SERIALE:
a
b
c

FILMY:
a
b
c

Te sposoby mogą wydawać się śmieszne ale są wydajne i łatwe w obsłudze (IMG:style_emoticons/default/wink.gif)

[Sztef89]

Ciekawe te widoki, już wiem o co chodzi i myślę że takie wyszukiwanie po takim widoku na pewno nie będzie super wydajne... (45tys rekordów w filmach o ok 30tys w serialach)
Czyli w moim przypadku proponujesz zrobić odrębną tabelą zawierającą tylko wybrane kolumny z tych wszystkich 7 tabel ?
Bo w sumie z każdych tej tabel potrzeba mi: uniqid, tytul, tytul_oryg, rok, czyli tylko 4 kolumny. Wyniki wyszukiwania byłyby sprawiedliwe dopiero gdy to się wrzuci do jednego wora... fizycznie czy też wirtualnie (widoki).

Napisz czy dobrze zrozumiałem z tworzeniem tej jednej wielkiej tabeli (IMG:style_emoticons/default/smile.gif)

2. Jeżeli masz wiele rodzajów rzeczy na stronie tak jak u Ciebie widzę seriale i filmy - można również zamiast 1 zapytania (bardzo kombinowanego) obejść problem szybciutko dodając select obok wyszukiwania z wyborem gdzie szukamy - kto powiedział że musi tam się znaleźć opcja "szukaj wszedzie"

To by ułatwiło dużo spraw ale te rozwiązanie nie przejdzie w moim projekcie. Musi być przeszukiwanie po dwóch kolumnach z każdej tabeli jednocześnie dołączając informacje o id oraz rok.

3. Można zamiast jednego zapytania dać tyle ile mamy rodzaji szukanych elementów i wyświetlić je na przykład tak:

hymm... (IMG:style_emoticons/default/smile.gif)


Chyba skorzystam z tej 3 propozycji - nawet chyba będzie lepiej a na pewno wydajniej i bez kombinowania. Jeszcze moi drodzy mam jedno pytanie.
Przy zapytaniu SELECT i ograniczeniu go przez LIMIT x,10 dostaniemy co najwyżej 10 rekordów zaczynając od x. Jak zastosujemy funkcje COUNT(kolumna) to zliczy nam do 10. Jest jakaś funkcja która zliczy nam ile jest rekordów pod to zapytanie bez wywoływania drugi raz tego samego selecta bez limitu z funkcją COUNT(kolumna) ?

Ten post edytował Sztef89 7.11.2011, 17:30:01

[Sephirus]

Wydaje mi się, że dobrze zrozumiałeś (IMG:style_emoticons/default/wink.gif) wariant 3 jest najłatwiejszy i nie wymaga dodatkowych działań (IMG:style_emoticons/default/wink.gif)

Co do pytania to niestety przy paginacji zawsze dwa zapytania ida parami.

Musisz mieć wyniki (stronę wyników) z limitem "pobierz X wyników od rekordu nr Y" oraz "Count(*)" dla wszystkich.

Z tego co mi wiadomo nie da się tego obejść - dopowiem tylko że zapytanie typu "SELECT count(*) AS liczba_filmow FROM filmy" przykładowo działa dość szybko (IMG:style_emoticons/default/wink.gif) nie musisz się tu martwić o wydajność (IMG:style_emoticons/default/wink.gif)

Grunt abyś nie uzywał czegoś w stylu MYSQL_NUM_ROWS() - bo to jest dopiero nie wydajne (IMG:style_emoticons/default/tongue.gif)

Jak zrobisz 2 zapytanka select + limit i select + count to będzie git (IMG:style_emoticons/default/wink.gif)

[Sztef89]

Wydaje mi się, że dobrze zrozumiałeś (IMG:style_emoticons/default/wink.gif) wariant 3 jest najłatwiejszy i nie wymaga dodatkowych działań (IMG:style_emoticons/default/wink.gif)

Co do pytania to niestety przy paginacji zawsze dwa zapytania ida parami.

Musisz mieć wyniki (stronę wyników) z limitem "pobierz X wyników od rekordu nr Y" oraz "Count(*)" dla wszystkich.

Z tego co mi wiadomo nie da się tego obejść - dopowiem tylko że zapytanie typu "SELECT count(*) AS liczba_filmow FROM filmy" przykładowo działa dość szybko (IMG:style_emoticons/default/wink.gif) nie musisz się tu martwić o wydajność (IMG:style_emoticons/default/wink.gif)

Grunt abyś nie uzywał czegoś w stylu MYSQL_NUM_ROWS() - bo to jest dopiero nie wydajne (IMG:style_emoticons/default/tongue.gif)

Jak zrobisz 2 zapytanka select + limit i select + count to będzie git (IMG:style_emoticons/default/wink.gif)

Dzięki za odpowiedź.

Nawet nie myślę o używaniu MYSQL_NUM_ROWS() bo to by było przeciwieństwo wydajności hehe (IMG:style_emoticons/default/biggrin.gif)
nie przyglądałem się jeszcze wydajności zapytania count(*) ale skoro mówisz że to ogólnie szybko działa to ok.

Moje problemy zostały rozwiązane. Dzięki za pomoc !