Czy te zapytanie jest "optymalne" ? Opcje

[scanner]

Witam. To moj pierwszy post na forum. W sumie mógłbym go nawet nie pisać, ale lepiej nie mieć zerowego konta (podobno zerówki będą kasowane ) a pozatym, wolę się upewnić w moich wątpliwościach.
W czym rzecz:
Tworzę stronę dla pewnej ligi piłkarskiej (http://www.alpn.prv.pl - jakby ktoś był ciekawy). I przyszło do stworzenia tabeli fer-play. Czyli zliczyc zolte i czerwone kartki kazdego zawodnika, pogrupowac ich wedlug druzyn, i obliczyc punkty dla kazdej druzyny.

Mam dwie tabele, jak stoi ponizej.

Kod

CREATE TABLE alpn_druzyny (

  id_teamu int(10) unsigned NOT NULL auto_increment,

  nazwa varchar(255) NOT NULL default '',

  rozegrane int(11) NOT NULL default '0',

  liga set('I','II') NOT NULL default '',

  bramki_strzelone int(10) unsigned NOT NULL default '0',

  bramki_stracone int(10) unsigned NOT NULL default '0',

  wygrane int(11) NOT NULL default '0',

  remisy int(11) NOT NULL default '0',

  przegrane int(11) NOT NULL default '0',

  punkty int(10) unsigned NOT NULL default '0',

  PRIMARY KEY  (id_teamu)

) TYPE=MyISAM;



CREATE TABLE alpn_zawodnicy (

  id_zawodnika int(10) unsigned NOT NULL auto_increment,

  id_teamu int(10) unsigned NOT NULL default '0',

  nazwisko varchar(255) NOT NULL default '',

  pozycja set('Bramkarz','Obrońca','Pomocnik','Napastnik') NOT NULL default '',

  bramki int(11) NOT NULL default '0',

  bramki_samobojcze int(11) NOT NULL default '0',

  zolte int(10) unsigned NOT NULL default '0',

  czerwone int(10) unsigned NOT NULL default '0',

  pauzuje_od int(11) NOT NULL default '0',

  pauzuje_do int(11) NOT NULL default '0',

  PRIMARY KEY  (id_zawodnika)

) TYPE=MyISAM;

Po dluższym myśleniu, wykombinowałem coś takiego:

Kod

SELECT SUM(alpn_zawodnicy.zolte) AS k_zolte, SUM(alpn_zawodnicy.czerwone) AS k_czerwone, (SUM(alpn_zawodnicy.zolte)*3+SUM(alpn_zawodnicy.czerwone)*6) AS punkty, alpn_druzyny.liga, alpn_druzyny.nazwa

FROM alpn_zawodnicy, alpn_druzyny

WHERE liga='I' AND alpn_zawodnicy.id_teamu=alpn_druzyny.id_teamu

GROUP BY alpn_zawodnicy.id_teamu

ORDER BY punkty DESC

I teraz tak. wyniki wygladaja bardzo dobrze, aż sam się dziwię, ale mam pytanie - czy te zapytanie jest OK, czy mozna je w jakis sposob zoptymalizowac? Bo napisac można wszystko, ale nie wszystko musi działąć optymalnie..

I o to własnie pytam - czy to jest optymalne?

A pytam Was dlatego, że strona ALPN to jest pierwszy projekt, w którym stosuję aż tak (lame...) skomplikowane zapytania. I szczerze powiedziawszy jesli sie okaże że te bazgroły powyżej są ok. bedę mogł przepisać kilka innych selectów, które robiłem "aby były"

[dragossani]

Zapytanie wygląda rozsądnie.

W sprawie optymalizacji:

Widzę, że używasz MySql-a. Warto odpalić sobie EXPLAIN zapytanie; żeby zobaczyć jak silnik bazy danych używa przy tym zapytaniu indeksów. Odpowiednio zindeksowane tabele to podstawa. Więcej o optymalizacji i funkcji EXPLAIN możesz poczytać TUTAJ.

Jeśli używałbyś np. Postgresa to mógłbyś zbudować sobie widok (View) z tą statystyką. Byłoby to bardzo rozsądne posunięcie, tak dla Ciebie jak i dla wydajności samej bazy. Wydawałbyś tylko zapytanie SELECT * FROM fairplay; i po sprawie.

[scanner]

Dzieki za odpowiedz.

Co do Postgresa.. hmm.. to by oznaczalo wycieczke do bytomia, przywiazanie admina do krzesla i kopniecie go w... zeby cos doinstalowal, hih... ale pomysle

A moze by tak zapytac jakie sa ronice miedzy PG a MySQL'em? Nie.. nie zapytac Poszukam sobie.

Co do EXPLAIN hmm... no tabelka mi sie pokazala ladna (nie wklejam, bo nie wiem czy mam sens) - z moim wyuczonym na mtv angielskim wykumalem, ze chyba jest OK Ale jescze o tym sobie poczytam do poduszki.

Arrggghh.. jescze cos.. nie potrafie namierzyc (google mam zaparowane, czy co?) polskojezycznej wersjo manuala.. jest w ogole cos takiego? Wystarczy mi zwykle "tak"

[dragossani]

Jedyna znana mi próba tłumaczenia manuala do mysql-a znajduje się TUTAJ. Tłumaczenie nie jest kompletne - obejmuje niektóre rozdziały. Niestety tego o optymalizacji nie obejmuje. :wink:

Tabelka którą wyświetla EXPLAIN mówi ci wszystko o sposobie w jaki baza radzi sobie z twoim zapytaniem. W manualu są wyjaśnienia co oznaczają dane informacje w EXPLAIN i jak korzystać z nich w celu poprawienia wydajności. Jak chcesz to wklej tabelkę, którą wywalił ci EXPLAIN (tylko obejmij CODE'm żeby się nie rozjechała) - wyjaśnię ci co idzie z niej wyczytać.

Różnic między MySql-em, a Postgresem jest sporo. Generalnie MySql jest szybszy (chociaż nie jest to takie oczywiste, w praktyce wygląda to różnie - poczytaj TUTAJ), a Postgres ma większe możliwości (co do tego raczej nie ma wątpliwości - zerknij TUTAJ). Z moich doświadczeń wynika, że MySql jest dobry na początek - z czasem zaczynasz dostrzegać braki i wtedy zaczynasz rozglądać się za Postgresem.

[scanner]

Primo, to dzieki za pomoc
O webdeveloperze jakos zapomnialem... trzeba sie bedzie tam znowu porozgladac..
Oj w manualu to sa wyjasnienia.. ale jak juz mowilem.. moj angielski to jakos taki "kali podejsc, kali poczytac, kali probowac zrozumiec"
Poczytam te linki.. w koncu dawno mojego admina na oczy nie widzialem, hihi...
Co do szybkosci.. hmm.. Czytalem kilka watkow tutaj i juz sie zorientowalem ze MySQL jest szybszy, ale chcac zrobic cos bardziej skomplikowanego robie sie to dluzej... Sa drogi szybki i sa wygodne...
Tabelka - zzamieiam sie w sluch i otwieram zeszycik coby notowac ) miejmy nadzieje, ze sie nie rozjedzie...

Kod

+----------------+--------+---------------+---------+---------+-------------------------+------+---------------------------------+

| table          | type   | possible_keys | key     | key_len | ref                     | rows | Extra                           |

+----------------+--------+---------------+---------+---------+-------------------------+------+---------------------------------+

| alpn_zawodnicy | ALL    | NULL          | NULL    |    NULL | NULL                    |  535 | Using temporary; Using filesort |

| alpn_druzyny   | eq_ref | PRIMARY       | PRIMARY |       4 | alpn_zawodnicy.id_teamu |    1 | where used                      |

+----------------+--------+---------------+---------+---------+-------------------------+------+---------------------------------+

p.s. a jednak sie rozjechala.. troszku za szeroka.. :cry:
p.s.2 na webdeveloperze wlasnie czytam o explain po polskiemu dzieki za linka. Ale o wyjasnienie lopatologiczne tez poprosze

[dragossani]

Wyjaśnienie do tabelki EXPLAIN:
Każdy rekord w tej tabeli to tabela, którą MySQL musi przejrzeć wykonując twoje zapytanie. Im jest ich mniej tym lepiej.
table - Nazwa tabeli
type - Rodzaj złączenia tabel. W twoim przypadku jest eq_ref - to dobrze, ponieważ jest to najwydajniejsze złączenie typu jeden-do-wielu; oraz ALL - to niedobrze, bo MySQL leci po wszystkich wierszach tabeli. Warto ograniczyć zakres jakimś indeksem. W twoim przypadku mógłby dość indeks następujący:

Kod

CREATE INDEX id_teamu ON alpn_zawodnicy(id_teamu);

. To powinno ograniczyć egzaminowanie tabeli zawodników do teamu z którego są, czyli ALL powinno zmienić się na ref.
possible_keys - Indeksy które MySQL widzi i bierze pod uwagę kiedy zabiera się za sortowanie.
key - Indeksy których MySQL użył. Jeśli wyraźnie widać, że nie zorientował się prawidłowo i wiemy lepiej którego indeksu należało użyć, możemy go do tego zmuszać (vide myisamchk --analyze oraz USE INDEX/IGNORE INDEX) - nieco wyższa szkoła jazdy.
key_len - Długość klucza. Krótkie klucze są szybsze. Jeśli klucz jest wielokolumnowy MySQL przycina go jeśli może.
ref - Jaki klucz obcy (lub stałą) użyto do wyboru rekordów w tej tabeli.
rows - Ilość rekordów, które MySQL ostatecznie musi przejrzeć. Im mniej tym lepiej. Można przyjąć, że jeśli mamy kilka rekordów w wyniku EXPLAIN to kolejne pola rows można przez siebie pomnożyć by określić złożoność zapytania.
extra - Dodatkowe informacje o użytych mechanizmach. U ciebie jest: Using temporary - użycie tymczasowej tabeli. Negatywnie wpływa na wydajność. Najczęstszym powodem pojawienia się tego mechanizmu jest sortowanie wg. innego pola niż następuje agregacja. U ciebie tak niestety jest i nie widzę za bardzo możliwości obejścia tego. Using filesort - dodatkowy przebieg wymagany przy sortowaniu. Wynika z tego, że sortujesz po dynamicznie tworzonym polu. Negatywnie wpływa na wydajność. Where used - przycięcie zakresu klauzulą where. Pozytywny objaw.

Możesz spróbować użyć konstrukcji z LEFT JOIN'em i porównać wyniki.

Kod

SELECT SUM(alpn_zawodnicy.zolte) AS k_zolte, SUM(alpn_zawodnicy.czerwone) AS k_czerwone, (SUM(alpn_zawodnicy.zolte) * 3 + SUM(alpn_zawodnicy.czerwone) * 6) AS punkty, alpn_druzyny.liga, alpn_druzyny.nazwa

FROM alpn_druzyny LEFT JOIN alpn_zawodnicy USING(id_teamu)

WHERE liga='I'

GROUP BY alpn_druzyny.id_teamu

ORDER BY punkty DESC;

[scanner]

Wow... chyba powoli sie wspinam na kolejny poziom wiedzy
W domu sprawdze jak wyglada sprawa po CREATE INDEX
Using temporary wystpepuje poniewaz sortuję po polu 'punkty' ktore jest własnie dynamicznie liczone (x*3+y*2=punkty)
pierwsza wskazowka co do struktury bazy - po prostu tabele FerPlay bede tworzyl w trakcie dodawania kolejnych spotkan do bazy zeby ominac tworzenie dynamiczne. To bedzie najlepsze wyjscie chyba

Możesz spróbować użyć konstrukcji z LEFT JOIN'em i porównać wyniki.

No tu to obydwa wiersze wyniku mialy "ALL" doszlo w Extra "using filesort" a do tego liczba wierszy do przetworzenia wzrosla z 535*1 do 535*24 ale moze to dlatego ze jescze tego indexu nie ustawilem sprawdze jescze pozniej.
No coz.. to by bylo wszystko w tym watku.. wielkie dzieki za pomoc Znowu sie czegos nauczylem.

[dragossani]

tabele FerPlay bede tworzyl w trakcie dodawania kolejnych spotkan do bazy zeby ominac tworzenie dynamiczne. To bedzie najlepsze wyjscie chyba

To jest właśnie to, co widoki robią za Ciebie w Postgresie.