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

[nospor]

Same joiny masz zrobione poprawnie.
Co do indeksów, to zrób sobie EXPLAIN i zobacz czy mysql z nich korzysta.

[luckyps]

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?

Przy tak skonstruowanych zapytaniach wyniki mogą się różnic (ale to juz zalezy od danych) - poczytaj jak jest roznica pomiedzy LEFT join a INNER join

[little_MASTER]

(IMG:http://img835.imageshack.us/img835/3771/screen1bx.jpg)
Tak wygląda tabelka z profilowania.
Czy korzysta w index'ów nie wiem :/
Prosiłbym o interpretację (IMG:style_emoticons/default/smile.gif)

Kiedy pierwszy raz wykonałem zapytanie trwało ono 0,0026s, za drugim i kolejnym razem 0,0006s.
Czy to normalne? MySQL sam cache'uje wyniki zapytań?

A i jeszcze jedno (tylko nie krzyczcie za bardzo):
W bazie mam relacje n do n tzn jedna osoba może należeć do wielu typów.
Mam tabele osoba i tabelę typ i pomiędzy nimi była tabela osoba_typ która przechowywała id_osoba i id_typ

Pominąłem to dodając pole typ które przechowuje połączone kropką typy.
Np jeżeli coś było typu 1,2 i 5 to w polu typ mam 1.2.5.
W php robię implode a przy odczycie explode.

Wszystko śmiga aż miło (IMG:style_emoticons/default/smile.gif)

Ale co jeśli będę chciał robić wybieranie po typie? Zostaje mi tylko like, a to przy dużych ilościach rekordów zabija bazę :/ (poprawcie mnie jeśli się mylę)

Czy to co zrobiłem było słuszne? Tzn czy tak się powinno robić? Muszę mieć jakąś linię obrony dla wykładowcy (IMG:style_emoticons/default/smile.gif)

Ten post edytował little_MASTER 25.10.2011, 11:52:51

[nospor]

Prosiłem o EXPLAIN a nie ok profilowanie

[little_MASTER]

Prosiłem o EXPLAIN a nie ok profilowanie

Przepraszam (IMG:style_emoticons/default/smile.gif)

Zwykłe nieporozumienie, szybka lektura na goolach i już wiem co to EXPLAIN

Dla zapytania z joinami ma się to tak:
(IMG:http://img638.imageshack.us/img638/4254/screen2ca.jpg)
a dla zapytania tylko z selectem tak:
(IMG:http://img585.imageshack.us/img585/8712/screen3ln.jpg)

[nospor]

No i już widzisz które lepsze (IMG:style_emoticons/default/smile.gif) Jedno pobiera 1 rekord, drugie az 36

Spróbuj dla drugiej opcji ten warunek wew_id='E000001' dać jako pierwszy a nie jak teraz masz ostatni

[luckyps]

Pominąłem to dodając pole typ które przechowuje połączone kropką typy.
Np jeżeli coś było typu 1,2 i 5 to w polu typ mam 1.2.5.
W php robię implode a przy odczycie explode.

Wg mnie rozwiazania w takim stylu sa calkowicie do bani (niepraktyczne).

PS: Jak Ty chciales na tej kolumnie robic zapytania z LIKE (IMG:style_emoticons/default/questionmark.gif)

[little_MASTER]

PS: Jak Ty chciales na tej kolumnie robic zapytania z LIKE (IMG:style_emoticons/default/questionmark.gif)

Tak najszybciej mi przychodzi do głowy:

[SQL] pobierz, plaintext 
SELECT * FROM `osoby` WHERE typ LIKE '%.1.%'
[SQL] pobierz, plaintext 

Celowo dałem kropkę przed i po 1 bo gdyby jej nie było wybrał by też 10, 11, itd.
Ale nie ukrywam że to chyba nie jest najlepsze rozwiązanie :/

Spróbuj dla drugiej opcji ten warunek wew_id='E000001' dać jako pierwszy a nie jak teraz masz ostatni

Jest bez zmian, dalej pobiera 36 rekordów, czyli opcja z JOIN zostaje (IMG:style_emoticons/default/smile.gif)


A czy mając tabelę pośrednią osoba_typ można jednym zapytaniem pobrać szczegóły o osobie i wszystkie jej typy?

Coś na zasadzie:
SELECT o.pole1, o.pole 2, w.nazwa, r.nazwa (a tutaj jakieś zapytanie które pobierze typy dla wskazanego obiektu i połaczy je w string jakoś) FROM osoby o LEFT JOIN wojewodztwa w ON o.wojid=w.id LEFT JOIN region r ON o.regid=r.id WHERE o.id=12;

Czy takie coś jest możliwe? Czy lepiej dwoma zapytaniami, najpierw pobrać info o osobie, a w drugim typy?

Ten post edytował little_MASTER 25.10.2011, 12:44:03

[nospor]

Co to za typy, ile ich będziesz miał, czy one są dynamiczne?

[little_MASTER]

Typy to tak jakby kategorie, tzn osoba może być lekarzem, lektorem, kierowcą itd.
Są one w tabeli typ, ma ona dwie kolumny id i nazwa.

Będą dodawane z poziomu panelu administratora, także jeśli zajdzie potrzeba to będą dodane, ich nazwy zmieniane, w ostateczności usunięte.

Po prostu w panelu chce mieć checkboxy i tam wybierać odpowiednie typy, jeden, kilka, jeśli nie będzie odpowiedniego dla osoby to w osobnym panelu go dodam do tej tablicy (IMG:style_emoticons/default/smile.gif)
A na stronie gdy mam wyświetlane informacje o osobie chciałbym żeby jako lista wyświetliły się typy.

Mam nadzieję że to tak zrozumiale opisałem (IMG:style_emoticons/default/smile.gif)

[nospor]

To musisz dodać tabelę łączącą osobę z typem:
osoba_typ
ID_OSOBA
ID_TYP

i wywalic to durne pole typ z osoby (IMG:style_emoticons/default/smile.gif)

[little_MASTER]

To musisz dodać tabelę łączącą osobę z typem:
osoba_typ
ID_OSOBA
ID_TYP

i wywalic to durne pole typ z osoby (IMG:style_emoticons/default/smile.gif)

Też tak myślałem, tzn miałem na początku tak zrobione (IMG:style_emoticons/default/smile.gif)
Całe szczęście jakaś kopia jest to przywrócę.

A da się jednym zapytaniem pobrać i informacje o osobie i typy? Ma to sens?
Czy dwoma zapytaniami?

Czyli jak mamy tabele:

Osoby
-----------------------
|id| imie|nazwisko|
|1|Tomek| xxx|
|2| Karol| xxx|

Typy
----------------
|id| nazwa|
|1| kierowca|
|2|nauczyciel|
...


Osoby_typy
---------------------
|id_osoby|id_typu|
| 1| 1|
| 2| 1|
| 2| 2|

O osobie pobieram tak jak wcześniej tzn po id.
A typy mogę pobrać tak:

[SQL] pobierz, plaintext 
SELECT nazwa FROM Typy WHERE id IN (SELECT id_typu FROM Osoby_typy WHERE id_osoby='id_osoby')
[SQL] pobierz, plaintext 

Czy jakieś rozwiązanie byłoby lepsze?
Chodzi o to że osób będzie dużo, jak i typów i żeby przy zaliczeniu mi się to nie wysypało albo nie zamuliło :/

Dużo osób to np 100tys danych testowych :/

[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)