[Jurix]

Mam tabelę z bardzo dużą ilością rekordów, składających się z klucza obcego, typu i value.
Potrzebuję wyciągnąć sumę value dla poszczególnych typów, pogrupowane przez klucz obcy. Jak możliwie wydajnie i zgodnie ze sztuką powinienem napisać query wyciągające te dane?

#programowanie #bazydanych #mysql

[asciiterror]

@Jurix: co rozumiesz przez "dla poszczególnych typów, pogrupowane przez klucz obcy"? Zapewne nie chodzi ci o SELECT sum(value) GROUP BY type, foreign_key

[Jurix]

@asciiterror: Właśnie coś w tym kierunku kombinuję, ale idealnie chciałbym to mieć w 1 row, tzn. select foreignkey, sumadlatype1, sumadlatype_2... Typów mam na ten moment tylko 2 i potem na backendzie i tak będę musiał je segregować, a wolałbym fajną odpowiedź z bazy. Da radę bez subquery?

[Jurix]

@asciiterror: Jak będę robić paginację to będę mieć duże problemy, bo niektóre klucze mogą mieć 1 typ, a niektóre 2. Jeśli na backendzie będę to łączyć to nie zrobię paginacji.

[asciiterror]

@Jurix: jeżeli chcesz mieć każdy typ w swojej kolumnie to nie kojarzę na to w SQLu innego sposobu niż wypisanie tych typów ręcznie. Tzn pewnie będziesz musiał użyć jakiegoś subquery, ale czy to jest w czymś problem?

[Jurix]

@asciiterror: Znam ilość typów, na ten moment wynosi 2, jak zmienię to edytuję query. To czego obawiam się przy subquery to że zabije mi wydajność. Samo odczytanie sumy po pogrupowaniu przez klucz obcy trwa teraz prawie 3 sekundy.

Chciałbym osiągnąć efekt na zasadzie

SELECT sum(value) where type = 1, sum(value) where type = 2. Inaczej niż subquery tego nie zrobię?

[asciiterror]

@Jurix: niekoniecznie zabije wydajność, przecież bazy zazwyczaj przekształcają zapytanie na równoważną postać. Sprawdź sobie plan zapytania.
Bardzo dużo tych rekordów tzn. ile?

[Jurix]

@asciiterror: ok. 10 milionów. Dla mojego serwera (przy złożoności relacji) to bardzo dużo, w pracbazie mamy tabele po 500kk, ale sprzęt lepszy i lepsze technologie to obsługujące.

[asciiterror]

@Jurix: nic lepszego nie wymyśliłem niż coś takiego:
zamiast WITH fkeys AS możesz zrobić paginację po tym kluczu obcym, bo zakładam że masz gdzieś tabelę gdzie jest kluczem głównym.

WITH
 fkeys AS ( SELECT fkey FROM jur GROUP BY fkey )
SELECT fkeys.fkey, s1.s AS "sum type=1", s2.s AS "sum type=2"
FROM fkeys
LEFT JOIN ( SELECT SUM(val) AS s, fkey FROM jur WHERE type=1 GROUP BY fkey ) AS s1

[Jurix]

@asciiterror: Widok #!$%@??

[asciiterror]

@Jurix: zmaterializowany tak. Czy się opłaca to zależy jak często dopisujesz dane do tej tabeli.
Rozważyłbym też zrobienie dwóch osobnych tabel na te dwa typy z klastrowanymi indeksami po fkey, bo podejrzewam że teraz zapytanie może w ogóle nie używać indeksów bo musi przejrzeć całą tabelę. Albo i w jednej tabeli ale z klastrowanym indeksem na (fkey, type) - powinno przyspieszyć.

[Jurix]

@asciiterror: Dzięki wielkie za pomoc! Udało mi się napisać query co zwraca co potrzebuję w 0,3s, więc sukces.