Ranking i sprawa wydajności Opcje

[spit]

Cześć.
Jak już pisałem na forum (może ktoś kojarzy) tworzę nieduży systemik do katalogowania i oceniania moich planszowych gier. Chciałbym, aby była możliwość oceniania ich przez odwiedzających (możliwe, że kiedyś to się rozrośnie ). W bazie mam tabelę "ratings", w której trzymam ocenę (pole ratings_rate) i identyfikator gry (ratings_gameid). Wpadłem na pomysł, by pobrać te dane do pliku i dodać im numery, tak:

[SQL] pobierz, plaintext 
	SELECT @i:=@i+1 nr, games_id, ratings_gameid, ratings_rate,
	COUNT(ratings_gameid) AS much,
	SUM(ratings_rate)/COUNT(ratings_gameid) AS all_ratings
 
	FROM games
 
	LEFT JOIN ratings ON games_id = ratings_gameid
 
	GROUP BY games_id
	ORDER BY all_ratings DESC
[SQL] pobierz, plaintext 

Następnie wrzucam wynik do pliku, z niego robię tablicę, przy wywoływaniu listy gier includuje go, i biorę "nr" odpowiadający identyfikatorowi. Teraz pojawia się problem. Jeżeli założymy, że baza gier się rozrośnie (na początek będzie tam zbiór mój i kilku kolegów ze studiów) i wyniesie (luźno liczymy) 500 pozycji, to jak będzie z wydajnością? Chcemy zrobić więcej rankingów, m.in. wg wydawcy i roku wydania, to wszystko to są zapytania, pobierające przecież pięćset rekordów.

Pomyślałem, żeby pobieranie rankingu zrobić w CRONie, raz na dobę. Ale wówczas problemem będzie to, że sortuję wyniki przez średnią arytmetyczną ocen. Mógłbym zamiast umieszczać wyniki w pliku, dawać je do bazy, ale problem główny leży w tym, czy zwykłe konto na serwerze "wydoli" z częstą aktualizacją takich rankingów, powiedzmy... no, szczerze, to przy każdej oddanej ocenie. A może macie jakiś inny sposób na ranking sortowany wg ocen?

Korzystam z ADOdb, gdyby to miało w czymś pomóc.

Ten post edytował spit 19.07.2010, 21:44:43

[Pawel16]

Nie wiem czy dobrze zrozumiałem ale odpowiem tak jak rozumiem.

Zamiast kombinować. Użyj przy wyjmowaniu rekordów agregowania(AVG() - średnia z liczb) oraz grupowania w MySQL. Dzięki temu obliczysz średnią z ocen danej gry. Następnie do takiego zapytania dodaj aby "wyjmowało" zapytanie według średniej z ocen, np.:

[SQL] pobierz, plaintext 
mysql_query("SELECT * FROM `games` GROUP BY `ratings_gameid` ORDER BY avg(ratings_rate) DESC");
[SQL] pobierz, plaintext 

Takie zapytanie o ile sie nie myle wyjmnie wszystkie gry w kolejnosci od największej do najmniejszej według ocen. Nie wiem czy dobrze dobrałem pola ;P.

[spit]

Zobacz do zapytania, ja już mam średnią arytmetyczną z ocen. Chodzi zupełnie o co innego, przeczytaj post jeszcze raz.

Więc, nikt nie jest w stanie mi pomóc? Nie wierzę, że pierwszy chcę zrobić ranking

[athabus]

Na początek powiem, że nie wiem czy do końca rozumiem Twój problem, bo nie opisałeś go jednoznacznie, albo ja dzisiaj już nie od końca kontaktuje.

Ogólnie to źle podchodzisz do tematu (pomijając już kwestie że wykonujesz zupełnie nieoptymalne zapytania do bazy). Wg. mnie w kwestii rankingów różnej maści sprawdzają się wyzwalacze - ranking zmienia się tylko wtedy gdy ktoś dodaje ocenę więc najlepiej ustawić wyzwalacz, który obliczy średnia ocenę dla danej gry po każdym dodaniu oceny i zapisze ją w bazie. Cachowanie wyników w plikach imho w takiej sytuacji jest zupełnie bez sensu i nic nie daje.

Co do Twojego drugiego pytania to raczej 500 w miarę prostych selectów nie będzie problemem dla dobrej jakości konta o ile będzie to oczywiście kwestia jednorazowa wywoływana z crona w nocy - niemniej przez palce by mi nie przeszedł kod generujący taką liczbę zapytań :-) Pomyśl raczej nad jakimś lepszym algorytmem wykonania tego zadania.

[spit]

Jakością zapytań teraz w ogóle się nie martwię, bo na razie jestem na etapie budowania szkieletu, dopiero potem będę się bawił w optymalizację całości. Możesz mi powiedzieć coś więcej o tych wyzwalaczach? Masz tu na myśli normalny isset i wstawianie do bazy? To byłyby dwa zapytania, SELECT i INSERT (chyba, że da się je rozwiązać za jednym razem?). Aha, no i jeżeli miałbym to tak robić, to od razu chciałbym ustalić pozycję w rankingu, jak mogę to zrobić?

Ogólnie problem jest taki, że chcę zrobić ranking gier taki jak tutaj, z tą różnicą, że układałbym tylko wg średniej (dla gier mających więcej niż X ocen), a tam jest inny algorytm. Problem leży głównie w tym, że taki ranking jest w jakiś sposób dynamiczny. Jeżeli dziennie padnie X ocen, a ja będę generował ranking raz na dobę, to to może być troszkę niewiarygodne.

[athabus]

ad1) Wyzwalacze to taki mechanizm, który powoduje wywołanie pewnych operacji na bazie w momencie zajścia zdarzenia. Programuje się je bezpośrednio w bazie danych. Dzięki temu możesz napisać np. wyzwalacz, który zawsze po dodaniu oceny spowoduje naliczenie średniej dla danej pozycji.
W sieci jest sporo na temat wyzwalaczy (trigger) i procedur - pamiętaj tylko, że są one obsługiwane tylko w mysql 5.x
Zamiast wyzwalaczy możesz zrobić to ręcznie dopisując odpowiednie zapytania w metodzie, która obsługuje dodanie oceny, ale wyzwalacze są bardziej eleganckie wg. mnie. Poza tym są wywoływane automatycznie, więc nie trzeba pamiętać o dopisywaniu tego samego kodu do każdej operacji zmieniającej tabelę z ocenami

ad2) Co do rankingu to źle podchodzisz do sprawy - jeśli będziesz chciał przy każdej ocenie ustalić pozycję rankingową dla każdej pozycji to będzie to zawsze wywołanie wielu zapytań typu update. Znacznie lepszym rozwiązaniem w Twojej sytuacji będzie:
- przechowywanie średniej dla danej gry w bazie i aktualizowanie jej po dodaniu nowej oceny (1 zapytanie) - dodaj po prostu 1 pole do tabeli w której przechowujesz gry o nazwie "srednia"
- w celu wyciągnięcia rankingu używasz zwykłego "order by srednia"

Sortowanie po liczbach w mysql jest bardzo wydajne więc spokojnie możesz to stosować bez obaw o wydajność.

[thek]

Chodzi o wyzwalacze bazy danych, nie kod php po stronie serwera. Tutaj wyzwalacz reagowałby na on insert dla tabeli ratings. Co do optymalizowania i pomysłu. Masz tabelę 'ratings'. Ma ona pola: id_gry, ocena i przynajmniej jeszcze jedno powinno być, by wyeliminować kilkukrotne głosowanie przez tego samego usera. Do tego dorzuciłbym tabelę game_summary z polami: id_gry, ilosc_glosow, srednia. Wyzwalacze zmieniały by wartości tych pól odpowiednio. Lepsze to niż count i avg za każdym razem.

EDIT: athabus był szybszy, ale chciałbym zwrócić uwagę na jedną rzecz... W przypadku gry najpewniej sama tabela podstawowych danych o grze będzie miała wiele kolumn, z których część nie jest konieczna do czytania za każdym razem i sensowne może być podzielenie jej na 2 lub 3 mniejsze, gdzie łącznikiem (kluczem) byłoby id_gry. Jako że dane gry jeśli chodzi o statystyki są pobierane zapewne często, to pod kątem optymalizacji wyciągnąłbym je do osobnej tabeli. Jeden JOIN LEFT by statystyki ewentualnie dołączyć do wyników głównej tabeli nie jest problemem.

A co do wywołań to oprócz triggerów, można jeszcze kombinować z eventami.

Powód edycji: [thek]: athabus był szybszy, ale dodam jeszcze coś ;)

[spit]

No dobrze, to rozumiem, a jeszcze pozycja w rankingu. Tu zdaje się być największy problem, bo zakładając nawet, że będę ją przechowywał w bazie, to i tak przy każdym głosie będzie trzeba a) pobrać wszystkie inne średnie sprawdzić pozycję tej średniej pomiędzy innymi c) wrzucić UPDATE do tabel z miejscami. I tego boję się najbardziej.

Z tymi triggerami to nie wiem za bardzo (nie czytałem jeszcze, brak czasu), czy mój host to obsłuży (zwykły shared account za stówkę rocznie), ale dzięki za pomysł.

[thek]

A popatrz, że zapis i odczyt do pliku tablicy za każdym razem to mnóstwo operacji. Masz id gry, ale znajdź ją w pliku Musisz wprowadzić jakiś sensowny sposób prezentacji i zapisu danych w pliku. Zapewne skończy się na XML, który jest po prostu inną formą zapisu danych. I tak będziesz musiał te dane z niego jeszcze jakoś obrabiać, z czasem modyfikować. Baza jest bardziej poręczna niż pisanie tego samemu. Poza tym co to za problem z updatem? Dla mnie to kwestia jednego prostego pomysłu. Podpunkty o jakich mówisz da się zrealizować inteligentnie Jak?
1. Musisz zapamiętać jaką pozycję ma obecnie rekord dla jakiego liczysz średnią. To $zapamiętana.
2. Musisz sprawdzić, ile jest rekordów ze średnią większą od Twojej po zmianach. Zapisz ją jako $nowa bo bo zaraz się przyda
3. Jeśli $nowa = $zapamiętana-1 to nie ruszyłeś się w rankingu.
4. Jeśli $nowa > $zapamiętana-1 to spadłeś i musisz wszystkie rekordy z miejscami od $zapamiętana+1 do $nowa zwiększyć o 1 a w obliczanym rekordzie dać pozycję rankingową = $nowa
5. Jeśli $nowa < $zapamiętana-1 to podskoczyłeś i musisz wszystkie rekordy z miejscami od $zapamiętana-1 do $nowa zmniejszyć o 1 a w obliczanym rekordzie dać pozycję rankingową = $nowa
Mówisz, że tych UPDATE będzie makabryczna ilość? Wątpię. Nie zmieniasz bowiem za każdym razem wszystkich rekordów tabeli tylko określony zakres wierszy. Po prostu trzeba mieć pomysł jak podejść problem w elegancji sposób, a nie tępym liczeniem za każdym razem wszystkiego jak leci. Zauważ, że podpunkt C, ktorego się najbardziej obawiasz można rozwiązać bardzo elegancko i angażując niemal minimalnie bazę. W określonych przypadkach nawet do UPDATE'u nie dojdzie (zmiana średniej nie wpłynie na pozycję). Na kartce sobie ten mój algorytm sprawdź i sam zobaczysz jak on zmniejsza ilość rekordów zaangażowanych w operacje aktualizacji. W najkorzystniejszym przypadku zmian tylko 2 rekordy się "wymienią" pozycjami.