[MySQL]długo wykonujące się podzapytanie cz. 2 Opcje

[jacusek]

Witam.
2 dni temu walczyłem z jednym podzapytaniem i mi się udało. Niestety w momencie, kiedy dodaje drugie podzapytanie mysql bije wszelkie rekordy w długości zapytań
mam taką tabelę:

[SQL] pobierz, plaintext 
CREATE TABLE `lek_pac` (
	`id_pow` INT(10) NOT NULL AUTO_INCREMENT,
	`id_pac` INT(3) NOT NULL,
	`id_lek` INT(3) NOT NULL,
	PRIMARY KEY (`id_pow`),
	INDEX `id_pac` (`id_pac`),
	INDEX `id_lek` (`id_lek`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=0
[SQL] pobierz, plaintext 

Łączącą pacjenta z lekarzem, który się nim opiekuje. Oczywiście może występować sytuacja, kiedy jeden pacjent "przypisany" jest do więcej niż jednego lekarza. Na to znalazłem sposób:

[SQL] pobierz, plaintext 
SELECT id_pac,id_lek lekarz1,
(SELECT id_lek FROM lek_pac WHERE id_lek !=(lekarz1) HAVING count(id_pac)>1) lekarz2
 FROM 
  (SELECT * FROM lek_pac GROUP BY id_pac HAVING count(id_pac)>1) AS t2
[SQL] pobierz, plaintext 

Ale w momencie kiedy mam sytuację, ze jednej pacjent jest pod opieką więcej niż dwóch lekarzy wówczas zaczyna się problem. Idąc za taką logiką rozumowania zrobiłem następujące pytanie:

[SQL] pobierz, plaintext 
SELECT id_pac,
id_lek lekarz1,
(SELECT id_lek FROM lek_pac WHERE id_lek !=(lekarz1) HAVING count(id_pac)>1) lekarz2,
(SELECT id_lek FROM lek_pac WHERE id_lek NOT IN(lekarz1,lekarz2) HAVING count(id_pac)>1) lekarz3
 FROM 
  (SELECT id_lek,id_pac FROM lek_pac GROUP BY id_pac HAVING count(id_pac)>1) AS t2
[SQL] pobierz, plaintext 

tutaj niestety już jest poważny problem, bo pytanie wykonuje się ponad 80 s. Oczywiście jest to niedopuszczalne. Zresztą nie było pożądanego efektu.
Kombinowałem z IFem, gdzie 4 kolumna miała wyglądać następująco

[SQL] pobierz, plaintext 
(SELECT (IF(id_lek NOT IN(lekarz1,lekarz2),id_lek,NULL)) FROM lek_pac HAVING count(id_pac)>2) lekarz3
[SQL] pobierz, plaintext 

ale to też niestety na nic. Kiedy dodaje group by id_pac w tym podzapytaniu, pytanie wykonywało się w nieskończoność zresztą bezskutecznie.
Potrzebuję zrobić zapytanie gdzie będzie mi sprawdzał do 5 lekarzy u pacjentów.
Nie chodzi mi oczywiście o rozwiązanie tylko o pomoc gdzie można jeszcze szukać lub gdzie popełniam błąd w rozumowaniu

[wookieb]

Kontynuuj w poprzednim temacie
http://forum.php.pl/index.php?showtopic=180387&hl=
Otwieram po konsultacji przez PW

Powód edycji: [wookieb]:

[jacusek]

podam jeszcze, że explain wyrzuca mi coś takiego przy tym pytaniu

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"PRIMARY";"<derived4>";"ALL";NULL;NULL;NULL;NULL;"11";""
"4";"DERIVED";"lek_pac";"ALL";NULL;NULL;NULL;NULL;"6820";"Using temporary; Using filesort"
"3";"DEPENDENT SUBQUERY";"lek_pac";"ALL";NULL;NULL;NULL;NULL;"6820";"Using where"
"2";"DEPENDENT SUBQUERY";"lek_pac";"ALL";NULL;NULL;NULL;NULL;"6820";"Using where"


Doszedłem w końcu do takiego pytania:

[SQL] pobierz, plaintext 
SELECT id_pac pacjent,
id_lek lekarz1,
(SELECT id_lek FROM lek_pac WHERE id_lek !=(lekarz1) HAVING count(id_pac)>1) lekarz2,
(SELECT id_lek FROM lek_pac WHERE id_lek NOT IN(lekarz1,lekarz2) AND id_pac IN(pacjent) HAVING count(id_pac)>1) lekarz3
 FROM 
  (SELECT id_lek,id_pac FROM lek_pac GROUP BY id_pac HAVING count(id_pac)>1) t2
[SQL] pobierz, plaintext 

No właśnie pokazuje mi 4 kolumnę, wszytko niby działa, ale nie pokazuje żądanego wyniku ( kolumnie 4 same null). Już nie wiem gdzie popełniam błąd.
Wygląda to tak jakby nie traktował warunków w sposób prawidłowy.

Ten post edytował jacusek 21.07.2011, 10:42:47

[thek]

W takiej sytuacji widzę, że mamy tabelę n-n... Teraz musimy określić CO chcesz z tego złączenia wyciągnąć. Jeśli tylko id lekarzy, to kombinujesz jak koń pod górkę Najprościej wtedy wziąć tabelę łaczącą lek_pac i złaczyć ją z tabelą lekarzy oraz użyć GROUP BY id_pac a w części select to co nas interesuje pobrać do kolumny w której uzyjemy GROUP_CONCAT na tym co chcemy.

EDIT: W zasadzie jeśli pokombinujesz chwilkę, to używając GROUP_CONCAT i konkatenacji stringów, możesz w ten sposób jednym zapytaniem wyciągnąc co chcesz.. Zarówno dane pacjenta, jak i dane jego lekarzy... Dla całej bazy
Wtedy łącząca będzie bazą główną i zrobisz join na pacjentów, potem join na lekarzy, całość pogrupować po pacjentach, a do tego ładnie w select group_concat. Powinna całość drastycznie przyspieszyć jeśli jeszcze masz sensowne indeksy.

PS... I bonusem... int(3) nie oznacza, że to pole jest 3-cyfrowe czyli od 0 do 999. Ale co oznacza to już sam się dowiedz.

[jacusek]

siedzę już i kombinuję z tym co napisałeś i nic niestety mi nie wychodzi. Mógłbyś ewelntualnie podać jakiś przykład.

ja wymyśliłem na razie coś takiego, co właściwie mi nic nie daje niestety. Poza mozliwością znalezienia konkretnych pacjentów będących pod opieką więcej niż jednego lekarza. Dalej nie wiem jak to ugryźć.

[SQL] pobierz, plaintext 
SELECT id_pac, 
group_concat(id_lek ORDER BY id_lek DESC) lekarz2 FROM lek_pac
GROUP BY id_pac
HAVING count(id_pac)>1
[SQL] pobierz, plaintext 

Czy ewentualnie jakiś przykład gdzie mogę to użyć w inny sposób

[thek]

Nie miałes chyba nigdy dużo kontaktu ze złączeniami i grupowaniem...

[SQL] pobierz, plaintext 
SELECT id_pac, group_concat(id_lek) AS lekarze
FROM lek_pac
GROUP BY id_pac
[SQL] pobierz, plaintext 

To jest pobranie wszystkich pacjentów i id ich lekarzy, tak więc blisko byłeś i dlatego pomagam, bo widzę, że coś od siebie kombinujesz, a nie jak niektórzy, co na gotowca liczą... GROUP BY nie ma opcji ORDER BY, a klauzula HAVING służy do ograniczania na zasadzie podobnej jak w WHERE. Jeśli więc byś to wywalił, to masz to co ja napisałem. Kwestia tylko tego co JESZCZE chcesz wyciągąć z tego zapytania, bo na razie znajduje Ci wszystkich lekarzy dla każdego pacjenta. To co Ty zrobiłeś (po usunieciu ORDER BY z GROUP_CONCAT) wyszukiwało by tylko pacjentów, którzy mają więcej niż 1 lekarza, a więc coś nieco innego niż chciałeś. Teraz możesz to zapytanie nieco przerobić i połaczyć z tabelą pacjentów by wyciągnąć dane pacjenta, a z odrobiną modyfikacji w GROUP_CONCAT możesz sobie pokombinować dodając JOIN do tabeli lekarzy i wyciągając z niej interesujące Cie rzeczy tyczące lekarzy. Podpowiem, że w takiej sytuacji wygodnie jest uzyć ustawianie własnego separatora, uzywanie wewnątrz CONCAT_WS konkatenacji interesujacych Cię pól i rozdzielanie tego po separatorze już po stronie skryptu php z użyciem explode. To będzie w miarę optymalne

[jacusek]

Dzięki za pomoc. Po prostu niepotrzebnie się upierałem żeby wszystko robić po stronie bazy, a myślę, ze część pracy muszę przerzucić na php i już.

[thek]

Dlatego tutaj zaleca sie rozsądek. Wiele osób upiera się, że wszystko ma być robione po stronie bazy jednym zapytaniem, bo to optymalniejsze (i czesto tak jest), ale sa sytuacje, gdy rozbicie na 2, 3 zapytania jest zwyczajnie lepsze, bo mocno upraszcza zapytanie i odciąża bazę. Zauważ jak bardzo uprościłem to zapytanie w porównaniu do Twojego z początkowego postu. Zamiast tworzyć multum kolumn, użyłem jednej i resztę zrzuciłem na skrypt PHP po stronie serwera. Tutaj właśnie liczy sie doświadczenie. trzeba wiedzieć, kiedy się wycofać z "oficjalnie uznanymi praktykami optymalizacyjnymi" Z czasem nauczysz się, kiedy powinno się łamać reguły by osiągnąć większe korzyści (mniejsze obciążenie, większa responsywnośc i szybkość aplikacji).

[jacusek]

Więc w sumie poradziłem sobie w ten sposób. Dla zainteresowanych:

[SQL] pobierz, plaintext 
SELECT concat(nazwisko,' ',imie) pacjenct, group_concat(nazwisko_imie) AS lekarz
FROM lek_pac
LEFT JOIN lekarze ON lek_pac.id_lek=lekarze.id_lek LEFT JOIN pacjenci ON pacjenci.id_pac=lek_pac.id_pac
GROUP BY lek_pac.id_pac
HAVING count(lek_pac.id_pac)>2
[SQL] pobierz, plaintext 

Oczywiście having count tylko w celach testowych . No i wykonuje się w normalnym czasie.....

[thek]

No i brawo! Jak widzisz da się. Tylko zastanawiam się co ten having, bo w tym momencie wybierasz tylko pacjentów posiadających więcej niż 2 lekarzy. No chyba że faktycznie tylko do testów

concat(nazwisko,' ',imie) pacjenct
zamieniłbym jeszcze na
concat(nazwisko,' ',imie) AS pacjenct
by ujednolicić jak nadajesz aliasy...

[jacusek]

teraz pojawił mi się nowy problem z tym pytaniem

[SQL] pobierz, plaintext 
SELECT nazwisko AS pacjent, group_concat(nazwisko_imie) AS lekarz
FROM lek_pac
LEFT JOIN lekarze ON lek_pac.id_lek=lekarze.id_lek LEFT JOIN pacjenci ON pacjenci.id_pac=lek_pac.id_pac
GROUP BY lek_pac.id_pac
HAVING count(lek_pac.id_pac)>2
[SQL] pobierz, plaintext 

przy próbie wykorzystania go w php

[PHP] pobierz, plaintext 
<?php
include('connect.php');
$sql="SELECT nazwisko as pacjent, group_concat(nazwisko_imie) AS lekarz
FROM lek_pac
left join lekarze on lek_pac.id_lek=lekarze.id_lek left join pacjenci on pacjenci.id_pac=lek_pac.id_pac
group by lek_pac.id_pac
having count(lek_pac.id_pac)>2";
 
$rez= mysqli_query($mysqli, $sql);
var_dump($rez);
echo 	"<table align=\"center\" border=\"1\">
			<tr><td>Pacjenct</td><td>lekarz</td></tr>";
			while ($rekord = mysqli_fetch_array($rez, MYSQLI_ASSOC)){
			$pacjent=$rekord['pacjent'];
			$lekarz=$rekord['lekarz'];
			}
echo "<tr><td>$pacjent</td><td>$lekarz</td></tr></table>";
?>
[PHP] pobierz, plaintext 

wywala mi błąd mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

wiem co on oznacza. Ale dalczego w var_dump ($rez) pokazuje mi boolean false, skoro pod mysql mi się wykonuje i nie wywali mi błędu składniowego?

Bład był w pliku dołączonym.

Ten post edytował jacusek 22.07.2011, 13:47:28

[thek]

Bo to nie błąd składniowy! Pokazuje, że masz skopane połączenie z bazą. Temat się już tyle razy przewinął przez to forum, że temat zamykam. Najpierw miałes problem z zapytaniem i twierdziłes, że umiesz złączenia - nie umiałeś. Dałem gotowca do minimalnego przerobienia. Teraz walisz pytanie z serii: "Byłem już na forum z milion razy...", które wymaga znów minimalnej checi uzycia głowy i wyszukiwarki. Innymi słowy po raz kolejny widze, że Ty nie chcesz sie uczyć, tylko czekasz na gotowca.

Chłopie.... Masz 200 postów ponad i nadal walisz elementarne byki. Forum to nie inteligentny parser, który pokaże Ci zawsze co nie tak. W końcu ludziom puszczą nerwy gdy teoretycznie użytkownik bedący już prawie 2 lata tu, a który powinien mieć jakąś wiedzę większą niż żółtodziób, pyta o tak elementarne rzeczy.