Witam. Mam problem ktory typ indexu wybrac dla kluczy obcych. Z tego co znalazlem w sieci BTREE jest najbardziej uniwersalny i najbardziej sie do tego nadaje, ale wole zapytac Was. Za wszystkie konstruktywne wypowiedzi dziekuje.

HASH używasz wtedy kiedy nie potrzebujesz sortowania a wyszukujesz jedynie konkretne wartości. BTREE jest po prostu bardziej uniwersalny.

Potrzebuje jak najwieksza wydajnosci kluczy obcych poniewaz baza ma kilkanascie mln rekordow i z zalozenia bedzie miala duzo wiecej

Ale indeksów nie zakłada się pod "schemat" tabeli tylko pod zapytania i częstotliwość ich występowań. Poza tym konieczne jest info o selektywności danych w kolumnie. Bez tego, żaden indeks nie pomoże.

Dane sa pobierane bardzo czesto (300 osob na raz mieli system). Chodzi mi o szybkie zlaczenia bo to one sa najwolniejsze. Pola na ktore nakladam warunki tez oczywiscie musza miec indexy, ale to jest drugi krok w rozwiazaniu problemu wydajnosci. Chodzi mi o ogolne poradu w jakim przypadku jaki index bo z reszta sobie poradze.

Ale przecież mówię Ci jasno, że bez podanych przeze mnie wyżej danych, zakładanie indeksu to półbezsens.

Users:
id
name
password

Users_Numbers
user_id
number_id

Numbers:
id
number

Properties
id
number_id
name
value

[SQL] pobierz, plaintext 
SELECT * FROM users 
JOIN users_numbers ON users.id = users_numbers.user_id
JOIN numbers ON users_numbers.number_id = numbers_id
JOIN properties ON properties.number_id = numbers.id
[SQL] pobierz, plaintext 

Tak to wyglada. Po kluczach obcych w ogole nie wyszukuje, robie po nich jedynie zlaczenia.

Czy to całe zapytanie? Pewnie nie. Jeśli jednak tak, to przy założeniu, że masz kilkanaście milionów rekordów (jak piszesz) to nie dziwota, że działa powoli. Podaj całe zapytanie (wraz z warunkami).

[SQL] pobierz, plaintext 
SELECT * FROM users 
JOIN users_numbers ON users.id = users_numbers.user_id
JOIN numbers ON users_numbers.number_id = numbers_id
JOIN properties ON properties.number_id = numbers.id
WHERE users.id = 5837354
[SQL] pobierz, plaintext 

Wyknuje sie okolo 2 sec ale to stanowczo za wolno i wlasnie dlatego chce nalozyc indexy, tylko nie wiem ktory typ wybrac.

Nie sądzę, by typ indeksu wiele tutaj pomógł, ale możesz przecież samodzielnie spróbować: najpierw btree i sprawdzić czas, potem hash i sprawdzić czas.

Ja spróbowałbym rozbić to na kilka zapytań, bo coś mi się wydaje, że poprzez SELECT * pobierasz nadmiarową liczbę danych. Czyli najpierw pobierasz samego użytkownika, potem pobierasz jego numery. W tym co robisz to dla każdego zwracanego wiersza dodajesz również informacje o użytkowniku.

Indeksy typu hash wiele nie zmienią, a mogą Ci sprawić dodatkowe problemy.

@phpion

Nie sądzę żeby tak było, chyba że optymalizator zapytań MySQLa jest tak dupiaty że wykonuje złączenie przed operacją restrykcji. Rzuć EXPLAIN.

@everth:
Chodziło mi o nadmiarowość w już zwracanych danych, np.:

Przy Henryku mamy 3x pobrane dane użytkownika - o tą nadmiarowość mi chodziło.

PS: Nie MySQL, a PostgreSQL

Tu już trzeba by mieć wiedzę jak realizowane jest to przez sterownik bazy. W PDO jest metoda fetchColumn która pozwala wybrać z wiersza interesujące nas kolumny. Jeśli te zapytania są traktowane przez bazę jako adhoc to w rezultacie twoje podejście może okazać się bardziej ciężkostrawne dla bazy niż jedno duże zapytanie.

PS: Tak to już jest jak się pisze w środku nocy.