[MySQL] losowe rekordy z każdej kategorii

napisałem sobie skrypt galerii, podział jest na kategorie (bez zdjęć) i foldery (zawierające zdjęcia i przypisane do dokładnie jednej kategorii). Każde zdjęcie posiada w swoim rekordzie (w bazie) zarówno ID folderu, jak i ID kategorii. Chciałbym jak najmniejszym obciążeniem bazy wyciągnąć listę kategorii z losowymi zdjęciami. Podczas, gdy samo wyciągnięcie listy to pikuś (SELECT *), to wyciągnięcie tych losowych zdjęć robi schody :/

struktura tabeli ze zdjęciami:

• id int primary
• kategoria int index
• folder int index

próbowałem m.in. tak:
SELECT DISTINCT(kategoria), id FROM zdjecia ORDER BY kategoria, RAND()
SELECT kategoria, id FROM zdjecia GROUP BY kategoria ORDER BY kategoria, RAND()

ale nic nie pomaga - nie ma pożądanego efektu "losowania" :/

czemu ORDER BY kategoria, RAND()? Jak posortujesz kategorie wedlug kategorii, to ten RAND raczej niewiele zmieni. Sprobuj samo order by rand()

bo chce, zeby z kazdej kategorii po jednym zdjeciu wylosowal. Zreszta z samym rand tez probowalem...

coś takiego mi się nasuwa na myśl, nie wiem czy w mysqlu to ruszy (w postgresie pewnie tak, ale nie próbowałem)

SELECT DISTINCT kategoria, (SELECT id FROM zdjecia WHERE id = RAND() AND id IN (select id from zdjecia)) FROM zdjecia

Takie cos u mnie poszlo - nie mam pojecia czy jest jako tako optymalne, ale dziala

select (select id from zdjecia where kategoria = parent.kategoria order by RAND() LIMIT 0,1 ) id, kategoria from zdjecia parent group by kategoria order by kategoria

podzapytania - nie optymalne :(

Nie wierzę, że takiej rzeczy nie da się jednym zapytaniem prostym zrobić :/. Ale będę próbował...

Jeśli liczba kategorii jest znana i w miarę stała, to można to zrobić następująco...

(select kategoria, id from zdjecia
where kategoria = 'Nowości'
and datediff(now(), data_zdjecia) <4
order by rand()
limit 3)

UNION


(

(select kategoria, id from zdjecia
where kategoria = 'nazwa kategorii pierwszej'
order by rand()
limit 1)

UNION

(select kategoria, id from zdjecia
where kategoria = 'nazwa kat. 2'
order by rand()
limit 1)

UNION
....

UNION

(select kategoria, id from zdjecia
where kategoria = 'nazwa kat. 7'
order by rand()
limit 1)

ORDER BY data_zdjecia desc
)

Możesz zmieniając wartości 'limit' pokazywać po 1, 2, 3 .. zdjęcia, i to niezaleznie dla każdej kategorii,
Nawiasy w zapytaniach składowych są niezbędne dla zachowania składni dla klauzuli ORDER BY, a ostatnie order by, poza nawiasami sortuje otrzymany wynik, np. po dacie zdjęcia. Na początku idą 3 zdjęcie z kategorii 'nowości', ale nie starsze niż trzy dni.. potem z pozostałych kategorii w/g daty...Sorki że troche poszalałem, ale nie wiem czemu klauzula UNION choć tak prosta w użyciu jest dziwnie pomijana... :) Ja z niej korzystam dość często i bardzo mi pomaga w wielu wydawać by się mogło 'trudnych" zapytaniach..

Niestety, po dodaniu nowej kategorii trzeba zmieniać zapytanie, ale może mieć to też te plusy, że do losowego wyświetlania nie idzie każda nowa kategoria założona przez usera (jeśli ma taką możliwość), ale jedynie już te które zaakceptujesz...

Oczywiście możliwe jest napisanie kodu który będzie najpierw pobierał unikalne nazwy kategorii, a potem w pętli budował zapytanie dla kazdej kategorii... w php to ma być? wieczorem będe miał więcej czasu to mogę Ci napisać... ;) )

dzieki za zaangazowanie, ale juz na innym forum dostalem odpowiedz :). Twoje zapytanie jest - na oko - niewiele szybsze od zapytania johny'ego, ja natomiast uzylem tego:

SELECT `kategoria`, SUBSTRING_INDEX( GROUP_CONCAT( `zdjecie` ORDER BY RAND() ), ',', 1) AS `zdjecie`
FROM `tabela` GROUP BY `kategoria`

i obawiam się, że w mysql jest to zapytanie optymalne :(

ale nie dostalem tam odpowiedzi na pytanie: "GROUP_CONCAT" wczytuje cały wynik, czy tylko przechowuje "wskaźnik" na rekordy? kluczowa sprawa, jeśli chodzi o wydajność :)

Hmm.. ciekawe zagadnienie... aż puszcze Twoje rozwiązanie wersje na explain...

W 'moim' rozwiązaniu wykonywane jest kilkakrotnie proste zapytanie, a jego wynik dołączany do wierszy wynikowych - ponieważ użyłem ograniczenia LIMIT, dla każdego ze składowych zapytań przetwarzany jest tylko jeden wiersz (no, dla 'Nowości' aż trzy) tak więc dla Twojego pierwotnego założenia przetworzonych zostanie dokładnie tyle rekordów, ile masz kategorii...

Twoje rozwiązanie używa klauzuli GROUP BY, co w połączeniu z brakiem klauzuli WHERE daje prawo przypuszczać, że przetwarzane są wszystkie wiersze tabeli... ale pewien na 100% nie jestem...

Już sprawdziłem... oczywiście użyłem własnej tabeli, ale z identyczną ideologią, czyli zlecenia pogrupowane w działy... indexy zarówno na pole dział jaki zlecenie.. przetwarza wszystkie rekordy w tabeli ;)

pobawilem sie explainem i.. wyszlo mi ze zwykle

SELECT DISTINCT kategoria FROM zdjecia

przelatuje wszystkie wiersze o_O. indeksy zalozone...

poddaje sie, będzie cache... nie mam ochoty na union, bo przy kilkudziesieciu kategoriach takie zapytanie to bedzie megapotworek...

tomkiewicz napisał(a)

indeksy zalozone...

na jakich polach?

na wszystkich :D

jedna tylko rzecz dziwna... właśnie zauważyłem, że mam tylko na id zdjecia primary moc=liczba zdjec, a kategoria ma w kolumnie "moc" wpisane "Brak" o_O. Dopiero jak dałem "ANALYZE", to się wpisały...

mimo to dalej w explain "select distinct kategoria from galeria" podaje mi tyle "rows" ile mam rekordów o_O, poza tym w komórce extra mam "Using index", za to w possible_keys mam "NULL". Może mechanizmy optymalizacji załączają się od określonej ilości rekordów w tabeli?

a mi sie wydawało, że znam się co nieco na optymalizacji baz danych o_O

Wydaje mi sie, że indexy używane sa do instrukcji wybierających, a nie grupujących, nie masz klauzuli WHERE, więc grupowanie odbywa się tak jak powinno, na wszystkich rekordach?

A czy przy cashe nie robisz przypadkiem dokładnie tego samego co w union, tyl eże zamias megapotworka masz kilkadziesiąt małych potworków ;)

Zawsze można porównać czasy różnych rozwiązań wykonując je w pętli, większą ilość razy - czasem wyniki bywają zaskakujące w porównaniu z explain i logiką...

małych potworków? Po prostu dodatkowa kolumna w tabeli kategorie, codziennie aktualizowana z crona - w tym przypadku wydajność na marginalne znaczenie (w granicach rozsądku ofc).

Zdecydowałem - chyba żadne rozwiązanie nie będzie nawet w procencie tak wydajne jak cache :P