Optymalizacja zapytania SQL Opcje

[tuptak6]

Witam

Po wielu próbach udało mi się napisać zapytanie które spełnia wszystkie moje warunki i wyświetla wszystkie potrzebne dane:

Przykład z wszystkimi warunkami i uzupełnionymi danymi

[SQL] pobierz, plaintext 
	SELECT m.*, mc.*, l.*, 
 
	(SELECT GROUP_CONCAT(cc.cat_name) 
	FROM category_movies cc, movies_cats mm 
	WHERE mm.movie = m.movie_id 
	AND cc.cat_id = mm.cat ) AS moviecat_names, 
 
	(SELECT GROUP_CONCAT(cc.cat_id) 
	FROM category_movies cc 
	LEFT JOIN movies_cats mm ON ( cc.cat_id = mm.cat ) 
	WHERE mm.movie = m.movie_id ) AS moviecat_ids, 
 
	(SELECT GROUP_CONCAT(lang) 
	FROM links_movies 
	WHERE movie = m.movie_id 
	GROUP BY lang) AS lang_ids 
 
	FROM movies m, movies_cats mc , links_movies l 
	WHERE m.movie_id = mc.movie 
	AND m.movie_year >= '1900' 
	AND m.movie_year <= '2012' 
	AND m.movie_rate >= '0' 
	AND m.movie_rate <= '10' 
	AND m.movie_id = l.movie 
	AND l.lang IN (1) 
	AND mc.cat IN (1) 
	GROUP BY mc.movie 
	ORDER BY m.movie_title ASC 
	LIMIT 0,10 
[SQL] pobierz, plaintext 

Wszystko było by ładnie pięknie ale strona się ładuje bardzo długo
Czy pomoże mi ktoś to ogarnąć ?

Pozdrawiam

[redeemer]

Zamiast subselectów spróbój zastosować JOINy. Jakie masz indeksy w tej bazie? Co pokazuje EXPLAIN?

[strife]

Zobacz co mówi EXPLAIN, zobacz czy masz pozakładane poprawnie indeksy.

[Tomplus]

Miałem ostatni tak sam problem.
Zapytanie normalnie działało mi super, szybko, ale dodając jedną tabelę za dużo, nagle ładowanie strony przekroczyło nawet cierpliwość programisty.

Wystarczyło zastosować dla odpowiednich tabel JOIN LEFT,RIGHT lub OUTER i zapytanie się przyśpieszyło jeszcze lepiej. Więc teraz mam w jednym zapytaniu i subselecty, i joiny.

[tuptak6]

Dodałem indexy i zapytanie śmiga szybko
Nowy explain:
(IMG:http://fooh.pl/pokaz/1715625708.jpg)

Ale czy jest jakaś jeszcze opcja optymalizacji ?

Ten post edytował tuptak6 18.07.2012, 14:20:39

[thek]

Orzeszku... Nie prościej było to pojoinować? Z tego co widzę, bierzesz filmy i wyciągasz wszystkie kategorie filmu, id tych kategorii i coś z językami oraz linkami (nie znam struktur tabel). Do tego w where zamiast tylu AND można by zakresy trzasnąć w BETWEEN. No i te "using temporary" źle wróży na przyszłość przy twoim iloczynie kartezjańskim na 3 tabelach.

[tuptak6]

W takim razie jak pozbyć się "Using temporary;" ?

Ten post edytował tuptak6 19.07.2012, 12:54:15

[alegorn]

jesli masz w zapytaniu order by - wymuszasz poniekad stosowanie tabel tymczasowych.
nie zawsze sie da uniknac tempa. i nie zawsze jest to problem dla zapytania.
problem pojawia sie wtedy - gdy ilosc danych jaka zapisujesz do tempa - przekracza dostepnej pamieci (ramu), wtedy nastepuje to co najgorsze - zapis na dysk.

rada? ograniczaj maksymaline ilosc danych, do momentu w ktorym masz sortowac dane. (jesli wrecz nie zrezygnowac z sortowania)
czasem, lepszym rozwiazaniem jest wlasne przygotowanie tabeli tymczasowej. z pozakladanymi odpowiednimi indeksami mozna uzyskac rewelacyjne czasy dostepu.
pamietajac ze innodb domyslnie sortuje wg najwazniejszego indeksu - nie wg kolejnosci dodania - da sie uzyskac tabele z posortowanymi danymi gotowa do natychmiastowego uzycia

czasem, przy tworzeniu takich tymczasowych tabel, warto sie zaineresowac silnikiem memory.
zajmuje ram - ale daje rewelacyjne czasy (IMG:style_emoticons/default/wink.gif) (oczywiscie wczesniej poczytaj co oznacza wybor tego silnika!)

j.

[thek]

@alegorn: Zwróć uwagę na ilość przy explain. 890 rekordów i temporary table już? To znaczy, że tu już coś może być lekko nie tak. I zauważ, że tak jest. Bez dokładnej struktury tabel oczywiście nie powiem tego na 100%, ale moim zdaniem większość podzapytań można by po prostu dodać z użyciem JOIN, które potem się pogrupuje.

[alegorn]

tutaj temporary jest potrzebne dla sortowania. koniec kropka.

nie jest ważne ile masz tam rekordów (10 czy 1k) zauwaz ze jest tutaj razem z filesort, jest to potrzebne do wykonania tej operacji.

tutaj, po wielu filtrowaniach/relacjach itp. silnik wybral odpowiednie rekordy z wielu źródeł. ostatnią operacją jest posortowanie tego i zwrot. by to wykonać - silnik pakuje to do tempa i wykonuje sortowanie (order by)i porcjowanie (limit)

zauważ, że ilekroć będziesz używał order by - będziesz miał w explainie: using temporary (przynajmniej wiecej niz 90%, dla joinowanych tabel)

to ze tutaj da sie przebudować to zapytanie? zgoda! ze da się napisać lepiej? jasne! czy da się uniknąć tempa? niekoniecznie.

poza tym jak napisałem, póki wielkość danych w tempie nie przekracza wartości krytycznej - (tzn nie wymusza zapisu na dysk) to wielka tragedia się nie dzieje. dlatego powinno się maksymalnie ograniczać ilość wierszy i kolumn.

j.

edit: literówka

Ten post edytował alegorn 19.07.2012, 16:08:52

[tuptak6]

To co mam zrobić dalej ?

Powiem tak każdy warunek jest uzależniony od parametru w adresie URL.
Dlatego chociażby z tego powodu BETWEEN, nie jest możliwy.
Podobnie będzie z joinami nie zawsze wykorzystywane są wszystkie tabele.
Gdy np user chce linki z lektorem to wtedy dodawana jest trzecia tabela links_movie a normalnie jej nie ma. Wszystko zależy od warunków.
Wyżej podałem pełne zapytanie z wszystkimi warunkami

[thek]

A nie pomyślałeś o jakimkolwiek query builderze? Poza tym chyba składanie zapytania na podstawie parametrów nie jest trudne aż tak? Kwestia tylko odpowiedniego IF-owania i dokładania elementów do zapytania. Jeśli dobrze to przemyślisz to naprawdę nie jest problemem to.