Vrlo često imamo situaciju da želimo sortiranje po nekom određenom polju u tebeli ali tako da se NULL polja nikada ne pojavljuju na početku. Tipičan primer je recimo frontend koji prikazuje podatke iz neke tabele, gde se klikom na header kolone obavlja sortiranje po rastućem (asc) ili opadajućem (desc) poretku.
Obzirom da se ja u poslednje vreme dosta bavim domenima, kreirao sam jednu tabelu sa par svojih domena, čisto kao demonstraciju koncepta.
mysql> select * from domains; +----+-------------------+-------------+ | id | domain | expire_date | +----+-------------------+-------------+ | 1 | dinke.net | 2010-01-17 | | 2 | lampix.net | 2009-12-26 | | 3 | blogodak.com | 2010-09-08 | | 4 | maestrodesert.com | 2009-09-11 | | 5 | nepostojeci.com | NULL | +----+-------------------+-------------+ 5 rows in set (0.00 sec)
Dakle problem, želim sortiranje po expire_date polju ali tako da se NULL polje (recimo domen koji još nije regovan ili je istekao) uvek pojavljuje na kraju. Po defaultu NULL se javlja na početku ako sortiramo u rastućem (ASC) orderu odnosno na kraju ako sortiramo po opadajućem (desc) orderu.
mysql> select * from domains order by expire_date asc; +----+-------------------+-------------+ | id | domain | expire_date | +----+-------------------+-------------+ | 5 | nepostojeci.com | NULL | | 4 | maestrodesert.com | 2009-09-11 | | 2 | lampix.net | 2009-12-26 | | 1 | dinke.net | 2010-01-17 | | 3 | blogodak.com | 2010-09-08 | +----+-------------------+-------------+ 5 rows in set (0.00 sec) mysql> select * from domains order by expire_date desc; +----+-------------------+-------------+ | id | domain | expire_date | +----+-------------------+-------------+ | 3 | blogodak.com | 2010-09-08 | | 1 | dinke.net | 2010-01-17 | | 2 | lampix.net | 2009-12-26 | | 4 | maestrodesert.com | 2009-09-11 | | 5 | nepostojeci.com | NULL | +----+-------------------+-------------+ 5 rows in set (0.00 sec)
Problem sortiranja ćemo rešiti korišćenjem MySQL-ove IF f-je, a rešenje je:
mysql> select * from domains order by if(expire_date is null, 1, 0), expire_date asc; +----+-------------------+-------------+ | id | domain | expire_date | +----+-------------------+-------------+ | 4 | maestrodesert.com | 2009-09-11 | | 2 | lampix.net | 2009-12-26 | | 1 | dinke.net | 2010-01-17 | | 3 | blogodak.com | 2010-09-08 | | 5 | nepostojeci.com | NULL | +----+-------------------+-------------+ 5 rows in set (0.00 sec)
MySQL-ova IF f-ja slična je ternarnom operatoru, tj. vraća prvi argument ako je iskaz tačan odnosno drugi u slučaju da nije, dakle u ovom slučaju vraća 1 za null vrednosti odnosno 0 za ostale, čime dobijamo upravo prikaz koji želimo tj. NULL polje na kraju liste.
Znam da ovo može delovati pomalo konfuzno pa ću otići još jedan korak dalje i dodati još jedno polje u našoj tabeli čisto radi razjašnjenja šta se ovde tačno događa:
mysql> alter table domains add column nullorder tinyint not null; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
a zatim i update-ovati vrednosti nullorder polja tako da sadrže vrednost IF iskaza odozgo:
mysql> update domains set nullorder = if(expire_date is null, 1, 0); Query OK, 1 row affected (0.00 sec) Rows matched: 5 Changed: 1 Warnings: 0 mysql> select * from domains; +----+-------------------+-------------+-----------+ | id | domain | expire_date | nullorder | +----+-------------------+-------------+-----------+ | 1 | dinke.net | 2010-01-17 | 0 | | 2 | lampix.net | 2009-12-26 | 0 | | 3 | blogodak.com | 2010-09-08 | 0 | | 4 | maestrodesert.com | 2009-09-11 | 0 | | 5 | nepostojeci.com | NULL | 1 | +----+-------------------+-------------+-----------+ 5 rows in set (0.00 sec)
Sve u svemu naš gornji query iz rešenja problema:
select * from domains order by if(expire_date is null, 1, 0), expire_date asc;
Potpuno je isto što i ovaj query:
mysql> select * from domains order by nullorder, expire_date; +----+-------------------+-------------+-----------+ | id | domain | expire_date | nullorder | +----+-------------------+-------------+-----------+ | 4 | maestrodesert.com | 2009-09-11 | 0 | | 2 | lampix.net | 2009-12-26 | 0 | | 1 | dinke.net | 2010-01-17 | 0 | | 3 | blogodak.com | 2010-09-08 | 0 | | 5 | nepostojeci.com | NULL | 1 | +----+-------------------+-------------+-----------+ 5 rows in set (0.00 sec)
osim što naravno nullorder polje nismo morali da kreiramo.
Naravno na sličan način možemo dobiti NULL polja na početku u desc prikazu (za slučaj da je to ikome potrebno).
Mislim da je ovo dosta jednostavnija varijanta za stavljanje
redova na kraj…
SELECT * FROM domains ORDER BY ISNULL(expire_date), expire_date [ ASC | DESC ]
Samim tim nema potrebe za dodavanjem nove kolone u tabelu.
Nema potrebe za dodavanjem nove kolone ni kod “if” varijante:
select * from domains
order by if(expire_date is null, 1, 0), expire_date asc;
je sve sto ti treba.
Ono dodavanje koje sam odradio posle bilo je samo da bih demonstrirao sta se desava sa if-om (kako radi sort). Pouzdano znam da postoje ljudi koji to ne razumeju 🙂
hehe, nisam citao ceo post… 🙂 Videh samo krajnji rezultat 🙂
Svakako je isto resenje, samo sto mySql ima IFNULL funkciju, pa kontam da je verovatno bolje koristiti nju.
hmmm… rekao bih da je ovo prilično usko rešenje, tj može da se primeni samo u nekim slučajevima:
1. na maloj tabeli
2. ako se koriste uslovi (where) koji su indexirani
Ako ova dva uslova nisu zadovoljena, cela priča pada u vodu. Ako nema uslova u WHERE, hteće da koristi index u ORDER-u, ali neće moći jer imaš funkciju. Dakle upit će raditi sporo, što je, složićete se, najbitnije.
U slučaju da upit nema uslove, kao u gorenavedenom slučaju, ne koristi se nikakav index. Dakle, još jedno polje dobro dođe u ovom slučaju, i ja bih definitivno predložio to za neki veći problem. Onda je lakše forsirati index koji ti treba, u slučaju da je specifična situacija takva da hoćeš da koristi index koji se koristi u ORDER a ne u WHERE čak i ako ga imaš. MySQL nekad ume da pogreši tu, ako vraćaš puno redova, pa se desi da iskoristi index da uzme podatke (za where), ali onda sortira po file-sistemu, i zakoči ga svetski ako imaš puno redova. U _tom_ slučaju, upravo hoćeš da ga teraš da radi tablescan da uzme redove, ako ima gomilu redova za sortiranje, e _tu_ mu treba forsirati index. U ovoj priči to sve škripi.
Lepa je ovo priča teorijski, ali praktično je situacija drugačija… 🙁
@Srdjevic Ova prica je naravno isprobana u praksi na tabelama sa domenima (tipa od 200k – n miliona domena) i radi sasvim ok bez obrzira na realne probleme sa indexom koje spominjes. U slucaju da je to zaista neophodno moglo bi se praviti jos jedno polje kao sto sam i naveo u primeru (nullorder) mada bi i tu bio problem zbog lose kardinalnosti, sto ce reci cak i sa indexom ne bi bilo nekih poboljsanja.
Inace ako mislis da je ovo “usko” resenje, daj da vidimo to tvoje “siroko” 🙂
Pa poenta i jeste da nema “širokog” rešenja, usklađujes rešenje problemu. Samo htedoh da napomenem radi kompletnosti da ovo nekad i nije najsretnije rešenje. Naleteh slučajno na text na DPT-u, pa kako sam se bavio ovim problemom pre nepunih mesec dana, rekoh ajd’ da dodam.
Skoro sam uvodio baš to polje, jer je upit za export u CSV contact book-a od par miliona redi (newsletter) radio upravo to: negde obarao server, negde ga vukao satima.
Recimo, običan upit tipa:
SELECT * FROM contacts WHERE active = 1 ORDER BY ISNULL(sdate), sdate
MySQL nekad iskoristi index za active, a onda par miliona redova (sa đumlom kolona) počne da sortira… kad opališ EXPLAIN, vidiš da im koristi temp za sortiranje, pa to ubija. 🙁
Vidim da si zauzeo malo odbramben stav, a upravo si ti dao “šire” (da ga tako nazovemo) rešenje. 🙂 Ja samo htedoh dodati da je ono naizgled ružnije više skalabilno, ništa više.
Još jedan konstruktivan savet: u ovakvim člancima lepo je navesti i output EXPLAIN-a.
Svakako je dobar članak! Sada je, nadam se, još bolji. 😉
Svako dobro,
Miloš
^Pazi ovde i nije bilo price o optimizaciji vec o resenju jednostavnog problema koji ja cesto imam kod prikaza podataka iz tabela u nekom frontendu, gde jednostavno ne zelim da mi se slogovi sa null vrednostima pojavljuju na pocetku bez obzira na tip sortiranja (klik na header tabele koji sortira podatke u njoj). Sve sto si rekao stoji, resenje nije optimalno u smislu performansi i svakako da bi kod nekih velikih sajtova sa ko zna kakvim trafikom moralo da se traga za optimalnijim (u smislu brzine izvrsavanja kverija) ali iz licnog iskustva opet kazem da odlicno radi u praksi za ono za sta je namenjeno 🙂
Sto se performanski tice, evo jedne tabele sa mog servera:
mysql> select count(*) from za_domains_expiring;
+———-+
| count(*) |
+———-+
| 269913 |
+———-+
1 row in set (0.00 sec)
mysql> select domain from za_domains_expiring where available=’yes’ order by isnull(last_update), last_update desc limit 0, 100;
+—————————+
| domain |
+—————————+
…
+—————————+
100 rows in set (0.56 sec)
mysql> explain select domain from za_domains_expiring where available=’yes’ order by isnull(last_update), last_update desc limit 0, 100;
+—-+————-+———————+——+—————+———–+———+——-+——–+—————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————+——+—————+———–+———+——-+——–+—————————–+
| 1 | SIMPLE | za_domains_expiring | ref | available | available | 2 | const | 166889 | Using where; Using filesort |
+—-+————-+———————+——+—————+———–+———+——-+——–+—————————–+
1 row in set (0.00 sec)
mysql>
Dakle 0.56 sec iko koristi filesort za prikaz, ali ne znam … mozda je ovaj server zver pa to ide ovako brzo 😉
Jos jednom hvala za komentar, izvini ako je izgledalo da imam neki odbrambeni stav, jednostavno sa tvoje strane meni je zvucalo kao da nipodastavas pomenuto resenje bez da si dao predlog boljeg (sto bih iskreno i ja voleo da vidim, naucim nesto novo itd.) 🙂
Poz
Da, jasno… Kod mene je bila obrnuta situacija, te mi je upit vadio veliki broj redova… To bi kod tebe bilo nešto tipa WHERE available != ‘yes’…
Mada, nije to veliki broj redova, sigurno ti je mysql server dovoljno dobro podešen (dovoljno memorije za cache) da može da obavi to sve bez ikakvih problema… U mom “problemu” lik je pravio export od par miliona redova, sa cirka par desetina kolona…i naravno, bilo je i JOIN-a… :)))
Explain, je, naravno, rekao da radi file system sort, i to ga ubivalo… Ma bio je krš shared server, jer sam navikao već da mi dolaze svakakvi likovi sa raznoraznim kombinacijama, pa se nekad previše bavim time da radi svima/svugde kako treba… 😀
Sve u svemu, softver se preporodio dodavanjem FORCE INDEX varijante za polja za sortiranje…ali za to ne smeju funkcije. To je sve što je pisac hteo da kaže. 🙂
M
Ispravka: tek sad primetih da si imao limit tamo na stotku, a pre toga count bez uslova…. tako je to u 5am… 😀
Sve u svemu, ono WHERE available != ‘yes’ ne stoji, već koji god uslov da daje _veći_ broj redova, a mysql hoće index prema polju iz uslova zbog kardinaliteta…. pa piše po file systemu gomilu podataka da bi posle sortirao. 🙂