Jak zaimplementować system tagowania podobny do SO w php / mysql?
Koduję stronę w PHP / MySQL i chciałbym zaimplementować podobny do stackoverflow tagging engine. Mam 3 odpowiednie tabele w DB: 1. Pozycje 2. Tagi 3. ItemTagMap (mapuje znaczniki do elementów, n:n mapping)
Teraz na stronie wyszukiwania chciałbym pokazać odrębną listę wszystkich tagów dla całego wyniku wyszukiwania (nie tylko bieżącej strony), aby użytkownicy mogli "udoskonalić" swoje wyszukiwanie, dodając/usuwając tagi z tej listy tagów.
Pytanie jest takie, że to dość ciężkie pytanie na DB i tam może być mnóstwo żądań wyszukiwania, które skutkują różnymi zestawami wyników, a tym samym różnymi zestawami znaczników.
Czy ktoś wie jak to skutecznie wdrożyć?
3 answers
Zanim przejdziemy do trybu , warto przyjrzeć się poniższemu szablonowi zapytań. Jeśli nic innego, można to wykorzystać jako punkt odniesienia, w stosunku do którego można zmierzyć skuteczność możliwych optymalizacji.
SELECT T.Tagid, TagInfo.TagName, COUNT(*)
FROM Items I
JOIN Tags TagInfo ON TagInfo.TagId = T.TagId
JOIN ItemTagMap T ON I.ItemId = T.ItemId
--JOIN ItemTagMap T1 ON I.ItemId = T1.ItemId
WHERE I.ItemId IN
(
SELECT ItemId
FROM Items
WHERE -- Some typical initial search criteria
Title LIKE 'Bug Report%' -- Or some fulltext filter instead...
AND ItemDate > '02/22/2008'
AND Status = 'C'
)
--AND T1.TagId = 'MySql'
GROUP BY T.TagId, TagInfo.TagName
ORDER BY COUNT(*) DESC
Zapytanie podrzędne to "zapytanie kierujące", tzn. takie, które odpowiada początkowym kryteriom użytkownika końcowego. (zobacz poniżej szczegółowe informacje na temat tego, jak to zapytanie, wymagane wiele razy może zmieścić się w ogólnie zoptymalizowanym przepływie) Commented is the JOIN on T1 (i ewentualnie T2, T3, gdy wybrano kilka znaczników) oraz, wraz z klauzulą WHERE, powiązane kryteria. Są one potrzebne, gdy użytkownik wybierze konkretny tag, czy to w ramach wstępnego wyszukiwania, czy przez udoskonalenie. (Może być bardziej efektywne umieszczenie tych łączy i klauzul where w pod-zapytaniu; więcej o nich poniżej)
Dyskusja.. "Zapytanie kierujące" lub jego odmiana jest potrzebna do dwóch różnych celów:
1 aby zapewnić uzupełnij listę ItemId, która jest potrzebna do wyliczenia wszystkich powiązanych tagów.
2 aby podać pierwsze n wartości ItemId (N jest wyświetlany rozmiar strony), w celu wyszukania szczegółów elementu w tabeli elementów.
Należy pamiętać, że pełna lista nie musi być sortowana (lub może korzystać z sortowania w innej kolejności), przy czym druga lista musi być sortowana na podstawie wyboru użytkownika (powiedzmy według daty, malejąco lub według tytułu, Alfabetycznie rosnąco). Zauważ również, że jeśli wymagana jest jakakolwiek kolejność sortowania, koszt zapytania będzie oznaczać zajmowanie się pełną listą(nieśmiała od nieparzystej optymalizacji przez sam SQL i / lub pewną denormalizację, SQL musi "zobaczyć" ostatnie rekordy na tej liście, w przypadku, gdy należą do górnej, sortowania).
Ten ostatni fakt, jest na korzyść tego samego zapytania dla obu celów, odpowiednia lista może być przechowywana w tymczasowej tabeli. Ogólnym przepływem byłoby szybkie wyszukuje N rekordów pozycji z ich szczegółami i zwraca je do aplikacji na raz. Aplikacja może następnie uzyskać ajax-fashion listę tagów do udoskonaleń. Lista ta będzie generowana z zapytaniem podobnym do powyższego, gdzie zapytanie podrzędne jest zastępowane przez "select * from temporaryTable"."Szanse są dobre, że optymalizator SQL zdecyduje się posortować tę listę( w niektórych przypadkach), pozwólmy mu to zrobić, zamiast zastanawiać się i sortować ją jawnie.
One innym punktem do rozważenia jest umieszczenie join (s) W tabeli ItemTagMap wewnątrz "zapytania jazdy", a nie jak pokazano powyżej. Prawdopodobnie najlepiej jest to zrobić, zarówno dla wydajności, jak i dlatego, że stworzy odpowiednią listę dla celu #2 (Wyświetlanie strony elementów).
Query/flow opisane powyżej prawdopodobnie będzie skalowane dość dobrze, nawet na stosunkowo skromnym sprzęcie; wstępnie do 1/2 miliona+ elementów, z ciągłymi wyszukiwaniami użytkowników może do 10 na sekundę. Jeden z kluczowych czynnikiem byłaby selektywność pierwotnych kryteriów wyszukiwania.
Pomysły optymalizacyjne
- [W zależności od typowych przypadków wyszukiwania i statystyk danych] może mieć sens denormalizowanie, wprowadzając (rzeczywiście powielając) niektóre pola elementów do tabeli ItemTagMap. W szczególności krótkie pola mogą być tam "mile widziane".
- ponieważ dane rosną w milionach + elementów, możemy wykorzystać typowo silną korelację niektórych tagów (np. MySql, btw często bez powodu...), z różnymi sztuczkami. Na przykład wprowadzenie znaczników" multi-Tag " może uczynić logikę wprowadzania nieco bardziej skomplikowaną, ale może również znacznie zmniejszyć rozmiar mapy.
-- 'nough powiedział! --
Należy dobrać odpowiednią architekturę i optymalizacje w świetle rzeczywistych wymagań i efektywnego profilu statystycznego danych...
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-07-21 23:01:16
Będziesz chciał spróbować zminimalizować liczbę wywołań DB, wkładając ciężką pracę w PHP.
Najpierw wybierz wszystkie elementy z DB:
select * from items where (conditions);
Następnie utwórz tablicę wszystkich id z zestawu wynikowego.
$ids = array();
foreach ($items as $item) {
$ids[] = $item['id'];
}
$ids = implode(',' $ids);
Następnie wybierz Wszystkie Itemtagmapy i Powiązane Dane tagów dla wcześniej pobranego identyfikatora elementu.
select map.item_id, t.id, t.name from tags t, item_tag_maps map where t.id = map.tag_id and map.item_id in ($ids);
Teraz, gdy zapętlasz tablicę $items, możesz zlokalizować wszystkie pasujące tagi z 2. zapytania SQL, które wykonałeś, o ile ma pasujące wartość item_id.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-10-07 01:45:00
Zakładając:
- Item (id);
- Tag (id, nazwa) z indeksem na nazwie;
- ItemTag (item_id, tag_id).
Wtedy:
SELECT t.name
FROM Tag t
WHERE EXISTS (SELECT 1 FROM ItemTag WHERE item_id = 1234)
ORDER BY t.name
SELECT t.name
FROM Tag t
WHERE t.id IN (SELECT tag_id FROM ItemTag WHERE item_id = 1234)
ORDER BY t.name
SELECT DISTINCT t.name
FROM Tag t
JOIN ItemTag i WHERE i.tag_id = t.id
WHERE i.item_id = 1234
ORDER BY t.name
Myślę, że pierwszy będzie szybszy, ale jak zawsze w przypadku SQL, warto go przetestować (na wystarczająco dużym zestawie danych).
Powyższe zostały wykonane do listy znaczniki dla pojedynczego elementu. Chcesz mieć złożony zestaw tagów do wyników wyszukiwania. To nie jest trudne z powyższego, ale to zależy od tego, jak uzyskać wyniki wyszukiwania.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-07-21 22:59:57