Witam,
Projektuję stronę i mam problem z założeniami - proszę o pomoc.
Czy moje rozwiązanie będzie wydajne?

W bazie przechowuję idiki (w formie: 123,345,2324325,453432,324234 itd.) jako wartość MEDIUMTEXT docelowo może być ich nawet 50 000.
Na stronie robię zapytanie sql: select kolumna from tabela where id IN(moje 50 000 idików).

Co jakiś czas wartość z idikami jest zmieniana - dodawane nowe idiki lub kasowane.

Nie potrafię przeprowadzić testu czy to rozwiązanie jest optymalne a obawiam się, że stronę będę musiał szybko zamknać bo serwer nie wytrzyma obciążenia przy takich zapytaniach i przy działaniach na takiej ilości idików tzn pobranie do tablicy->rozdzielenie po przecinku->dodanie idików->skasowanie duplikatów->ponowne połączenie z przecinkami->zapisanie do bazy.


Nie mogę podać swojego zastosowania ale podam przykład:
serwis społecznosciowy, w którym wyświetlamy znajomych i ich znajomych. Aby za każdym razem nie pobierać wszystkich znajomych i ich wszystkich znajomych można zapisać wszystkie idiki znajomych znajomych do tabeli i robić zapytania WHERE id IN(idiki znajomych znajomych).
Znajomych jest 200 czyli dla jednego usera może być nawet 40 000 znajomych znajomych.
Może jest na to lepsze rozwiązanie?

Nie powinieneś ich przechowywać po przecinku, tylko stworzyć kolejną tabelę, np. 'friends' o kolumnach

id - auto_increment primary
user_id - id usera, do którego chcesz przypisać znajomego
friend_id - id usera, który jest tym znajomym

Dodatkowo mógłbyś zastosować Cache dla strony użytkownika i np. zastosować wzorzec Obserwator, który zupdatuje Cache w razie zmiany przyjaciół.

Rozwiązanie wydajniejsze, czytelniejsze i dużo łatwiej wtedy operować danymi.

1. Przykład ze znajomymi to idealne zastosowanie dla tzw. grafowych baz danych, może powinieneś zainteresować się takim rozwiązaniem? Sam polecam Neo4j.
2. Powinieneś mieć osobną tabelę, w której będziesz miał zapisane id użytkownika, id drugiego użytkownika i opcjonalnie rodzaj ich relacji (friend, friend-of-firend itp.). Wtedy też będziesz mógł w miarę wygodnie wykonywać zapytania z podzapytaniami w stylu:

[SQL] pobierz, plaintext 
SELECT ... FROM users WHERE id IN(SELECT friend_id FROM user_friends WHERE user_id = :uid);
[SQL] pobierz, plaintext 

co w przypadku gdy osób będzie 10 000, wtedy zakładając że każdy ma po 200 znajomych relacji będzie 2mln.

większy problem mam ze znajomymi znajomych - jak zapisać relacje ze znajomymi znajomych, których może być nawet 40 000 dla jednego użytkownika ?



jak wyżej:

co w przypadku gdy osób będzie 10 000, wtedy zakładając że każdy ma po 200 znajomych relacji będzie 2mln.

większy problem mam ze znajomymi znajomych - jak zapisać relacje ze znajomymi znajomych, których może być nawet 40 000 dla jednego użytkownika ?

2mln relacji z wykorzystaniem cache to pikuś dla bazy danych

Widać, że nie masz doświadczenia z dużymi serwisami. 2 mln rekordów w bazie danych to nie jest żaden niszczyciel. Do takich tabel do tego używa się zakładek itp. (INDEX)

Jeżeli Twoja wiedza wysiada już na tym poziomie to nie radzę się brać za pisanie dużego serwisu, bo to znacznie więcej roboty niż się wydaje.

10 000 userów to przykład a co w przypadku gdy będzie ich 100 000?
Wtedy relacji znajomych będzie 20 mln.

Z tym bym jeszcze jakoś sobie poradził ale większy problem mam z relacją user -> znajomi znajomych.
Gdybym chciał wyświetić znajomych znajomych zakładając, że każdy ma po 200 znajomych wtedy dla jednego usera byłoby 40 000 relacji.
dla 100 000 userów byłoby ... tego już by baza mogła nie pociągnąć.



Z tym bym jeszcze jakoś sobie poradził ale większy problem mam z relacją user -> znajomi znajomych.
Gdybym chciał wyświetić znajomych znajomych zakładając, że każdy ma po 200 znajomych wtedy dla jednego usera byłoby 40 000 relacji.
dla 100 000 userów byłoby ... tego już by baza mogła nie pociągnąć.

Ale jak? Na jednej podstronie pokażesz wszystkich znajomych danego użytkownika razem ze znajomymi tych znajomych? Nie wydaje mi się. Prędzej już będzie jakiś link "Pokaż znajomych" i wtedy wykonywane jest jedno zapytanie. I dlaczego od razu zakładasz, że Twój portal będzie miał 100k użytkowników i każdy z nich po kilkaset znajomych? Chyba trochę się przeliczasz . Poza tym nawet w takim wypadku dla dobrze napisanego, zoptymalizowanego skryptu to nie jest żaden problem. Martwiłbym się dopiero jakby portal miał mieć miliony użytkowników i każdy z nich po kilkaset znajomych.

// ADD

Z tego co pamiętam facebook używa MySQL*. Miliardy użytkowników, pierdylion znajomych, a jakoś ciągnie, prawda?

* - z tego co pamiętam wprowadzili wiele high-performance enchancements

Chciałbym wyświetlić wpisy ale tylko znajomych moich znajomych więc w zapytaniu podaję SELECT * FROM tabela WHERE id_autor IN(idiki znajomych znajomych)
i właśnie czy mam zrobić podzapytanie aby pobrał wszystkie relacje znajomych z ich znajomymi czy lepiej jest trzymać w bazie idiki znajomych znajomych wlaśnie po przecinku z tym, że jak pisalem znajomych znajomych może być nawet 40 000 dla jednego użytkownika.

Jeśli tak bardzo obawiasz się proponowanego rozwiązania z nową tabelą to go nie adaptuj, Twoja decyzja. Wiedz jednak, że jest to jedyne prawidłowe rozwiązanie problemu. To wymyślone przez Ciebie (z idkami po przecinku) sprawi Ci więcej problemów niż pożytku.

Wydajność to jest jedna sprawa, dochodzi jeszcze zarządzanie taka listą, sortowanie itp.

Obawiam się, że ilość relacji będzie badzo szybko rosła.
Jak Twoim zdaniem wyświetlić wpisy znajomych znajomych? Zrobić relacje user->znajomy znajomego czyli dla jednego usera nawet 40 000 powiązań?
100 000 userów to 4 000 000 000 powiązań.
Czy za każdym razem pobierać znajomych a następnie ich znajomych, odrzucić duplikaty i dopiero zapytać o ich wpisy, wtedy i tak będę musiał użyć SELECT * FORM tabela WHERE id_autor IN(idiki znajomych znajomych)
Przetrzymując w tabeli idiki po przecinku robię mniej zapytań ale każda zmiana powoduje, że muszę rozdzielić po przecinku zapisując do tablicy idiki następnie zrobić na niej operacje i ponownie zapisać z przecinkami do bazy.

1. Zdajesz sobie sprawę z tego, że prawdopodobnie nie ma na świecie osoby, która byłaby zainteresowana wpisami 40 000 tysięcy znajomych jej znajomych? Musisz jakoś filtrować osoby, którymi użytkownik może być faktycznie zainteresowany.
2. Dla bazy danych typu MySQL ilość rekordów nie jest najistotniejszym czynnikiem jeżeli chodzi o jej możliwości. W dużym uproszczeniu, mówiąc półprawdę, pomiędzy 2 mln, a 20 mln jest ok. 20% skok złożoności obliczeniowej, a pomiędzy 2 mln i 2 mld 50%.

Przetrzymując dane tak, jak napisałeś, robisz tylko i wyłącznie burdel w bazie danych. Zamiast zrzucić tego typu powiązania na mechanizm kluczy obcych, na siłę wpychasz się w ślepą uliczkę. Prędzej czy później takie podejście do tematu się na Tobie zemści. Tego typu "relacje" będziesz miał o wiele trudniej nadzorować. Bo np. co w przypadku kiedy usuniesz jakiegoś usera z bazy danych? Będziesz przeglądał wszystkie inne rekordy w poszukiwaniu jego ID, żeby usunąć relacje? Mało tego, nie zrobisz tego z poziomu bazy danych, tylko będziesz musiał cudować z explode() w PHP.

Co jeśli będziesz chciał zapisywać datę dodania do znajomych, możliwość potwierdzania zaproszeń czy cokolwiek innego co jest związane z dodawaniem do znajomych? Bo w takiej tabelce łączącej dodasz dwie kolumny i po sprawie. Natomiast w formie ID'ków zapisywanych po przecinku będziesz musiał zacząć kombinować z nowymi tabelkami o nie wiadomo jakiej strukturze.

Tak więc zastosuj się do dobrej rady pyro, bardziej doświadczonego kolegi, z pierwszego postu, a oszczędzisz sobie niepotrzebnej roboty. I poczytaj trochę o relacyjnych bazach danych - na pewno Twoje aplikacje na tym zyskają.

1. Po co robić relacje user->znajomy znajomego? Skoro jednym zapytaniem, pobierasz znajomych i za pomocą JOIN'a znajomych znajomych
2. Tak jak Crozin wspomniał.. filtry + cache'owanie przefiltrowanych relacji.

to opiszę to nieco inaczej:
User ma znajomych i ok mogę zrobić relacje user->znajomy
W portalu jest katalog podzielony na kategorie i User wchodząc do odpowiedniej kategorii ma możliwość zobaczenia wpisów swoich znajomych a w drugiej zakładce wpisów znajomych jego znajomych. Wiem, że nikt nie będize przeszukiwał wpisów 40 000 osób ale jeśli będzie to jedna z kilkuset kategorii to tych wpisów będzie niewiele.
Problem w tym jak wyświetlić Użytkownikowi wpisy tylko znajomych jego znajomych z konkretnej kategorii zakładając, że kazdy może mieć po 40 000 znajomych znajomych.

Pytanko do pyro i pierwszego posta.
Dla jednej relacji tworzysz dwa wiersze czy jeden?

Jeden wiersz:
user_id - Marek
friend_id - Robert

user_id - Marek
friend_id - Łukasz

user_id - Łukasz
friend_id - Robert

czy przy pobieraniu listy znajomych pobierasz where user_id = Marek - tu ok.
A teraz chce znajomych Łukasza?

(pytam z ciekawości bo osobiście stosuje jeden wiersz ale może to warto przemyśleć).

tabela user
id
name

tabela relacji
id_user
id_friend

i w niej jeśli jest user o ID 5 i user o ID 6 wtedy wpisy są dwa
id_user=5 id_friend=6
id_user=6 id_friend=5

Ale ja się Ciebie nie pytam (sory, ale jak komuś przychodzą takie pomysły jak walenie idików po przecinku i szukanie po tym relacji to ja dziękuje za porady)

ech chyba źle mnie wszyscy zrozumieliście, wiem na czym polegają relacje i jak je w moim przypadku zastosować ale szukałem otymalniejszego rozwiązania.
Zamiast przeszukiwania milionów rekordów relacji aby wybrać liste idików wolałem je po prostu przechowywać juz wygenerowane i po prostu wklejać listę w zapytanie z IN().
Tak jak w przykładzie - mniej będzie zmian wśród relacji znajomych niż wyświelteń strony, która robi zapytanie po znajomych znajomych.

nie zgodzicie się ze mną?

Hm..

To może tak - ja ogólnie stosuje jedno wierszową relacje.
I jak trzeba wyciągnąć listę znajomych to normalne zapytanie, ale czasem muszę w dłuższym zapytaniu zawrzeć jakby warunek "tylko znajomy".
Np wyświetl zdjęcia znajomego.

[PHP] pobierz, plaintext 
if (!function_exists('friend_query'))
{
    /**
     * Return query string to get friend's records
     *
     * @param $user_id
     * @return string
     */
    function friend_query($user_id)
    {
        $CI =& get_instance();
        $user_id = (int)$user_id;
        return ' user_id IN (SELECT inviter_id FROM '. $CI->db->protect_identifiers('user_connections', TRUE) .' WHERE invited_id = '. $user_id .' AND status = 1 UNION SELECT invited_id FROM '. $CI->db->protect_identifiers('user_connections', TRUE) .' WHERE inviter_id = '. $user_id .')';
 
    }
}
[PHP] pobierz, plaintext 

To mam coś takiego - sklecam sobie normalnie zapytanie pobierające zdjęcia i wywołuje w nim helpera friend_query, który dodaje warunek.
Za pomocą UNION radzę sobie z tym, że relacja A-B i B-A nie jest rozdzielona.

@humman: Przechowywanie ID w postaci tekstu nie jest, i raczej nigdy nie będzie wydajniejsze od dodatkowej tabeli przeznaczonej na relacje. RDBMS-y są tak zbudowane by pracować na relacjach - i robią to bardzo szybko. Miliony rekordów w tabeli z relacjami nie stanowi żadnego problemu dla bazy danych.
@markonix: Mógłbyś zastosować pojedynczy rekord i przy wybieraniu danych podać:

[SQL] pobierz, plaintext 
... WHERE user_id = :uid OR fiend_id = :uid;
[SQL] pobierz, plaintext 

Ale na takie coś pozwolić sobie możesz jedynie w przypadku, gdy obie strony relacji zawsze będą miały dokładnie te same "właściwości". Ogólnie odradzałbym takie coś ze względu na niewygodne pobieranie danych i brak realnych zalet w stosunku do "klasycznego" rozwiązania z dwoma rekordami.

Tzn moje relacje to po prostu zapraszający, zaproszony, status i data.
Samo OR nie wystarcza bo ok - WHERE jest prawidłowy ale pytanie czy pobrać user_id czy friend_id.

No jedyna, konkretna i mierzalna zaleta to 50% mniej rekordów
Ale może jeszcze to przerobie póki mam taką szansę.

Ale czy ta mniejsza ilość rekordów przekłada się na jakieś faktyczne zalety? Zapewne masz co najmniej o rząd wielkości za małą ilość rekordów by sama ich ilość powodowała problem.

a czy nie uważasz, że jest to w pewnym sensie cache to co zastosowałem?
Wyświetlam użytkownikowi wpisy i zapisuję idiki i gdy ktos doda kolejny wpis po prostu kasuję wszystkim (do ktorych skierowany jest wpis czyli wszystkim znajomym) wpis z idikami. Przed wyświetleniem wyników sprawdzam czy jest wpis z idikami do wyświeltenia jeśli tak to robię select .. IN(idiki) a jeśli nie ma to generuję i zapisuję do tymczasowej tabeli.

Jeszcze oprócz jednego rzędu mniej to dochodzi za to prostsze wylistowanie wysłanych zaproszeń.
Przy dwu-wierszowej to już nie jest takie proste - wymagać będzie pewnie dodatkowej kolumny.

(ciągnę dyskusje bo mam nadzieje, że jeszcze ktoś inny poprze którąś z opcji tak abym miał pewność czy zmieniać czy zostawić).

rozwiazanie trzymania idkow w stringu jest MEGA nie optymalne.
1 jezeli trzymasz idki w kolumnie to wstawiajac do selekta zawartosc defakto otrzymasz cos takiego

[SQL] pobierz, plaintext 
IN (kolumna_z_idkami) co jest rowne IN ('123,345,2324325,453432,324234')
[SQL] pobierz, plaintext 

czyli musisz calego stringa rozbic na poszczegolne wartosci albo zastosować jakies instringi co jest bardzo czasochlonne w ilosci 50 000
2 jezeli juz chcesz miec IN (1,2,3,4,5,6,7,8....) to nie wiem jak mysql ale oracle np dopuszcza tylko 1000 pozycji w IN-ie
mozesz to obejsc robiad IN (select id from tabela) ale tutaj wiaza sie inserty

3 nawet jezeli chcesz pokazac znajomych -> znajomych -> twoich znajomych to rozwiazanie

[SQL] pobierz, plaintext 
SELECT * FROM tabela t1 JOIN tabela t2 ON t1.id = t.parent_id WHERE id = 2 //<-ja
[SQL] pobierz, plaintext 

i tak jest najlepsze lub tego typu podobne

4 poza tym wszystkim i zawsze stosuje sie paginacje bo nie sposob pokazac 10 000 znajomych na jednej stronie, zakladajac jeszcze ze pokazujesz jego zdjecie 1cmx1cm uzyskujesz 1m2 powierzchni wiec nie wiem jak chcesz to zrobic!!!!

Czyli jakby nie próbować tego zmienić to i tak najprostrze relacje będą najlepsze nawet jeśli będziemy przeszukiwać milionowe ilości rekordów?
user -> znajomy (1 user do 200 znajomych x2 bo w odwrotną stronę również)
prywatna wiadomość jako wątek id_wątku->user1 , id_wątku->user2
itp



add:

Co jeśli na stronie kilkakrotnie wykonuję zapytanie do bazy i za każdym razem pytam o wpisy znajomych czy nie lepiej raz pobrać idiki znajomych i używać ich przy kolejnych zapytaniach?

Od tego masz lazy loading - pobierasz raz i korzystasz z tego ile chcesz (google -> Symfony2 ORM lazy loading). Tak nawiasem mówiąc to strasznie kombinujecie

Załóżmy, że musisz pobrać coś od znajomych znajomych, użytkownika o id 11

[SQL] pobierz, plaintext 
SELECT
    f2.*
FROM
    friends f // znajomi
    LEFT JOIN friends f2 ON (f.user_2 = f2.user_2) //znajomi znajomych
WHERE
    f.user_1 = 11
[SQL] pobierz, plaintext 

wystarczy pobawić się w dopasowanie user_* w ON i po kłopocie

ps. Crozin, jeżeli właściwości są inne zawsze zostaje alternatywa w postaci IF/ELSE
ps2. oczywiście relacje w jednym wierszu to błąd gdy chcemy tworzyć bardziej skomplikowane zapytania. Jeżeli mamy dwa rekordy na relacje sprawa wygląda dość prosto (tak jak powyżej)

W oraclu sa widoki zmaterlizowane ale w mysql chyba nie ma czegos takiego, ale jezeli nie chcesz ciagle odpytywac o to samo to juz lepsze sa tabele temporary gdzie trzymasz dane jezeli istnieje sesja o ile wykorzytasz mechanizm sesji na bazie to latwo mozesz kontrolowac te dane wykonujac odpowiednie czynnosci w garbage collection.

O stringach naprawde zapomnij, zrob sobie prosty test, wygeneruj sobie dane gdzie masz w stringu te 10000 idkow i zrob 50 requestow z przegladarki i zobacz jak to chodzi

czy możesz napisać coś więcej o lazy loading jak to działa i jak to wywołać? w google na kilku stronach czytałem o tym ale nie rozumiem