Już dawno temu odkryłem potęgę funkcji warunkowych w SQL, tak dawno, że zapomniałem czemu uważam je za potężne. Odświeżyłem sobie tę wiedzę podczas rozwiązywania problemu statystyk, a przybliżę ją Tobie na przykładzie forum. Pytanie brzmi: jak optymalnie pobrać i przetworzyć dane z funkcji agregujących pochodzące z kilku tabel (tłum. fajnie by było jakbym widział kto i ile razy wypowiadał się w jakich tematach i żeby serwera nie zamuliło).
Założenia:
- moje testowe “forum” składa się z dwóch tabel, tabeli “subjects” [id, subject] w której przechowuję identyfikator i temat wątku (np. tytuł newsa) oraz tabeli “replies” [id, user_id, subject_id, reply] przechowującej odpowiedzi (reply) użytkownika (user_id) w danym temacie (subject_id)
- identyfikatory użytkowników (user_id), których wypowiedzi szukam to “666″, “777″ i “12″
- interesuje mnie wydruk listy tematów w których użytkownicy brali udział z ilością wypowiedzi w każdym z tematów
- zachmurzenie jest umiarkowane
Pierwszy pomysł jak to zrobić wygląda mniej więcej tak:
s.subject, r.user_id
FROM
subjects s
LEFT JOIN
replies r ON s.id = r.subject_id
WHERE
r.user_id IN(12,666,777)
teraz możemy wynik przepuścić przez pętlę sumującą w zmiennej tablicowej ilość wypowiedzi dla każdego użytkownika (zakładam, że wyniki zapytania SQL wrzuciłeś do zmiennej $replies)…
… i wyświetlić w odpowiedni sposób. Gra, ale czy jest optymalnie? Za pomocą zapytania SQL pobierasz mnóstwo danych, zajmujesz pamięć przechowaniem ich i procesor przetwarzaniem za pomocą pętli “foreach”. Ja zrobiłem to za pomocą funkcji warunkowych i agregacji:
s.subject,
COUNT(IF(r.user_id = 666,1, NULL)) AS replies_666,
COUNT(IF(r.user_id = 777,1, NULL)) AS replies_777,
COUNT(IF(r.user_id = 12,1, NULL)) AS replies_12
FROM
subjects s
LEFT JOIN
replies r ON s.id = r.subject_id
GROUP BY
s.id
HAVING
replies_666 + replies_777 + replies_12
“COUNT(IF(r.user_id = 666,1, NULL)) AS replies_666” działa jak iterator na zmiennej “replies_666″, zwiększa się ona, gdy warunek “r.user_id = 666″ jest prawdziwy
“HAVING replies_666 + replies_777 + replies_12” filtruje wyniki do tematów, w których któryś z szukanych użytkowników wypowiadał się
Minusy:
- obciążenie serwera skomplikowanym zapytaniem
Plusy:
- mniej informacji przesyłanych z serwera SQL do przetworzenia w PHP
- brak potrzeby przetwarzania wyników SQL w pętli i ich buforowania w dodatkowych zmiennych
- ktoś widzi składnię SELECT … COUNT(IF(… w Twoim kodzie i mdleje z wrażenia
Przykład z forum i wyszukiwaniem wypowiedzi użytkowników jest banalny, jak już wcześniej pisałem, przypomniałem sobie o tym przy okazji pisania skryptów do statystyk i zaoszczędziło to mojemu programowi kilku dodatkowych zapytań SQL, pętli i zmiennych pomocniczych. Zachęcam Cię do testowania różnych kombinacji funkcji SQL z różnymi funkcjami warunkowymi, efekty mogą być świetne a Twój skrypt może zacząć działać szybciej, kod być bardziej przejrzysty, bez zbędnych zmiennych tymczasowych i dodatkowych zapytań SQL. Aloha.
05 10 2007 at 10:46 am
a czemu nie tak pomiędzy jednym a drugim rozwiązaniem?
SELECT s.id, s.subject, r.user_id, COUNT(*)
FROM subjects s LEFT JOIN replies r ON s.id = r.subject_id
WHERE r.user_id IN(12,666,777)
GROUP BY s.id, s.subject, r.user_id
05 10 2007 at 11:45 am
Równie dobre jeżeli chodzi o rozwiązanie tego konkretnego zadania, chociaż też trzeba będzie zwrócony wynik trochę obrobić. Nie za ciekawy przykład dałem, po małym zmodyfikowaniu będzie bardziej klarowne o co mi chodziło. Zastosowałem to w statystykach:
SELECT
s.subject,
COUNT(IF(r.user_id = 666,1, NULL)) AS replies_666,
COUNT(IF(r.user_id = 777,1, NULL)) AS replies_777,
COUNT(IF(r.user_id = 12,1, NULL)) AS replies_12,
COUNT(*) AS subject_replies
FROM
subjects s
LEFT JOIN
replies r ON s.id = r.subject_id
GROUP BY
s.id
HAVING
replies_666 + replies_777 + replies_12
“subject_replies” zawiera liczbę wszystkich odpowiedzi w danym temacie.
Poza tym w warunkach IF() możesz dodawać jakie chcesz porównania, po złączeniu z tabelą użytkowników możesz pobierać jednym zapytaniem ile kobiet w wieku poniżej 30 lat wypowiadało się na dane tematy, ile w tym samym temacie wypowiadało się wąsatych mężczyzn powyżej 40 roku życia, ile ogólnie było wypowiedzi i co Tobie jeszcze potrzeba do statystyk. Po umieszczeniu warunku w SUM() wachlarz zastosowań się zwiększa. Im więcej kombinuję, tym bardziej warunki w SQL mi się podobają. Idę napisać sobie jakieś zapytanie. Pozdrawiam.
05 06 2008 at 9:32 am
Czy instrukcje warunkowe działaja na MySQL 4 ?
Fajny artykuł
Pozdrawiam
05 06 2008 at 10:11 am
Dam sobie obciąć paznokieć że korzystałem z nich pracując na MySQL 4.1, ale to było kilka lat temu a w tej chwili nawet nie mam gdzie ich przetestować. W komentarzach na MySQL w wersji 4.1 podawane są przykłady łączenia funkcji agregujących z warunkowymi, więc pewnie nie będzie problemu:
http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html
Dzięki za miłe słowo, pozdrawiam.
17 06 2008 at 6:15 pm
Mam takie małe pytanie: czy takie zapytanie nie obciąża za bardzo serwera? to znaczy czy nie bardziej optymalnie jest przetworzyć to po stronie PHP? Pytam bo sam jestem ciekawy.. robiłeś może jakieś testy kiedyś?
19 06 2008 at 11:51 am
Nie robiłem testu więc nie będę zgadywał, ale odpowiedź na to pytanie jest oczywista - “to zależy”:
1. od ilości zapytań do różnych tabel, bo złączenie trzech tabel o wielkości kilkudziesięciu tysięcy rekordów (przy średniej wielkości serwisu to nie jest duża liczba) i pobranie z nich rekordów lub zagregowanie ich na pewno będzie bardzo obciążające, więc chyba lepiej wykonać oddzielne zapytania agregujące na każdą z tabel.
2. od ilości danych wysyłanych z serwera baz danych do PHP, bo jeżeli rekordów będzie 100 000, to tyle trzeba przesłać i obrobić (zalecam w tym wypadku pytanie agregujące lub kilka agregujących - patrz punkt 1)
3. i oczywiście od tego, co dalej chcemy zrobić z danymi. Najlepsze wg mnie jest podejście “pobieram z bazy danych to, co jest mi potrzebne”. Jeżeli jest to bardzo obciążające, staram się cachować wynik z bazy po stronie php lub tworzę w bazie danych oddzielną tablicę w której “denormalizuję” wpisy, aby nie wykonywać dodatkowych złączeń.
Jak znajdę więcej czasu to bardzo chętnie potestuję różne podejścia, ale jak zauważyłeś - wyniki będą sensowne dla konkretnej struktury bazy danych / ilości danych / potrzeby informacyjnej. Jeżeli interesuje Cię optymalizacja kodu php, polecam strony http://phplens.com/lens/php-book/optimizing-debugging-php.php oraz http://www.phpbench.com
Pozdrawiam
21 06 2008 at 2:02 pm
“tworzę w bazie danych oddzielną tablicę” => “tworzę w bazie danych oddzielną tabelę”, zawsze mi się to pieprzy…
03 04 2009 at 10:30 pm
Po pierwsze - rozwiazania oryginalnego nie polecam. Bo wymagana ono posortowania PELNEGO zbioru danych bedacych wynikiem zlaczenia dwoch uzywanych tabel (w tym oczywiscie rekordow niezwiazanych z uzytkownikami o id = 666, 777 i 12) - generalnie niepotrzebne sortowanie mnostwa danych, ktore i tak pozniej nie licza sie w ogolnym podsumowaniu. Rozwiazanie z komentarza matmisia jest duzo lepsze a jesli nie podoba sie, ze wynik nie jest transponowany do jednego rekordu to mozna to zrobic dodatkowa warstwa group-by (operujaca juz na 3 rekordach, wiec pomijalna z punktu widzenia wydajnosci).
Po drugie - odnosnie komentarza nr 6, punkt 1: zlaczenie kilkudziesieciu tysiecy rekordow z trzech tabel nie powinno byc obciazajace nawet na domowym desktopie. Bo operacja zlaczenia to zazwyczaj “hash join”, ktorego zlozonosc jest prawie liniowa w stosunku do ilosci danych (napisalem: prawie - bo zalezy od wielu rzeczy, w tym ilosci dostepnej pamieci, jednak dla nawet 100.000 rekordow to nie powinno byc waskim gardlem na dowolnym sprzecie). Co wiecej, w klasie zapytan podobnych do tego z przykladu proces agregacji ma miejsce dopiero po zlaczeniu tabel (czyli zazwyczaj po znacznym ograniczeniu ilosci rekordow), nie polecam tutaj odwrocenia procesu - czyli najpierw agregowania a potem laczenia gdyz osiagniemy tym sposobem trzy byc moze spore procesy sortowania, zamiast jednego.
04 04 2009 at 12:04 pm
@eh - nie skupiaj się na rozwiązaniu tego konkretnego problemu - to jest przykład zastosowania zapytania warunkowego. Nie widzę sensu utrudniania przykładu tylko po to, żeby udowodnić że tylko to zapytanie byłoby słuszne - wrzuć zamiast COUNT inne funkcje agregujące (STD, AVG, VARIANCE). Tak jak napisałem, rozwiązanie matmisa jest dobre - rozwiązanie tego konkretnego problemu.
Jestem przekonany, że domowy desktop, na którym sam wywołasz zapytanie do bazy danych da sobie z nim radę - wrzuć to zapytanie na serwer, pomnóż je razy 1000 i sprawdź zużycie zasobów. Dokładnie tak, jak napisałeś - zależy od wielu rzeczy. Miałem kiedyś przyjemność pracować z kilkoma tabelami o wielkościach 100-200 tyś rekordów. Ich złączenia były bardzo obciążające, jedynie denormalizacja tabel przyniosła efekt (czyli punkt 3 komentarza nr 6)
Jeszcze raz - to jest przykład uświadamiający, że istnieją warunki, które można wykorzystać ale które nie są dobre same z siebie - dobre są w konkretnych zastosowaniach.
Dzięki za komentarz i pozdrawiam.