Optymalizacja zapytań z GROUP BY w MySQL (i nie tylko)

Przyznam, że do wczoraj sposób działania planera w przypadku zapytań agregujących (z klauzulą GROUP BY) był dla mnie nie do końca jasny.

Akurat trafiłem w mojej aplikacji na zapytanie na tyle proste, a jednocześnie wolne (operujące na dużej tabeli), że łatwo było je analizować. Szukając przyczyny i sposobów optymalizacji, trafiłem na znakomitą odpowiedź na stackoverflow. Pozwolę sobie wkleić jej sedno:

For covering index you add:
1. columns used in where clauses first, then
2. columns used in group by, then
3. columns used in order by, and then
4. columns used in select.

Ze swoim zapytaniem miałem nieco większy problem, ponieważ łączyło ono dwie tabele poprzez JOIN. Jak się okazuje, kolumny z warunków JOIN-owania należy przypisać do pierwszego punktu, co potwierdziło się w moim dzisiejszym pytaniu.

Sprawdziłem działanie tego schematu również na zapytaniach z GROUP BY na dużych tabelach w PostgreSQL i również działało (skutkowało wykonywaniem zapytania w pełni na indeksach), więc można przyjąć, że podobnie działa to dla większości popularnych DBMS.

PostgreSQL – losowe wartości z podzapytania

W PostgreSQL, korzystając z podzapytania używającego funkcji RANDOM() tak, jak np. w MySQL, otrzymamy te same wyniki dla każdego wiersza.

Przykład:

SELECT
    t1.id,
    (SELECT name FROM table2 ORDER BY RANDOM() LIMIT 1) AS random_name
FROM table1 t1

W tym DBMS podzapytanie tego typu wykonuje się raz, więc przy każdym wierszu z tabeli table1 będzie widniała ta sama wartość random_name.

Aby to zmienić, wystarczy sztucznie skorelować podzapytanie z zapytaniem nadrzędnym, np. tak:

SELECT
    t1.id,
    (SELECT name FROM table2 WHERE t1.id = t1.id ORDER BY RANDOM() LIMIT 1) AS random_name
FROM table1 t1

Niby proste, ale jakiś czas temu straciliśmy w pracy na tym trochę czasu (zwłaszcza nie wiedząc, że problem jest związany z podzapytaniami, bo samo zapytanie było bardziej złożone).