Klucze obce - ktory typ indexu wybrac Opcje

[lukaskolista]

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.

[wookieb]

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

[lukaskolista]

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

[wookieb]

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.

[lukaskolista]

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.

Ten post edytował lukaskolista 9.06.2011, 12:22:19

[wookieb]

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

[lukaskolista]

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.

[phpion]

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).

[lukaskolista]

[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.

[phpion]

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.

[AsYlum]

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

Note: Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. Furthermore, hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged.

[everth]

@phpion

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.

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

[phpion]

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

Kod

users.id | users.username | users.name | phones.id | phones.number
1        | mietek         | Mieczyslaw | 1         | 123 45 67 89
2        | heniek         | Henryk     | 2         | 234 56 78 90
2        | heniek         | Henryk     | 3         | 345 67 89 01
2        | heniek         | Henryk     | 4         | 456 78 90 12

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

PS: Nie MySQL, a PostgreSQL (IMG:style_emoticons/default/wink.gif)

[everth]

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.