Wydajność zapytani sub select Opcje

[kris2]

Mam taki zestaw tabel:

użytkownik (z uzytkownikami systemu)

grupy (grupy do ktorych moze zapisac sie uzytkownik) (np A, B, C , D , E ...)

uzytkownicy_grupa (relacjz zawierająca informacje o grupac do ktorych jest zapisany użytkownik)

załóżmy że użytkowników jest milion a grup 10-20 więc wydajność ma znaczenie.

schamt jest taki

użytkownik 1 - N użytkownicy_grupa N <- 1 grupy

Jak najwydajniej zapytać się o użytkowników należących do grupy A, B i C:
Przychodzą mi do głowy 3 rozwiązania

po 1: - totalnie lame
- umieścic w tabeli użytkownicy spis grup po przecinku i użyć LIKE

po 2: - wykorzystanie zmiennej typu array
- to też nie wygląda na idealne rozwiązanie ponieważ jest ograniczenie przy przeszukiwaniu zmiennych array, można jedynie użyć any lub all
(+ na PostgreSQL napisano
Tip: Arrays are not sets; searching for specific array elements may be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale up better to large numbers of elements.
)

po 3: - używanie subselecta na zasadzie

[SQL] pobierz, plaintext 
SELECT * FROM użytkownik u WHERE (SELECT * FROM grupa g WHERE u.użytkownik_id=g.użytkownik_id AND (g.grupa=A or g.grupa=B or g.grupa=C))=3
[SQL] pobierz, plaintext 

Ma ktoś jakieś lepsze rozwiązania lub umie uzasadnić które wybrać i dlaczego?

Ten post edytował kris2 21.08.2007, 19:40:38

[osiris]

Wydaje mi sie ze to zapytanie nie jest zbyt optymalne, a to dlatego ze podzapytanie bedzie wykonywane dla kazdego uzytkownika.Chyba najprosciej:

[SQL] pobierz, plaintext 
SELECT * FROM uzytkownicy WHERE id IN (
SELECT uzytkownik_id
FROM uzytkownicy_grupy WHERE grupa_id IN (A,B,C,D)
GROUP BY uzytkownik_id
HAVING COUNT(grupa_id) = 4)
[SQL] pobierz, plaintext 

W tym wypadku podzapytanie powinno wykonac sie tylko raz, gdyz nie ma w nim odwolania do tabeli zewnetrznej

Jesli chodzi o indeksy, to niewazne ktoro rozwiazanie zastosujesz, zawsze powinienes z nich korzystac.

A zeby sie w latwy sposob przekonac ktoro rozwiazanie bedzie najszybsze, stworz te tabele, wypelnij je przykladowymi danymi i uruchom kilka razy te zapytania, ale wylacz dla nich cacheowanie (SQL_NO_CACHE).

Teraz przyszedl mi do glowy jeszcze jeden pomysl. Ma on jedno wazne ograniczenie, ale mysle ze byloby najbardziej wydajne.
Ograniczeniem jest tu liczba wszystkich mozliwych grup, nie moze byc wieksza niz 32 (bo typu int ma 32bity).

Dla pelnego zrozumienia wymagana jest podstawowa wiedza nt operacji logicznych.

Oto co moznaby zrobic:
- tabela uzytkownicy_grupa niepotrzebna
- kazdej grupie przypisujesz numer bedacy potega dwojki (1,2,4,8,16,32... az do 2^31)
- do tabeli uzytkownikow dodajesz jedno pole grupy - typu unsigned int
- teraz chcesz dodac kogos do danej grupy:

[SQL] pobierz, plaintext 
UPDATE uzytkownicy SET grupy = (grupy | $nr_grupy) WHERE id = $id_uzytkownika
[SQL] pobierz, plaintext 

- usunac kogos z danej grupy:

[SQL] pobierz, plaintext 
UPDATE uzytkownicy SET grupy = (grupy & ~$nr_grupy) WHERE id = $id_uzytkownika
[SQL] pobierz, plaintext 

- chcesz wyszukac wg danej grupu:

[SQL] pobierz, plaintext 
SELECT * FROM uzytkownicy WHERE (grupy & $nr_grupy) > 0
[SQL] pobierz, plaintext 

Wszysktkie te operacje mozna wykonac dla kilku grup naraz, zastepujac $nr_grupy czyms takim ($grupa_1 | $grupa_2 | $grupa_3 .... $grupa_n)czyli dla przykladu:

[SQL] pobierz, plaintext 
SELECT * FROM uzytkownicy WHERE (grupy & ($g1 | $g2 | $g3 | $g5)) > 0
[SQL] pobierz, plaintext 

operatory:
& - iloczyn logiczny bit po bicie
| - suma logiczna bit po bicie
~ - inwersja wszystkich bitow

Uwaga, nie mylic tych operatorow z &&, || i !

Ten post edytował osiris 22.08.2007, 10:53:33