JOIN parametryczny, słowa kluczowe + 5 typów zawartości Opcje

[WebCM]

Dodaję obsługę słów kluczowych. Z mapą/listą tagów nie ma problemu, ale schody zaczynają się, gdy chcę wyświetlić elementy, które mają przypisane konkretny tag.

[SQL] pobierz, plaintext 
SELECT TYPE,ID FROM tags WHERE tag = 'security'
[SQL] pobierz, plaintext 

Uzyskam TYP oraz ID elementów, ale brakuje tytułu, kategorii i ewentualnie opisu. Pewnie powiecie - JOIN załatwi sprawę. Istnieje 5 typów elementów (albo więcej), każdy ma swoją własną tabelę, np. arts, files, news...

Czy da się łączyć tabele wg warunku - na przykład gdy TYPE=1, łączymy z "arts"? W SQLite próbowałem CASE, ale wyskakuje błąd. Nie sprawdzałem w MySQL.

Ewentualnie: jazda 5 zapytań albo zastosuj UNION.

Dodatkowo trzeba sprawdzić, czy element jest włączony oraz czy kategoria, do której należy element, jest włączona (w osobnej tabeli cats, a więc dodatkowy JOIN jest nieunikniony).

Ze względu na tak skomplikowane operacje warto tworzyć CACHE, jednak liczę na sugestie związane z powyższym.

Ten post edytował WebCM 26.02.2010, 22:33:43

[Riklaunim]

Napisz zapytanie oparte o UNION ALL i zobacz jak szybko się wykonuje przy realistycznych ilościach danych. Jeżeli zapytanie miałoby być wykonywane często (np. przy wyświetlaniu jakiejś podstrony), a jego wynik nie zmieniałby się za często to oczywiście można wynik jakoś keszować - czy to przez plik, czy przez tabelę w bazie danych zawierającą bardziej wynikowe dane.

Ja generuję listę "co nowego", która na podstawie UNION ALL pobiera ostatnie rekordu z wielu tabel (newsy, artykuły, posty, katalog projektów, katalog prezentacji) i sortuje to po dacie dodania (wszystko ją ma) i mam gotową ładną listę. Zapytanie wykonuje się przy zapisie obiektu do jednej z powyższych tabel i ląduje gotowiec w bazie danych w oddzielnej tabeli na takie feedy dla poszczególnych serwisów (a operacje zapisu nie są częste). SQLka mniej więcej taka, pod SQLite:

Kod

SELECT
                'content', auth_user.username, date, title, description, is_update, changes, slug, content_type, author_id, Null, Null, Null
                    FROM rk_content$sid JOIN auth_user ON author_id = auth_user.id WHERE slug != 'index'
            UNION ALL
            SELECT
                'injector', auth_user.username, date, title, description, Null, Null, slug, Null, user_id, source, Null, Null
                    FROM feed_injector_injector JOIN auth_user ON user_id = auth_user.id WHERE $inject_col = 1
            UNION ALL
            SELECT
                'media', 'riklaunim', date, title, description, Null, Null, denormalised_title, Null, 1, media_type, Null, Null
                    FROM rk_media$sid WHERE accepted = 1
            UNION ALL
            SELECT
                'post', rk_post$sid.author, rk_post$sid.date, rk_topic$sid.name, rk_post$sid.text, rk_topic$sid.posts, is_locked, rk_topic$sid.last_pagination_page,
                rk_post$sid.topic_id, rk_topic$sid.is_solved, rk_topic$sid.is_external, rk_post$sid.author_anonymous, rk_post$sid.author_system_id FROM rk_post$sid
                    JOIN rk_topic$sid ON rk_post$sid.topic_id = rk_topic$sid.id
            ORDER BY date DESC LIMIT 12

[WebCM]

Działa, ale na pewno dałoby się napisać zapytanie lepiej:

[SQL] pobierz, plaintext 
SELECT
	i.ID,i.name
FROM
	arts i
INNER JOIN
	cats c
ON
	i.cat=c.ID
WHERE
	i.access=1 AND c.access=1 AND i.ID IN (SELECT ID FROM tags WHERE tag=? AND TYPE=1)
 
UNION ALL
 
SELECT
	i.ID,i.name
FROM
	files i
INNER JOIN
	cats c
ON
	i.cat=c.ID
WHERE
	i.access=1 AND c.access=1 AND i.ID IN (SELECT ID FROM tags WHERE tag=? AND TYPE=2)
 
ORDER BY i.name 
[SQL] pobierz, plaintext 

Co należałoby zoptymalizować? Obecnie:
1. Dla każdego typu elementów baza musi pobrać odpowiadające ID elementów
2. JOIN z tabelą cats pewnie też jest kosztowny

[Riklaunim]

Opanuj się trochę . Zapytania nie są kosmicznym wysiłkiem. Od lat relacyjne bazy danych te zapytania obsługują i internet jakoś się nie zawalił. Chcesz to optymalizować? To napisz stronkę w HTMLu i odpal na serwerze napisanym w asemblerze.... bo przecież ten Pehap i te okrutne zapytania SQL są takie obciążające dla serwera.
Jak chcesz skrypt ogólnoużytkowy na darmowe i tanie hostingi współdzielone, to raz że o wykorzystanie skryptu do stron o jakimś ogromnym ruchu nie musisz się martwić w początkowych latach istnienia projektu, a dwa że na takich hostingach nie masz dostępu do narzędzi jakie są wykorzystywane do skalowanie dużych serwisów na lepszych platformach hostingowych (memcache, gearman, sphinx/lucene/solr, czy też układy wieloserwerowe z load balancerem, czy replikacją bazy danych).


Notka: nie używaj subselectów (mają naturę do nie używania indeksów), wykonaj je oddzielnie i wstaw gotowe listy do tego zapytania.

Ten post edytował Riklaunim 27.02.2010, 14:25:05