Proste zapytanie prosta optymalizacja :) Opcje

[little_MASTER]

Witam wszystkich!
Mam malutki problem odnośnie zapytania.
Otóż mam zapytanko:

[SQL] pobierz, plaintext 
SELECT r.reg_nazwa AS region, w.woj_nazwa, o.typ, o.nazwa, o.ulica, o.kod, o.miasto, o.email, o.telefon, o.www, o.opis, o.notatki, o.cenaszkolenia, o.zdjecie FROM osoby o LEFT JOIN regiony r ON o.reg_id=r.reg_id LEFT JOIN wojewodztwa w ON o.woj_id = w.woj_id WHERE wew_id='E000001'
[SQL] pobierz, plaintext 

Które wybiera jakieś tam szczegóły z tabeli osoby, a że region jest jako id to wypadałoby pobrać nazwę z innej tabeli, podobnie dla województw.
Ogółem zapytanie działa (IMG:style_emoticons/default/smile.gif)

Ale...
To jedno zapytanie dla jak na razie 100 rekordów w tabeli osoby wykonuje się w 0,0006 sekundy (czyli dość szybko w/g mnie).

Tylko zastanawiam się jak to coś zoptymalizować, tzn same zapytanie, czy muszę robić dwa razy JOIN i czy koniecznie musi być LEFT, może jakiś inny.

A tak na marginesie czy w przypadku takich zapytań ma znaczenie jak ustawiłem indexy?

Dla tabeli osoby mam jako indexy: wew_id jako primary, reg_id i woj_id jako pomocnicze.

W razie potrzeby przedstawię strukturę wizualnie, choć wydaje mi się że jest ona dość zrozumiała (IMG:style_emoticons/default/smile.gif)

Prosiłbym o pomoc, wykładowca dość poważnie ocenia optymalizację zapytań, a chciałbym to jakoś spokojnie zaliczyć (IMG:style_emoticons/default/tongue.gif)

Z góry dzięki za pomoc.

//EDIT
Stworzyłem jeszcze takie zapytanko:

[SQL] pobierz, plaintext 
SELECT r.reg_nazwa AS region, w.woj_nazwa, o.typ, o.nazwa, o.ulica, o.kod, o.miasto, o.email, o.telefon, o.www, o.opis, o.notatki, o.cenaszkolenia, o.zdjecie FROM osoby o, regiony r, wojewodztwa w WHERE o.reg_id=r.reg_id AND o.woj_id = w.woj_id AND wew_id='E000001'
[SQL] pobierz, plaintext 

I teraz kolejne pytanie: które z tych dwóch jest lepsze? Które w przypadku dużej ilości rekordów będzie lepsze?

Wiem że mogę wygenerować 100tys albo i więcej rekordów i to sprawdzić, ale jednak wolałbym poradzić się doświadczonych koderów najpierw (IMG:style_emoticons/default/wink.gif)

Ten post edytował little_MASTER 25.10.2011, 11:37:00

[piotr.kazmiercza...]

Ogólnie jeżeli zależy Ci na prędkości działania bazy to:

1) Olej postacie normalne w bazie.

[SQL] pobierz, plaintext 
SELECT r.reg_nazwa AS region, w.woj_nazwa, o.typ, o.nazwa, o.ulica, o.kod, o.miasto, o.email, o.telefon, o.www, o.opis, o.notatki, o.cenaszkolenia, o.zdjecie FROM osoby o LEFT JOIN regiony r ON o.reg_id=r.reg_id LEFT JOIN wojewodztwa w ON o.woj_id = w.woj_id WHERE wew_id='E000001'
[SQL] pobierz, plaintext 

Zmień na

[SQL] pobierz, plaintext 
SELECT o.reg_nazwa AS region, o.woj_nazwa, o.typ, o.nazwa, o.ulica, o.kod, o.miasto, o.email, o.telefon, o.www, o.opis, o.notatki, o.cenaszkolenia, o.zdjecie FROM osoby o WHERE wew_id=1
[SQL] pobierz, plaintext 

Czyli co zmienić i dlaczego:
Jeżeli robisz dużo zapytań gdzie szukasz np informacji o użytkowniku jak w tym przypadku, wszystko co się da umieść w tej samej tabelce. Z zapytania widzę że potrzebujesz przy wyjmowaniu jedynie nazwę województwa w takim razie nie ma potrzeby JOINa. Jest to bardzo "sztywne" rozwiązanie jednak w znaczącym stopniu przyśpiesza zapytania.

2) Użyj MEMORY

Ogólnie jeżeli przechowujesz w bazie dane które będą rzadko/nigdy nie zmieniały używaj dla nich typu tablic: MEMORY. Tablice te będą przechowywane wyłącznie w pamięci więc dostęp do nich będzie dużooo szybszy. W tym wypadku do MEMORY wstawiasz rediony i wojewodztwa

3) Użyj widoków

Jeżeli spodziewasz się częstego odpytywania o dane. Stwórz z tych zapytań z Joinami gotowe widoki (View). Są to tymczasowe tabele utworzone z zapytań. Przydatne i szybkie rozwiązanie.

Wybór rozwiązania zależy od
- ilości danych (100tys to bardzo mało, zabawa zaczyna się przy 30 mil.)
- częstotliwości dodawania nowych danych (predykcja)
- częstotliwości aktualizacji istniejących danych(predykcja)

[little_MASTER]

Wszystko jest w tabeli użytkownicy, jedyne co potrzebuję to nazwę regionu z którego pochodzi użytkownik (relacja n-1 wielu użytkowników jeden region, czyli użytkownik może mieć wybrany tylko jeden.) podobnie z województwami.

Regiony będą się zmieniały sporadycznie, tzn czasami dojdą nowe, województwa, hmm to po jakiś zmianach administracyjnych w Polsce (IMG:style_emoticons/default/tongue.gif)

Czyli rozumiem żeby robić SELECTA z bazy osoby normalnie, a co z regionami i województwami? Jakiś przykładzik zastosowania tego MEMORY? (IMG:style_emoticons/default/wink.gif)

Osoby będą dodane raz (przeze mnie na potrzeby zaliczenia:)), ale w założeniu mam, że kilka osób może dodawać/aktualizować dane odnośnie osób z poziomu panelu administracyjnego, chodzi o to żeby mieć poziomy uprawnień, ale to poza tematem troszkę.
Wykładowca wpisał mi że powinna być możliwość dodawania/edycji/usuwania przez kilka osób na raz.

A odnośnie edycji/usuwania: jak zabezpieczyć tabelę osoby przed edycją/usunięciem rekordu w tym samym czasie?
Czyli ktoś edytuje rekord a druga osoba chce go usunąć, albo ktoś edytuje, a druga osoba chce to samo zrobić.
Myślałem o tym żeby zrobić timestamp w momencie kiedy zaczynamy edytować, tzn ktoś wchodzi w edycję jakieś osoby, w rekordzie w polu timestamp ustawia się czas+pół godziny i mamy czas na edycję, po zaptwierdzeniu zmian timestamp robimy null. W razie wyłączenia kompa/przeglądarki po pół godziny edycja będzie dostępna dla kogoś innego.

A co do widoków, to czy dużo pomagają przy częstych zapytaniach? Przykładowo 4 razy na sekundę? Strona i tak jest na smarty więc jest cachowana, ale zastanawiam się czy dodatkowo cachować wyniki z zapytań z bazy?

//BTW
Co z tabelą na silniku MEMORY kiedy zrobimy restart serwera? Tzn w tym przypadku mojego kompa? Czy dane są usuwane? Chyba tak skoro są w pamięci tylko. Ale czy są przywracane po ponownym starcie serwera MySQL?

Ten post edytował little_MASTER 25.10.2011, 19:28:12

[piotr.kazmiercza...]

Jeżeli to na jakieś zaliczenie to pomiń moją propozycje z umieszczeniem nazw regionów i województw w jednej tabeli bo Cie wykładowca na pal nabije (IMG:style_emoticons/default/smile.gif) .

Czyli rozumiem żeby robić SELECTA z bazy osoby normalnie, a co z regionami i województwami? Jakiś przykładzik zastosowania tego MEMORY? (IMG:style_emoticons/default/wink.gif)

MEMORY to tylko typ tablicy robisz do tego takie same zapytania jak każde inne.

A odnośnie edycji/usuwania: jak zabezpieczyć tabelę osoby przed edycją/usunięciem rekordu w tym samym czasie?
Czyli ktoś edytuje rekord a druga osoba chce go usunąć, albo ktoś edytuje, a druga osoba chce to samo zrobić.
Myślałem o tym żeby zrobić timestamp w momencie kiedy zaczynamy edytować, tzn ktoś wchodzi w edycję jakieś osoby, w rekordzie w polu timestamp ustawia się czas+pół godziny i mamy czas na edycję, po zaptwierdzeniu zmian timestamp robimy null. W razie wyłączenia kompa/przeglądarki po pół godziny edycja będzie dostępna dla kogoś innego.

Poczytaj o SQL Transaction

A co do widoków, to czy dużo pomagają przy częstych zapytaniach? Przykładowo 4 razy na sekundę? Strona i tak jest na smarty więc jest cachowana, ale zastanawiam się czy dodatkowo cachować wyniki z zapytań z bazy?

Widoki warto używać w momencie jak musisz robić JOIN na dużych tablicach. O ile to przyśpiesza to ciężko powiedzieć wszystko zależy od struktury danych.

//BTW

Co z tabelą na silniku MEMORY kiedy zrobimy restart serwera? Tzn w tym przypadku mojego kompa? Czy dane są usuwane? Chyba tak skoro są w pamięci tylko. Ale czy są przywracane po ponownym starcie serwera MySQL?

Trzeba to samemu oprogramować, że po restarcie SQL'a wstawiasz do tablic MEMORY dane na nowo. Trochę uciążliwe ale bardzo szybkie.

[little_MASTER]

Poczytaj o SQL Transaction

Co to transakcje to wiem (IMG:style_emoticons/default/smile.gif) (Tak mi się przynajmniej wydaje (IMG:style_emoticons/default/tongue.gif) )
mysqli ma mechanizm do tego. Mam zrobione tak:

[PHP] pobierz, plaintext 
//############### INSERTION ###############
		mysqli_autocommit($this->link, false);
		mysqli_query($this->link, "SET NAMES 'utf8'");
		$q = "insert into osoby zapytanie";
 
		mysqli_query($this->link, $q);
		if (mysqli_errno($this->link))
		{
			mysqli_rollback($this->link);
			return - 1;
		} else
		{
			$newID = mysqli_insert_id($this->link);
			$q = "UPDATE obiekty SET wew_id=CONCAT('E',substring('00000',length(osoba_id)),osoba_id) WHERE obiekt_id = $newID";
 
			$wynik = mysqli_query($this->link, $q);
			if (mysqli_errno($this->link))
			{
				mysqli_rollback($this->link);
				return - 1;
			} else
			{
				mysqli_commit($this->link);
				return 1;
			}
		}
		return - 1;
[PHP] pobierz, plaintext 

Ale bardziej chodzi mi o edycje osób.
Tzn i tak muszę zrobić selecta żeby wypełnić formularz edycji, a po zmianie czegokolwiek zrobić update. No chyba oczywiste jest (IMG:style_emoticons/default/smile.gif)
Tylko jak to zabezpieczyć żeby dwie osoby nie edytowały w tym samym czasie tej samej osoby.
Sytuacja jest prosta wydaje mi się, żeby jedna osoba nie zastąpiła przypadkiem pracy innej.

Zrobić jakiś timestamp w tabeli dodatkowo?

Co do tablic memory to chyba zostanę narazie na InnoDB, albo nawet zrezygnyje z transakcji i przejdę na MyISAM, bo z tego co wyczytałem przy operacjach typu select jest szybszy, a jak zaznaczyłem głównie będę wyświetlał dane, edycja będzie sporadyczna (IMG:style_emoticons/default/smile.gif)