Dzięki za wszystkie sugestie, brakło mi wcześniej czasu by odpowiedzieć.
- FK nie tworzy indeksu. FK z założenia jest do pola PK w innej tabeli więc to pole ma też indeks. Indeks na polu
messages->room_id
ma sens TYLKO jeśli używasz tego pola do wyszukiwania.
Używam pola do wyszukiwania. Gdzieś wyczytałem, że FK nie wymaga dodatkowego indeksu, bo sam w sobie jest indeksem w InnoDB.
co jeszcze możesz zrobić - sprawdz, czy wszystkie indeksy są używane https://vettabase.com/finding-duplicate-indexes-and-unused-indexes-mariadb-mysql/
Zrobiłem indeksy złożone zgodnie z ich opisem w dokumentacji, czyli kolumny od lewej w kolejności użycia w zapytaniach. Mimo to, niektóre zapytania (EXPLAIN) nie używają indexu złożonego, tylko np. PRIMARY.
no i ja bym jednak zrezygnował z UUID (o ile nie kożystasz z ich właściwości, np. globalna unikalność) na rzecz auto inkrementacji inta/biginta - po pierwsze tracisz czas na generowanie UUID, po drugie zajmują 2 razy więcej miejsca niż bigint albo 4 razy więcej niż int a co za tym idzie indeksy na tych polach są większe i porównywanie trwa dłużej
Możesz spróbować przejść tylko na zwykłego int. Wywal całkowicie uuid. A ten hash, który będzie widział user, możesz generować w locie na bazie ID z bazy:
https://sqids.org/php
Niestety jest to system rozproszony czasu rzeczywistego, więc UUID-y są tu koniecznością żeby nie "zarżnąć" bazy samym generowaniem identyfikatorów i pilnowaniem ich unikalności.
- Do relacji/identyfikacji używaj zwykłego ID integerowego incrementowanego.
- Jeśli chcesz UUID, żeby np. user nie mógł zrobić enumeracji, to przechowuj go jako unique w dodatkowej tabeli i używaj go zawsze gdy zwykły zewnętrzny user ma dostęp do obiektu (np. w adresie URL, w wyszukiwaniu resource'a po tym UUID). I obowiązkowo dodaj index wtedy na ten UUID.
- Jeśli nie ma znaczenia, czy user zna ID, czy nie, to lepiej korzystać z ID. Np. w panelu admina możesz operować na ID.
I to chyba będzie rozwiązanie. Stworzę nowe tabele mapujące ID numeryczne do UUID i zrobię relacje po tej intowej wartości. Wtedy przy insertach do tabeli messages
jedynym UUID-em do indeksacji będzie ID wiadomości, reszta (room_id
i pochodne) będą numeryczne. To chyba dobry kierunek
Co do partycjonowania, to szczerze mówiąc nie wiem jak się za to zabrać. Czytałem ogólnie na czym to polega i na pierwszy rzut oka wydaje mi się, że na tabeli która najbardziej potrzebowałaby tego rozwiązania, to będzie ciężkie do osiągnięcia przez konieczność użycia funkcji agregujących.