W jaki sposób mogę zoptymalizować to zapytanie? Obecnie wykonuje się koło 50 sekund, natomiast po usunięciu 3 JOINa jest to kwestia niecałej sekundy.

[SQL] pobierz, plaintext 
			SELECT * FROM `clientgroupassign`
			LEFT JOIN `clients` ON `clientgroupassign`.clientId = `clients`.clientId
			LEFT JOIN `users` ON `carerId` = `userId`
			LEFT JOIN 
				(SELECT * FROM 
					(SELECT * FROM `contacts` WHERE `contactGroup` = 4 ORDER BY `contactId` DESC) 
					AS `contacts` 
				GROUP BY (`contactClientId`)
				)  AS `contacts` ON `contactClientId` = `clients`.clientId
			WHERE groupId = 4
			ORDER BY `clients`.clientId
[SQL] pobierz, plaintext 

Ostatni LEFT JOIN zmień na RIGHT JOIN dla próby.

Bez zmian. Nawet samo wywołanie:
"SELECT * FROM (SELECT * FROM `contacts` WHERE `contactGroup` = 4 ORDER BY `contactId` DESC)
AS `contacts`
GROUP BY (`contactClientId`)"
wszystko zawiesza i wykonuje się w nieskończoność..

W tym zapytaniu chcę pobrać 1 ostatni rekord z tabeli `contacts` dla każdego klienta będącego w grupie 4(bo klient może być w kilku grupach).

Rozbić na 2 zapytania ? Dodać indeksy ?

Indeksy są, w tabeli kontakt wyglądają tak:

[PHP] pobierz, plaintext 
Nazwa klucza	Typ	Jednoznaczny	Spakowany	Kolumna	Moc	Metoda porównywania napisów	Null	Komentarz
PRIMARY	BTREE	Tak	Nie	contactId	30653	A		
contactClientId	BTREE	Nie	Nie	contactClientId	30653	A		
                                                        contactGroup	30653	A	
[PHP] pobierz, plaintext 

Jak rozbić na dwa zapytania?

Przedstaw całą strukture tabel, bo te całe Twoje zapytanie do dupy jest niepodobne. Lub opisz co chcesz osiągnąć.

Dziwi Cię to?

Do czego Ci te selecty w selectach?

Proponuję denormalizację poprzez dodanie osobnej kolumny, która będzie przechowywała ID ostatniego kontaktu. Zachowanie poprawności danych osiągniesz poprzez triggery.