witam,
mam tagi np.
-tag1
-coś tam 2
-test 3

I chcę znaleźć artykuł, który posiada najwięcej danych tagów (np. artykuł , który ma wszystkie tagi wyżej wymienione jest pierwszy)
Jak to osiągnąć najskuteczniej?

Lepszy przykład niż wykład, dlatego postaram się moją koncepcję opisać na przykładzie:

Mamy 3 artykuły, ich identyfikatory to 1, 2 oraz 3.
Mamy 3 tagi, 'koń', 'słoń', 'mysz'
Tagi mają swoje identyfikatory:
1: 'koń'
2: 'słoń'
3: 'mysz'

Artykuł o id:1 ma przypisane dwa tagi, 'koń' oraz 'słoń'

Tabela 'tag_by_art' wygląda mniej więcej tak:

id: identyfikator tagu
art: lista identyfikatorów artykułów..

I teraz gdy klient wpisze frazę:
"był sobie koń"
To wyszukanie pasujących artykułów w bazie jest bardzo proste:
1. Dzielimy frazę na słowa
2. Szukamy identyfikatów dla każego taga (słowa)
3. Szukamy w tabeli tag_by_art listy artykułów odpowiadających identyfiatorowi taga (słowa)

I to wszystko, obyło się bez nadmiarowości danych, bez pełnego przeszukiwania bazy (full_text_search)
Łatwo, szybko i przyjemnie.

nieźle ale jak wygląda struktura w przypadku gdy chcemy do jednego tagu dodać więcej artykułów (id)
Wstawiamy je w kolumnie art i odzielamy jakimś separatorem np. ; a potem robimy explode?

Dokładnie

A ja mam inny pomysł ciut. Do bazy zapisujesz dla danego artykułu tagi sobie jako osobna kolumna. Nakładasz na nią index Fulltext. W zapytaniu dajesz MATCH ... AGAINST.... IN BOOLEAN MODE i robisz sortowanie po wyniku jaki daje. Boolean mode daje to, że jeśli wyraz istnieje - oznaczy jako true, jeśli nie to da false i nie będzie ważne czy wyraz trafił się raz czy sto w tagach Tu i tak jest tylko 1 dla true, a 0 dla false dla każdego słowa z osobna Tak więc trafi 3 z 5 to dostanie w wyniku 3 punkty, a trafi wszystkie 5 to dostanie 5, nieważne czy któryś z wyrazów w tagach powtórzy się raz czy 10 razy Fulltext ma jednak swoje ograniczenia, więc zapoznaj się wcześniej z nimi, czy aby na pewno to jest to, o co Ci chodzi.

Oczywiście jest to jedenz pomysłów, który odrzucił wNogachSpisz, ale przeszukiwanie tagów przy jego użyciu powinno być szybkie, gdyż mamy ograniczoną do tagów jedynie treść indeksowaną, a nie długie teksty.

Całe tagi?
A nadmiarowość danych?
Baza będzie wielokrotnie większa niż mogła by być..
Nie wspomne o tym, że optymalność takich zapytań pozostawia (delikatnie rzecz ujmując) dużo do życzenia..

Problemem pomysłu wNogachSpis jest aktualizacja danych. W przypadku edycji musisz bowiem pobrać wiersze tagów gdzie ów artykuł jest i w razie czego go z nich usunąć i ewentualnie dodać do innych, uprzednio sprawdziwszy, czy taki tag już nie istnieje. Całość więc wygląda tak:
Pobierz wszystkoie wiersze tagów powiązane z artykułem. Sprawdź czy obecnie wpisane się pokrywają. Jeśli w nowych jakiś jest inny to znajdź czy jest w bazie ten tag i dodaj artykuł do niego. Jeśli w starych tagach jest jakiś, którego nie ma w nowych to usuń artykuł ze starych. W efekcie rozwiązanie to ma sens jedynie dla baz gdzie retencja tagów nie jest duża, bo może dojść do sytuacj, że większość operacji to będzie aktualizacja struktury tagów. A to Ci zarźnie maszynę bo całe działania masz rozbite zarówno na php jak i bazę. Wyszukiwanie identycznie, tylko że tu zamiast insert-update masz same selecty. Musisz przemyśleć co będzie wydajniejsze.

A co do sytuacji gdy masz kolumnę tagi: "tagA, tagB, tagC" to owszem, masz nadmiarowość, ale czasem taka jest lepsza w ostatecznym rozrachunku niż optymalizacja na siłę by baza nie miała redundantnych danych i cudowanie choćby z tabelami łączącymi. Możesz wylać dziecko z kąpielą i dopiero wtedy bazę zajedziesz niesamowicie.

Sam powiedz... Dla forum mam za każdym zapytaniem liczyć ile postów ma użytkownik czy lepiej tę liczbę zapisać w jego profilu? A tu mamy podobny przypadek. I tak - rozumiem Twoje podejście i rozumiem, że jest lepsze niż niektóre pomysły "optymalizatorów"

- Ile będzie tagów?
- Ile będzie artykułów?
- Jak dużo będzie zapytań select?
- Jak dużo będzie zapytań insert?

Bez tych informacji nie da się zaprojektować optymalnej bazy.
Twój pomysł osiągnie maksimum wydajności w zupełnie innych warunkach niż mój.
Proponuję przerwać dyskusję dopóki nie zostaną udzielone odpowiedzi na powyższe pytania.
Uważam że bez tego dyskuja nie będzie merytoryczna.

I ja tak uważam Bo od tego zależy wybór silnika, wybór rozwiązania i nawet to jak podejść do problemu. Ja bym nawet dodał "Czy będą występowały pewne tagi w przeważającej ilości artykułów?" oraz "Jak traktujemy tagi wielowyrazowe" bo na bank wiesz co jest minusem full-text-search i masz świadomość jego wad w określonych wypadkach wNogachSpisz.

Zawsze można też robić tabelę złączeniową z tagami i jechać na GROUP BY W sumie to by było najprostsze rozwiązanie chyba i nie tak znowu najgłupsze.

Ale najpierw niech autor odpowie na pytania, bo inaczej zacznie nam się teoretyczna dyskusja o wyższości jednych świąt nad drugimi

-Artykułów będzie sporo a tagów jeszcze więcej (do jednego artykułu 2-15 tagów)
-Zapytań select będzie więcej niż insert
-Będzie cache także różnice wydajnościowe już mnie nie interesują byle to wygodnie zrobić

W ostateczności zrobię starą metodą full text bo tak jak już wspomniałem będzie cache na długi okres czasu więc nawet na komórce by to poszło
LIKE ... OR ... OR...

2) Mam jeszcze drugie pytanie skoro zeszliśmy na drogę optymalizacji. Jeśli moim identyfikatorem nie jest id (np. "5") newsa a jego link (np. "tytul-newsa-jakis")
to z czym to się wiąże, jakie są minusy?
Plusy to na pewno seo friendly a minusy?

"Sporo" to pojęcie bardzo nieprecyzyjne. Tysiące? Setki tysięcy?? Miliony?

To, że SELECT będzie więcej niż INSERT to wiadomo... Chodzi bardziej o częstotliwość na jednostkę czasu. Ile przewidujesz SELECT a ile INSERT na dajmy na to minutę

I tu się mylisz. Częste INSERT lub UPDATE powodują częstszą wymianę danych w cache'u by był aktualny, więc mówienie takich rzeczy stoi na pograniczu nieświadomości i herezji Każde z rozwiązań bowiem nieco inaczej może z cache'm współpracować pod kątem ilości wymienianych danych choćby. Tak więc zmniejszyc możesz responsywność serwisu mimo wszystko.

Jakie są minusy tytul-artykulu zamiast jego id? Wydajność choćby. W przypadku używania id, musisz dodatkowo uzyć routera, który rozpozna prawidłowy artykuł. Zwłaszcza jeśli masz doczynienia z polskimi znakami, które htaccess z racji działania na latin-1 źle zinterpretuje, nie wspominając już o tym, że wyszukiwanie w bazie po stringu jest bardziej czasochłonne niż po integer. Nawet jeśli oba byłyby kluczami typu primary.

a jak byś zrobił szukanie artykułów po tym co mają w treści (a to pole tekstowe dosyć długie powiedzmy średnio 2000 znaków) ?
Kryterium szukania like dla kolumny tresc_artykulu to dobre rozwiązanie i czy idzie zajechać serwer mając np. 10.000 odwiedzin dziennie?
Jak to zoptymalizować oprócz długiego cache?

Jeśli miła by być CAŁA treść artykułu do sprawdzania to zastanawiałbym się najbardziej nad MATCH AGAINST, ponieważ jest to zaimplementowane w bazie, a każdy inny, kombinowany sposób, wiązałby się już z samodzielnym pisaniem ewentualnego rozwiązania i dokompilowywania do binarki, co raczej nie wchodzi w Twoim wypadku w grę. Niestety nie ma w takim wypadku dobrego rozwiązania, ponieważ LIKE nie korzysta z indeksów i rżnie bazę aż żal patrzeć, a z kolei MATCH AGAINST ma swoje "wydajnościowe" kruczki, które sprawiają, że nie można mu w pełni ufać jeśli chodzi o trafność. Jednocześnie ten drugi najmniej obciąży silnik, więc ostatecznie uważam, że konieczne jest... użycie obu w kolejności MATCH AGAINST -> LIKE w określonych sytuacjach, by nie wpaść w pułapkę złego wyniku.

powiedzmy, że mam strukturę bazy taką:

ARTYKUŁY:
ID | TRESC_ARTYKULU

NEWSY:
ID | TRESC_NEWSA

I chciałbym aby wyszukał z treści artykułu i treści newsa twoim sposobem to jak to mam zrobić łącząc dodatkowo like?

Jao że są to dwie osobne tabele, to MATCH AGAINST będzie użyty w obu jednocześnie. Tutaj wejdzie w grę UNION. Dodatkowo będziesz musiał sprawdzać czy wyrazy podane przez usera trafiają w przestrzeń akceptowalną przez MATCH AGAINST, a więc czy mają określoną liczbę znaków w wyrazie i czy w wyniku coś dostajemy. od tego będzie zależało jak "załatamy" wyniki. W najmniej korzystnym przypadku, po zrobieniu MATCHa, będziemy robili LIKE dla każdego wyrazu z osobna i po stronie php montować wyniki, co niestety wydajne nie będzie za żadne skarby świata...