Tworzę model bazy (serwer mysql) zbierającej dane pogodowe z wielu jednostek na świecie (około 160 regionów a te dzielone na podregiony). Mogę je wszystkie gromadzić w jednej wielkiej tabeli o głównych kolumnach "id_danych", "data_odczytu", "opis_danych" (TEXT), kolumny z danymi liczbowymi, "id_regionu", "id_podregionu". Indeks musi być założony na "id_podregionu" i prawdopodobnie na "opis_danych" (fulltext indeks) aby umożliwić działanie wyszukiwarki po tekście opisu. Dane będą dopisywane bardzo często, (ok 2 razy na minutę nowy rekord), będą też często czytane przez różnych użytkowników portalu internetowego (baz połączona z tym portalem). Myślę że będzie około 10 czytań (selectów) na minutę. Dotyczyć będą one pojedynczych rekordów lub grup rekordów dla podregionów. Nie będzie wyszukań zwracających np. 2 rekordy dotyczące jednego podregionu i dwa dotyczące drugiego. Wyszukiwania przez wyszukiwarkę po tekście opisu "opisu_danych" będzie znacznie rzadsze (raz na kilka minut). Tabela będzię zawierała max. 200 tys rekordów po czym najstarsze będą archiwizowane i usuwane z tabeli. No i tu obawiam sie o działanie całości bo duża tabela z indeksami może mocno zwalniać przy dodawania rekordów ze względu na konieczność nowego przeszeregowania indeksowań. Boję sie że przy dużej ilości updatów nawet zwykłe czytania (selecty) będą bardzo opóźniane. Mam drugi pomysł który może poprawi wydajność ale brak mi tu pewności. Mogę główną tabelę rozbić na 160 tabel reprezentujących regiony. Każda będzie gromadziła dane z danego regionu, po dodaniu rekordu do jednej tabeli przeindeksowania powinny być krótsze bo tabele będa znacznie mniejsze a jednocześnie blokowana będzie tylko jedna tabela a zapytania do pozostałych będą mogły być realizowane bez przeszkód (jak pisałem zapytania będą dotyczyły tylko jednego podregionów, żadnych joinów). Jak to widzicie? Są tu jakieś haczyki?
Mogę główną tabelę rozbić na 160 tabel reprezentujących regiony.
Architektoniczny koszmar ;) Ja bym nie szedł w tą stronę.
Może lepiej pochylić się nad jakąś sprytną procedurą, która będzie wyszukiwać/zwracać wynik według konkretnego zapytania, albo przemyśleć jakiś rozbudowany mechanizm tagów (zamiast full text search) i wiele do wielu z rekordami zawierającymi faktyczne dane. Czy full text search jest dobrze zoptymalizowane w mysql? Wiesz jak długo będzie się przebudowywał indeks dla full text search?
Z drugiej strony 200k rekordów to nie tak dużo, więc może nie ma co tworzyć sobie samemu problemów.
Index Full Text na polu opisu jest potrzebny tylko dla wyszukiwarki i właśnie tu widzę największy problem. Ale jak czytałem w artykułach jest to podstawowe narzędzie do tworzenia wyszukiwarki terminów dla użytkownika strony. Nie wiem jak to obejść za bardzo. Podział tabeli na 160 mniejszych będzie wymagał trochę pracy ale jest do zrobienia. Mogę tez pogrupować to w makroregiony i zrobić podział na np 20 tabel.
A co myślicie o takim rozwiązaniu żeby obok głównej tabeli takiej jak opisana powyżej stworzyć identyczną ale tylko z polami "nowe_id" (Primary Key), "id_danych" (Foreign Key) i "opis_danych". Dwie ostatnie kolumny byłyby kopią kolumn z pierwotnej tablicy. Dopisywanie danych do pierwszej tablicy skutkowałoby dopisaniem ich do tej drugiej. Tylko w drugiej tabeli kolumna z opisem "opis_danych" miała by ustawiony full text index. Zwykłe selekty byłby realizowane przez pierwszą tabelę ale selekty zlecane przez wyszukiwarkę terminów dotyczące tekstu w polu "opis_danych" prowadzone byłby na tej drugiej tabeli żeby nie blokować pierwszej. W tym momencie pierwsza tabela (częściej selectowana) byłaby bardziej dostępna (nie blokowana).
@Tomasz K. takie coś ma sens i to jest jeden z klasycznych przykładów dla stosowania powiązania 1:1 :) Jeśli masz jakieś duże dane (bloba, długie stringi etc) które są używane rzadko, to możesz je przerzucić to osobnej tabeli.