Witam,

Czy ktoś może ma doświadczenie i chciałby się podzielić w takiej sytuacji jak przedstawiam poniżej?

Mam (ciągle rosnącą) bazę danych pochodzących z kilkunastu różnych źródeł. Dane te z każdego źródła standaryzuję i upycham do jednej tabeli celem dalszej obróbki i przygotowania danych pod wykresy. Dane te rozróżnialne są przez pięć kolumn charakterystycznych: id źródła, data wpisu, id elementu którego dane dotyczą, typ i kraj, nazwijmy je col1..col5. Czyli tabelka wygląda tak:
id,col1,col2,col3,col4,col5,further_columns_with_data_to_show

Tyle backendu.

Od strony użytkownika to wygląda tak, że może wybrać zakres dat, jeden/kilka/wszystkie elementy, jeden/kilka/wszystkie kraje, jeden/kilka/wszystkie źródła i ewentualnie rozróżnić je po typach, klika "go!" i dostaje wykres iluś serii. Dodatkowo, może zechcieć zsumować dane po którejś kolumnie, na przykład chce mieć wykres z jedną serią dla każdego elementu (skumulowane kraje i źródła). Jedyne czego się nie da zsumować to daty - wykres zawsze jest dzień po dniu.

Tyle frontendu.

Wracając do bazy danych. Przygotowałem indeksy na tej tabeli pozwalające na wyświetlenie danych w "wybieraczkach" oraz jeden indeks do wyciągania danych, zawierajacy dane do wyświetlenia (celem przyspieszenia obliczeń). Wszystko działa w miarę sprawnie, to znaczy czas wykonania zapytania waha się, w zalezności od wybranych danych 0.1s-3s. Wierszy obecnie jest ponad 20mln. Wszystkie zapytania wykorzystują indeks, widać jego pracę itd.

Pytanie teoretyczne brzmi: jak to można przyspieszyć? Jaką użyć bazę, jak ułożyć struktury? Pomysły? Może przenieść to do plików i grep|awk? Albo Mongo? Pre-liczenie danych do wyświetlenia i redundancja?

czy jest jakieś pole w tej tabeli które może posłużyć za podziałkę danych i czy w ramach różnych podziałek łącznie może nastąpić wyszukiwanie?

Bo jeżeli możesz logicznie podzielić dane na jakiś typ (cokolwiek) a potem wyszukiwanie odbywa się tylko w ramach tego typu to możesz spokojnie zastosować partycjonowanie.
Da to kopa. Bo jeśli "typ" nie jest taką granicą to raczej coś NonSQL typu Mongo

Nie wiem czy Cię dobrze zrozumiałem. Dane na pewno są dzielone względem dni, nie robię sum z zakresu, tylko dzień po dniu. Poza tym jest to macierz 4x4 i może być tam dosłownie wszystko. Raz ktoś zapyta o sumę dla wszystkich elementów które wykazuje dane źródło, raz ktoś zechce porównać jak poszczególne elementy są raportowane przez różne źródła (i oleje kraje), a raz postanowi prześledzić historię konkretnego elementu w danym kraju według raportów z danego źródła i wybierze konkretnie jedną serię. Ciężki przypadek. "Elementy" są globalne i pojawiają się we wszystkich krajach, u wszystkich źródeł i dane dla nich mogą mieć wszystkie dostępne typy. Ale nie muszą. Streściłbym to: kolekcja (po datach) macierzy 4x4 (+dane).

Zastanawiałem się nad mongo, ale to jest raczej baza na dokumenty, hierarchiczna, i nie wiem czy przeszukiwanie iluś obiektów w celu policzenia średniej z jakiejś wartości to dobre zastosowanie dla niej... Są tam jakieś kolekcje danych na których można wykonywać agregacje?

A ile jest ~ danych np. w mc/roku?

ablo nie doczytałem albo nie napisałeś. Masz to w mysql? Jeżeli tak to i tak masz rewelacyjny czas dostępy do 20 mln rekordów. Możesz spróbować przejść na postgres, interface PDO / ORM nie zmieni się praktycznie a powinieneś troche zyskać na tym.

Dzięki za podpowiedzi. Tak, mysql. Mam inną część serwisu do przepisania, poeksperymentuję co można wyciągnąć z mongo. Może jakieś zbiory indeksów walnę w redisie a z mysql'a będę wyciągał dane rangem, chociaż nie wiem jeszcze co by to miało przyspieszyć :). Nie wiem ile czasu i pieniędzy da mi na to szef inwestor ;). W każdym bądź razie, jak uda mi się zrobić z tego demona prędkości, to podzielę się uwagami w tym wątku.