Kako izbjeći Using temporary; Using filesort

Imam poveći upit sa deset join-ova (je li to mnogo?) i na kraju group by koji mi "prouzrokuje" Using temporary; Using filesort
Pošto ne mogu da prikažem čitav kod i upit, evo samo taj dio zbog kojeg se javlja filesort i ekipa. Da li neko može iz ovoga da mi kratko objasni zašto MySQL ne koristi indekse.

Potpis

Beneath civilization's fragile crust, cold chaos churns...

da, mnogo je, sve preko 5 dovodi do toga da je optimizeru gotovo nemoguce da dodje do optimalnog resenja posto je broj kombinacija uzasno velik i provera koliko bi koja kombinacija kostala da bi se doslo do idealne traje uzasno puno



super ali bez
- strukture tabele
- explain-a koji radi ono sto ne zelis
tesko da ti neko realno pomogne

sta ti kaze explain za ovo sto si prikazao? ja bi rekao da koristi kljuceve za ovaj join tako da

group by ce uvek da pravi temp tabelu (dal u ramu ili na disku zavisi od dosta toga), filesort ti je verovatno zbog group by, prethodnu temp tabelu koju napravi zbog join-a sortira da bi brze radio group by .. opet napamet bez tabele i upita tesko je reci posto kao sto rekoh ovaj upit bi trebalo da radi ok

dalje, koji mysql? za ovakav upit moras da teras min 5.6 ili 5.7 posto ce optimizer na 5.0/5.1 da se udavi skroz sa tim 10way join-om

dalje, najlaksi nacin da ubrzas optimizer i dodjes do optimalnog exec plan-a je da forsiras kljuceve koji hoces da se koriste: https://dev.mysql.com/doc/refman/5.6/en/index-hints.html

Potpis

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

Evo explain-a


MySQL je 5.6.24 ali ono sto me vec smorilo jeste sto kazes da je ovo mnogo JOIN-ova.
Samo nisam siguran kako da to izbjegnem posto je baza relativno velika, i potpuno normalizovana. A ovaj upit mi treba za neku generalnu i opstu pretragu pa mi zato treba pomalo podataka sa svake strane :(

Potpis

Beneath civilization's fragile crust, cold chaos churns...

Da li bi kreiranje view-a pomoglo u ovom slucaju?
Sto se tice previse join-ova? Ili cak generalno da li bi nesto pomoglo.

Potpis

Beneath civilization's fragile crust, cold chaos churns...

jok. view je prilicno beskorisna stvar sto se tice optimizacije



ok, bar teras 5.6 ..

naravno da je mnogo joinova, kako 10 joinova nije mnogo?! razmisli samo koliko je to kombinacija za optimizer da pronadje valjan exec path





pa, stored procedura, napravis sam optimalno temp tabele sa kljucevima iz kojih izvadis podatke


sto se ovog upita tice on ti koristi article_metadata.idx_fk_city_id index.
za tabelu city imas "where `city_name` = 'beograd'" a nemas index na city_name tako da je nema isplativ kljuc tu da koristi tako da je tu where/temporary a onda posto ti treba kljuc po id na toj temp tabeli koju pravi sa where radi filesort da bi mnozio sa article_metadata ..
za pocetak dodas kljuc na city_name (btw i nije ti nesto normalizovana ako imas upit koji pita city_name, waljda taj where treba da bude where city.id = nekiid ?! ovako je normalizacija tu beskorisna)

obavezno kad dodas taj kljuc odradi explain extended i posle toga show warnings (imas dodatne podatke u warnings)

Potpis

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

btw ako imas vremena na neku dev masinu turi mysql 5.7 uvuci tu bazu i
uradi explain vidi koliko se razlikuje posto 5.7 ima dosta novih
mogucnosti u optimizer-u kada su join-ovi i group by u pitanju

Potpis

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

Jeste normalizovana :)
I sa city.id = nekiid je bila potpuno ista situacija, što me dodatno zbunilo.
Sad ću da probam ove tvoje savjete, a u međuvremenu sam ja nešto bunario po internetu i naletio sam na optimizer_search_depth. Mijenjao sam vrednosti i za vrijednost 10 dobio sam Using where; Using index tamo gdje je bio filesort :-)
E sad, upit se i nije nešto drastično ubrzao. Sa filesort upit traje 0.58s do 0.60s svaki put, a sa ovim čačkanjem izvršava se oko 0.50s.

Bonus pitanje, šta znači NULL u koloni Extra?

Potpis

Beneath civilization's fragile crust, cold chaos churns...

dje ba vidis null u extra?

elem, obrati paznju da imas 5.6!!!

upali profiling
odradi set profiling=1; select kukulele join join join join join .... ; select * from i_s.profiling .. i gledaj gde se trosi vreme ... (ili ako te mrzi da citas iz baze, samo "show profile

sta je fora, optimizer_search_depth je uzasno opasna varijabla, ako pustis, posebno za 10way join i gore, optimizer da divlja duboko moze da ti se desi da ti 99% vremena trajanja upita ode na optimizer!!!! pricao sam o tome na google day ne znam dal si slusao ..

elem, profiling ti je tu da pomogne gde se gubi vreme, dalje imas 5.6, u odnosu na pateticni explain koji ti generise klasican explain / explain extended mozes da dobijes json kod explain-a mnooogo detaljnije .. sad ovako json je ruzno citat ali uzmi workbench (da, znam da je uzasno bagovit, teraj ga na windozi posto na lindzi umire ko zvecka jbg mono na lindzi je jos uvek patetican i to je najveci izvor bagova, ali on je jedini koji za sada ume da cita taj json kako valja) i iz njega odradi explain (bese desni klik na upit pa explain) on povuce json i isparsira ga i prikaze ti grafikon koji dosta dobro moze da se razume sta se desava uzimajuci u obzir podatke koje nemas u klasicnom explain izlazu ... to je na 5.6 i 5.7 dosta jaka alatka!!!

finalno, osim eventualnog dodavanja nekog indexa, ja ti ne savetujem nikako da dizes optimizer_search_depth vec da hintujes (ili bolje forsiras) indexe tako da ti upit radi najbrze moguce .. na taj nacin ne gubis vreme u optimizeru, nemas razliku u exec plan-u zavisno od toga koliko je up-to-date table stats (a znas da na innodb ne moze stat da bude 100% up-to-date kao na myisam) i tako dalje i tako dalje, a kapiram da ti umes da kazes koji indexi treba da se forsiraju za koji deo upita .. (u najgorem slucaju probaj razne kombinacije i prati promenu explain-a i promenu brzine upita pa ce nabodes iz 10tog pokusaja :D )

ono sto obavezno treba da proveris je http://dev.mysql.com/doc/refma...s.html#sysvar_optimizer_switch ( SELECT @@optimizer_switch\G )
bitne stavke za tebe su bka, engine_condition_pushdown i materialization

Potpis

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

Napisao sam gore da je ovo samo "isjecak" od veceg explain-a :)
I NULL dobijam za dva reda...


Ovo su rezultati profilinga


Citam sad da je sending data u stvari samo izvrsavanje upita. Moze li taj segment da se razbije na dodatne korake?


Sto se tice hintovanja indeksa, tu mu kulira nacisto. Cak i sa force index. Je l' to znaci da nema sanse da ga natjeram da koristi taj indeks?
Zasto kad explain u koloni possible_keys prijavljuje PRIMARY?

Potpis

Beneath civilization's fragile crust, cold chaos churns...

update sto se tice koriscenja indeksa
sad kad promjenim where u city.id = nekiid onda koristi primarni kljuc, ali upit traje identicno dugo, plus u extra koloni i dalje stoji Using index; Using temporary; Using filesort

Potpis

Beneath civilization's fragile crust, cold chaos churns...

sending data je finalni prolaz kroz datu znaci sve temp tabele su odradjene u executing i sending data prolazi sada kroz taj finalni rezultat i radi "filtriranje" i slanje toga (kako koji red nadje posalje klijentu, zato je sending data). sta ti kaze show warnings posle explain-a ?

ako te force index kulira znaci da ne moze da ga upotrebi (zasto, nemam pojma bez tabela i upita)

jel mzo bar ceo explain da bacis ovde + show warnings, xxxx-uj imena tabela ako je frka :D

inace ono sto se meni dojmi ovako napamet je da mysql skocka tu finalnu temp tabelu brzo ali je doticna povelika i naravno bez indexa i onda prolaz kroz nju ide letnji dan do podne.. obicno resenje koje ide tu (ja ga ne volem uopste al je cesto i radi) je da ti napravis sam tu temp tabelu, dakle ti rucno create pa insert select a ta tabela ima index i ti onda finalno select iz te temp tabele i finalno drop tako da imas full kontrolu i max brzinu .. naravno odrzavanje postaje za klasu kompleksnije al jbg .. drugo resenje je naravno denormalizacije koristenjem materialized view, naravno mysql ne podrzava materialized view tako da se to pravi rucno tako sto imas normalnu tabelu umesto view i trigere u tabelama okolo koji updateuju tu tabelu on change tako da odrzavas tu tabelu up2date i na taj nacin imas sitno sporiji update/delete/insert iz tih ostalih tabela ali ti ovi spori upiti rade mega brzo jer imas denormalizovanu pripremljenu datu spremnu za citanje ... i to je isto cest nacin za resavanje, isto nije idealan al ..

Potpis

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

Moram da priznam kako sam dosta gluposti u pravljenju ovog upita napravio na koje sam sada naišao nakon stote iteracije :-)
Prvo sam krenuo da pravim upit za neki naaaajopštiji slučaj, koji se skoro i neće koristiti praktično, pa sam zato imao prilično podivljao upit koji skoro da nije imao nikakve uslove. Onda sam se uhvatio filesort i temporary iako to nije bio razlog sporog izvršavanja.
Kada sam napokon krenuo ispočetka kako treba (jedan join pa ; drugi join pa ;) lako sam došao do toga gdje se upit u stvari zaglavi.
Izbacio sam jednu tabelu koja nije bila potrebna za tu fazu prikaza podataka i izvršavanje se spustilo na < 0,20s što je odlično.
Čak sam poslije dodao jedan podupit kao join i sve radi odlično. Kida MySQL :-)
Malo sam pojednostavio sad kako sam sredio stvari, nije baš tako prosto bilo niti sam baš sve zabrljao, ali definitivno sam pogrešno pristupio ovome.

Inače, sad prvi put od tebe čujem za materialized view pa sam malo uzeo da čitam, i sviđa mi se ideja, pogotovo što bi fino poslužila u ovom mom slučaju bez obzira na poboljšanja koja su iskrsla.
Kapiram da to može i pješke da se emulira baš tako kako si ti opisao, ali ipak je native najbolja varijanta, pa me zanima ima li u planu tako nešto za buduće verzije MySQL-a?

Potpis

Beneath civilization's fragile crust, cold chaos churns...

to za upit, zeznuto je kad ne mozes da ostavis ceo upit pa se ne vidi problem znas i sam, bitno da imas ideju kako da dodjes do resenja

sto se tice materialized, trenutno ne postoji worklog za materialized views na mysql-u i koliko ja znam nase developere nece ga ni biti osim ako neki ozbiljan klijent ne pozeli tako nesto, a poznajuci nase klijente ne verujem da ce to da se desi ikad ...

ono sto imamo od materialized price je u 5.6 i 5.7 imamo materialized temporary tables ... to je posebna vrsta subquery optimizacije (dakle ne join nego subquery) gde u slucaju da moze da se detektuje da se neki temp table koji se generise u svakom prolazu kroz upit ne menja optimizer ce napraviti staticno tu tabelu samo jednom... bas sam skoro radio na nekom bagu vezano za to (nije bug klijent samo nije razumeo kako to radi): https://bugs.mysql.com/76685
dokumentacija: https://dev.mysql.com/doc/refm.../en/subquery-optimization.html

Potpis

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

bogdane, sta znaci kad u show profile; imam gomilu redova sending data i executing?

Potpis

Beneath civilization's fragile crust, cold chaos churns...