wydajność zapytania Opcje

[acztery]

witam mam takie zapytanie:

[SQL] pobierz, plaintext 
SELECT * , 
			(SELECT count(*)  FROM stat WHERE stat.D='1' AND DocId=www.Id ) AS a1,
			(SELECT count(*)  FROM stat WHERE stat.D='2' AND DocId=www.Id ) AS a2,
			(SELECT count(*)  FROM stat WHERE stat.D='3' AND DocId=www.Id ) AS a3,
			(SELECT count(*)  FROM stat WHERE stat.D='4' AND DocId=www.Id ) AS a4,
			(SELECT count(*)  FROM stat WHERE stat.D='5' AND DocId=www.Id ) AS a5,
			(SELECT count(*)  FROM stat WHERE stat.D='6' AND DocId=www.Id ) AS a6,
			(SELECT count(*)  FROM stat WHERE stat.D='7' AND DocId=www.Id ) AS a7,
			(SELECT count(*)  FROM stat WHERE stat.D='8' AND DocId=www.Id ) AS a8,
			(SELECT count(*)  FROM stat WHERE stat.D='9' AND DocId=www.Id ) AS a9,
			(SELECT count(*)  FROM stat WHERE stat.D='10' AND DocId=www.Id ) AS a10,
			(SELECT count(*)  FROM stat WHERE stat.D='11' AND DocId=www.Id ) AS a11,
			(SELECT count(*)  FROM stat WHERE stat.D='12' AND DocId=www.Id ) AS a12,
			(SELECT count(*)  FROM stat WHERE stat.D='13' AND DocId=www.Id ) AS a13,
			(SELECT count(*)  FROM stat WHERE stat.D='14' AND DocId=www.Id ) AS a14,
			(SELECT count(*)  FROM stat WHERE stat.D='15' AND DocId=www.Id ) AS a15,
			(SELECT count(*)  FROM stat WHERE stat.D='16' AND DocId=www.Id ) AS a16,
			(SELECT count(*)  FROM stat WHERE stat.D='17' AND DocId=www.Id ) AS a17,
			(SELECT count(*)  FROM stat WHERE stat.D='18' AND DocId=www.Id ) AS a18,
			(SELECT count(*)  FROM stat WHERE stat.D='19' AND DocId=www.Id ) AS a19,
			(SELECT count(*)  FROM stat WHERE stat.D='20' AND DocId=www.Id ) AS a20,
			(SELECT count(*)  FROM stat WHERE stat.D='21' AND DocId=www.Id ) AS a21,
			(SELECT count(*)  FROM stat WHERE stat.D='22' AND DocId=www.Id ) AS a22,
			(SELECT count(*)  FROM stat WHERE stat.D='23' AND DocId=www.Id ) AS a23,
			(SELECT count(*)  FROM stat WHERE stat.D='24' AND DocId=www.Id ) AS a24,
			(SELECT count(*)  FROM stat WHERE stat.D='25' AND DocId=www.Id ) AS a25,
			(SELECT count(*)  FROM stat WHERE stat.D='26' AND DocId=www.Id ) AS a26,
			(SELECT count(*)  FROM stat WHERE stat.D='27' AND DocId=www.Id ) AS a27,
			(SELECT count(*)  FROM stat WHERE stat.D='28' AND DocId=www.Id ) AS a28,
			(SELECT count(*)  FROM stat WHERE stat.D='29' AND DocId=www.Id ) AS a29,
			(SELECT count(*)  FROM stat WHERE stat.D='30' AND DocId=www.Id ) AS a30,
			(SELECT count(*)  FROM stat WHERE stat.D='31' AND DocId=www.Id ) AS a31
 
			FROM www GROUP BY www.Id
[SQL] pobierz, plaintext 

Ale przy np 40 tys rekordów w bazu stat mam komunikat o braku pamięci. Jak wywołam zapytanie bezpośrednio w phpmyadminie jest. może ktoś ma jakiś pomysł. Indexy są

[phpion]

To co pokazałeś w pierwszym poście to jakiś koszmar! Nie sądziłem, że ktoś jest w stanie trzaskać (wymyśleć!) takie zapytanie... Ja bym Ci sugerował inne podejście do sprawy oraz zapoznanie się z tematyką:
- grupowanie (GROUP BY),
- podzapytania skorelowane.
Wykorzystując grupowanie jesteś w stanie bez problemu otrzymać statystyki, o które Ci chodzi. Jest tu jednak jeden problem: jeżeli w tabeli 'stat' nie będzie żadnych odwiedzin dla któregoś dokumentu to nie zostanie on uwzględniony w zbiorze wynikowym.
Wykorzystując podzapytanie skorelowane ominiesz powyższą niedogodność ale samo zapytanie może być mniej wydajne (szczególnie przy większej ilości dokumentów).
Decyzja, którą drogą pójdziesz należy do Ciebie.