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ą

[ddiceman]

Jezeli chcesz uzyskac taki format, jaki miales, to proponuje

[SQL] pobierz, plaintext 
SELECT *,
        SUM(IF(stat.D = '1', 1, 0)) AS a1,
        SUM(IF(stat.D = '2', 1, 0)) AS a2,
        SUM(IF(stat.D = '3', 1, 0)) AS a3,
        ...
        SUM(IF(stat.D = '31', 1, 0)) AS a31
    FROM www LEFT JOIN stat ON (www.Id = stat.DocId)
    GROUP BY www.Id;
[SQL] pobierz, plaintext 

Jezeli mozesz troche zmienic

[SQL] pobierz, plaintext 
SELECT *, COUNT(stat.DocId) FROM www LEFT JOIN stat ON (www.Id = stat.DocId) GROUP BY www.Id, stat.DocId;
[SQL] pobierz, plaintext 

Ale pisze z palca, wiec musisz sam przetestowac sobie to.