Optimizacija upita

Optimizacija upita 19.09.2016. u 12:30 · pre 123 meseca #1

Pozdrav svima,

U bazi imam dve tabele. U jednoj se nalaze podaci o porukama (id, naslov i tekst), a u drugoj fajlovi koji su vezani za poruke kojih moze biti od 0..n (id, poruka_id, naziv, fajl), tj. imam vezu jedan na vise.

Ako zelim da prikazem poruke i sve fajlove koji su vezani za svaku poruku, kako bi to najoptimalnije uradio?

Da ne bih napravio N+1 problem, pa da kroz petlju za svaku poruku izvlacim koje fajlove ima, ja trenutno koristim 2 upita. Jedan koji izvlaci sve poruke i drugi koji koristi WHERE IN sa id-jem svake poruke i onda to na kraju spajam.

Da li postoji optimalnije resenje?

Video sam da neki koristi GROUP BY i uz to GROUP_CONCAT kako bi podatke za fajlove smestili u jedno polja, a ne u vise redova.

Sta vi koristite?

Hvala!

Potpis

Pozdrav, NenadS!

Re: Optimizacija upita 19.09.2016. u 13:26 · pre 123 meseca #2

IN je "opasna" operacija (ponekad).

Pretpostavimo da imaš upit

SELECT *
FROM NEKA_TABELA
WHERE KEY_FIELD IN (key1, key2, key3)

Ova će se naredba interno izvršiti na serveru kao

SELECT *
FROM NEKA_TABELA
WHERE KEY_FIELD = key1
OR KEY_FIELD = key2
OR KEY_FIELD = key3

E, pa tu je problem OR. Na nekim SQL endžinima ovaj OR može da prouzrukuje da se "promaši" indeks. Naročito može da bude opasno ako su tipovi podataka različiti, recimo ako je KEY_FIELD neki NUMBER (neki numerički tip), neka od vrednosti key1, key2, key3 string. Tada je FULL TABLE SCAN gotovo siguran rezultat.

Ovde je najbolji pristup da se za svaki parent rekord uradi upit nad child tabelom sa odgovarajućim ključem, i naravno dapostoji indeks nad tim ključem. Takav upit je, po pravilu, efikasan i nema veze što ćeš za N parent slogova da napraviš N+1 upita (jedan za parent i N za child rekorde).

Re: Optimizacija upita 19.09.2016. u 13:59 · pre 123 meseca #3

nauci nenade kako se radi join :)
http://dev.mysql.com/doc/refman/5.7/en/join.html

i samo ce ti se reci :D

oces sve poruke abitno dal imaju fajlove ili ne (sa njihovim fajlovima
naravno)

Code:

mysql> create table t1 (t1_id int, val char(10));
Query OK, 0 rows affected (0.25 sec)

mysql> insert into t1 values (1,'poruka1'), (2, 'poruka2'), (3, 'poruka3');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> create table t2 (t2_id int, t1_id int, val char(10));
Query OK, 0 rows affected (0.31 sec)

mysql> insert into t2 values (1, 1, 'por1 fajl1'), (2, 1, 'por1 fajl2'),
    -> (3, 1, 'por1 fajl3'), (4, 2, 'por2 fajl1'), (5, 2, 'por2 fajl2');
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1 left join t2 using (t1_id);
+-------+---------+-------+------------+
| t1_id | val     | t2_id | val        |
+-------+---------+-------+------------+
|     1 | poruka1 |     2 | por1 fajl2 |
|     1 | poruka1 |     3 | por1 fajl3 |
|     1 | poruka1 |     1 | por1 fajl1 |
|     2 | poruka2 |     4 | por2 fajl1 |
|     2 | poruka2 |     5 | por2 fajl2 |
|     3 | poruka3 |  NULL | NULL       |
+-------+---------+-------+------------+
6 rows in set (0.00 sec)

ako neces poruke koje nemaju fajlove izbaci "left"

Code:

mysql> select * from t1 join t2 using (t1_id) ;
+-------+---------+-------+------------+
| t1_id | val     | t2_id | val        |
+-------+---------+-------+------------+
|     1 | poruka1 |     1 | por1 fajl1 |
|     1 | poruka1 |     2 | por1 fajl2 |
|     1 | poruka1 |     3 | por1 fajl3 |
|     2 | poruka2 |     4 | por2 fajl1 |
|     2 | poruka2 |     5 | por2 fajl2 |
+-------+---------+-------+------------+
5 rows in set (0.00 sec)

e sad, ako si ocekivao nesto tipa

to generalno mozes da zaboravis posto "variabilan broj kolona" nije
nikako nesto sto rdbms voli niti ume ..

mozes da simuliras to sa group_concat

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

1

Re: Optimizacija upita 19.09.2016. u 16:01 · pre 123 meseca #4

Savrseno jasno Bogdane :)

Tu opciju sam u startu iskljucio jer mi duplira podatke onoliko puta koliko poruka ima fajlova ali u sustini to nije bitno jer kroz aplikaciju to svakako mogu da sredim kao sto u verziji sa dva upita spajam. Ovaj upit je definitivno lagan i brz, a za par kilobajta duplih podataka ne moram da brinem. Potreban mi je left join jer nije obavezno da svaka poruka ima fajl, a opet, potrebno je da se svaka poruka prikaze.

Ovo prvo resenje da za svaku poruku radim upit i izvlacim poruke kroz petlju je lose i ne bi trebalo da se koristi jer u scenariju za 100 poruka imao bih 101 upit.

Hvala ljudi!

Potpis

Pozdrav, NenadS!

Re: Optimizacija upita 19.09.2016. u 16:21 · pre 123 meseca #5

ti svejedno mora prodjes kroz SVE slogove koje ti vraca taj join u nekoj
petlji sa tvoje strane, sortiras po id'u poruke i citas od pocetka, kad
ti pocne poruka, dokle god je taj id dodajes fajlove, kad krene nova
poruka popunjavas vrednost za nju i tako dalje ... u php/awk i slicnim
opustenim jezicima je to ultra lako

Code:

while (row=mysql_fetch_row(...)){
if (!is_set($poruka[$row[0]]['sadrzaj'])) $poruka[$row[0]]['sadrzaj']
= $row[1];
$poruka[$row[0]]['fajlovi'][] = $row[3];
}
print_r($poruka);

i eto ga :D

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Optimizacija upita 19.09.2016. u 18:04 · pre 123 meseca #6

btw, djoka, stvari se razvijaju vremenom, tako i mysql ... tako da to za
IN ..

https://twitter.com/vojtechkurka/status/776077562606940160

:D

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Optimizacija upita 19.09.2016. u 19:00 · pre 123 meseca #7

Hehehe, eto, svakog dana se nauci po nesto novo :)

Mislim da cu za sada ipak uzeti prvu varijantu sa join-om, mada u mom slucaju posto je to relativno mala kolicina podataka, radice sta god da uradim, pa cak i upiti u while ali ja imam problem u glavi koji me sprecava da tako nesto radim sve dok ne zadovoljim svoje neobjasnjive potrebe za brzinom :D

Potpis

Pozdrav, NenadS!

Re: Optimizacija upita 19.09.2016. u 19:11 · pre 123 meseca #8

za tvoj problem je join generalno jedino pravo resenje

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Optimizacija upita 19.09.2016. u 20:42 · pre 123 meseca #9

Slazem se da je najbolje, mada sam malopre gledao neke forume pisane u php-u i oni uglavnom koriste razdvojene upite, jedan da izvuku poruke, a drugi da izvuku fajlove preko WHERE IN.

Ako budem imao vremena, probacu obe varijante ali mislim da na par stotina redova u tabeli ne moze da se primeti bilo kakva razlika.

Potpis

Pozdrav, NenadS!

Re: Optimizacija upita 19.09.2016. u 21:00 · pre 123 meseca #10

a vidi sad, zavisi sta radis .. za forum je obicno varijanta da
prikazujes jednu stranu .. znaci 10tak postova... i postovi su samo po
sebi "veliki" tako da sad ako neki post ima 10 fajlova ako ces da 10x
ponavljas content posta to jeste uzasno bacanje resursa i mnogo ti je
brze da uradis 2 upita, jedan upit da pokupis sve postove (select * from
postovi where lelemudja..) i onda drugi gde pokupis samo fajlove (select
* from fajlovi where id_posta in (select id_posta from postovi where
lelemudija)) ... 2 upita nisu strasna uopste radi to lepo .. mozes ovaj
drugi, zavisno od verzije mysql-a i strukture baze da uradis kao join,
moze da bude mnogo brze nego where in ... nesto tipa ovog joina koji sam
ti reko prvi samo ne radis select * vec samo onog sto ti treba pa ti se
nista ne ponavalja - select fajl.post_id, fajl.fajl_id, fajl.fajl_name
from post left join fajl using (post_id); tako da u rezultatu nemas
nikakve duplikate

Potpis

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Re: Optimizacija upita 19.09.2016. u 21:19 · pre 123 meseca #11

Au al' sam zaribao... ili nikada nisam ni razmisljao na pravi nacin :(

Da, to je odlicno resenje, mislim na izbegavanje where in preko join-a uz selektovanje samo neophodnih podataka, svaka cast!

Sada imam sa cim da se igram u narednom periodu :)

Potpis

Pozdrav, NenadS!

Forum kao aplikacija na telefonu

Srodne teme