Jedan upit na tri razlicita nacina. Koji ce se najbrze izvrsiti?

[ kosta90s @ 14.11.2014. 20:10 ] @

Imam dve tabele, firma i osoba. Potrebno je da ispisem naziv firmi i koliko je zaposleno u svakoj firmi.
Ja sam napisao taj upit na tri nacina, pa me interesuje koji je od njih najbolji, tj koji ce se najbrze izvrsiti ukoliko npr imam 20000 zapisa u tabeli osoba i jedno 500 zapisa u tabeli firma.
Ovo pitam iz razloga sto sam isao na razgovor za posao i to mi je pitanje bilo postavljeno. U tom trenutku mi je palo na pamet prvi nacin, za koji i sam znam da nije najbolji jer ce praviti problem u performansama sa tabelama koje imaju puno zapisa, dok su mi ostala resenja pala na pamet nakon razgovora. Valjda me uhvatila trema, sta znam :)

Da li je drugi ili treci najbolji nacin? Nekako mi drugi nacin deluje kao najefikasniji. Da li sam u pravu?
Da li mozda postoji efikasnije resenje od ova tri?

Ovo su tabele:

Code:

mysql> select * from osoba;
+----+--------+-------+
| id | Ime    | Firma |
+----+--------+-------+
|  1 | Pera   |     1 |
|  2 | Mika   |     1 |
|  3 | Laza   |     2 |
|  4 | Dragan |     1 |
|  6 | Darko  |     4 |
+----+--------+-------+
5 rows in set (0.00 sec)
mysql> select * from firma;
+----+---------+
| Id | Naziv   |
+----+---------+
|  1 | Firma 1 |
|  2 | Firma 2 |
|  3 | Firma 3 |
+----+---------+
3 rows in set (0.00 sec)

Prvi nacin:

Code:

SELECT naziv, (SELECT COUNT(*) FROM osoba WHERE osoba.firma = firma.id) FROM firma

Drugi nacin:

Code:

SELECT naziv, SUM(IF(osoba.firma = firma.id,1,0)) FROM osoba, firma GROUP BY naziv

Treci nacin:

Code:

SELECT naziv, COUNT(ime) FROM firma
LEFT JOIN osoba ON osoba.firma = firma.id
GROUP BY naziv

P.S. Nisam primljen i ono sto zelim je da sledeci put zablistam u punom sjaju :)

[ dusans @ 14.11.2014. 20:33 ] @

Prvi način radi korelisani upit, tako da za svaku od firmi radi poseban upit koji broji osobe, i to je verovatno najsporiji upit.

Drugi način pravi dekartov proizvod obe tabele (500*20000) bez ikakve potrebe,
i onda iz tog result set-a grupiše i sumira (broji) osobe po firmi.

Treći način bi morao biti najefikasniji.

Još bolje performanse bi se dobile indeksiranjem firma kolone u tabeli osoba.

EDIT:
Izmerio sam performanse u SQL Serveru - 50 000 firmi i 2 000 000 osoba:
Upiti 1 i 3 imaju iste performanse (oko 0.9 sekundi) (verovatno postoji pametna optimizacija upita 1).
Upit 2 nisam dočekao da se izvrši pa sam redukovao set na 5000 firmi i 200 000 osoba, i treba mu neverovatnih 98 sekundi.

_{[Ovu poruku je menjao dusans dana 14.11.2014. u 22:17 GMT+1]}

[ kosta90s @ 14.11.2014. 22:15 ] @

Grdno sam se prevario :) Mislio sam da su join-ovi skupe operacije. Moracu detaljno da predjem baze. Hvala na brzom odgovoru.

[ nkrgovic @ 15.11.2014. 12:27 ] @

Joinovi JESU skupe operacije, posebno LEFT JOIN. Ali, alternativa gde ti u samom SQL-u pravis interno Dekartov proizvod daje isti efekat kao JOIN. Razlika je da optimizer unutar mysql-a ZNA sta je JOIN i moze da ga optimizuje, a ne zna sta ti hoces kad to uradis rucno, pa, osim ako nije u stanju da "provali", cesto zavrsi radeci neoptimalnije....

Znaci, ideja je izbeci JOIN pravilnim dizajnom baze, dodavanjem dodatnih tabela, normalizacijom i pravljenjem sifranika koji rade kao kljucevi, a ne tako sto izbacis kljucnu rec, pa drugim upitom napravis to isto :)

Postoje situacije kad je JOIN zapravo odlicno resenje. Neka dodatna tabela veza bi mozda ovo ubrzala, ali bi pravila veliki overhead zbog dodatnih podataka i potrebe da se ti podaci konstantno osvezavaju. Ako ti taj upit treba jednom mesecno mnogo je bolje da pustis LEFT JOIN i na 100K redova jednom mesecno, nego da odrzavas slozeni sifarnik non-stop i trosis I/O na to.

[ jablan @ 15.11.2014. 17:31 ] @

^ Normalizacijom se JOIN-ovi ne eliminišu, već stvaraju. Eliminišu se denormalizacijom.

Ne stoji ni to da su JOINovi skupe operacije, barem ne tako uopšteno.

[ bogdan.kecman @ 15.11.2014. 21:42 ] @

uh momci ... nema generalizacije kod relacionih baza ... napises upit koji najbolje objasnjava sta hoces da dobijes (u ovom slucaju to je v3, v1 i v2 nikako nisu ni blizu resenja, posebno ne v2 koji je uzasan po svim mogucim kriterijumima) i ostavis rdbms-u da pametuje kako ce to da izvrsi. sta koliko kosta i na koji nacin sta treba da se izvede je nesto sto odlucuje optimizer i ti tu ne treba da se mesas - posebno ne u "opstom slucaju" / "na testu". e, sad u praksi ces nekad denormalizovati bazu, nekad ces hintovati optimizer da uradi stvar drugacije zato sto ti znas tacno sta radis etc etc ali to sve podrazumeva "znas tacno sta i zasto radis" - u "opstem slucaju" napises upit koji kaze "Sta oces" a pustis rdbms da pametuje :D ... neki ce to odraditi losije, neki bolje ..