Šta je brže?

[bluesman]

Znam da ima ljudi koji vole da benchmark-uju MySQL, ja imam jedan praktičan problem i treba da donesem odluku. Ako imamo jednu tabelu users u kojoj imam klasicna polja:

user_id
username
email
...
country

I u njoj ima 2 miliona records, od kojih treba da izvucem na primer sve kojima je country='RS' (KEY). Recimo da ima 120.000 records

Tražimo klasično: SELECT ... WHERE country='RS' ...

Međutim na 2M records ovo WHERE mi bode oči jer treba da bude što je moguće brže. Pade mi na pamet da je možda brže ako izbegnem WHERE pa da pored ove imam tabelu sa jednim poljem:

create table users_rs (
user_id INT ...
primary key (user_id)
);

U kojoj su samo tih 120.000 koji si iz RS, i onda radimo ovako:

SELECT ... FROM users_rs LEFT JOIN users USING (user_id)

Pošto u tom select-u nema WHERE i ima manje recorda, da li neko misli da je to brže ili je samo wishfull thinking?

[holodoc]

Da li polazna baza sa 2M unosa već postoji ili se sada donosi odluka o tome kako će kompletan sistem da izgleda? Rekao bih da je u pitanju dizajn od nule.

U svakom slučaju odmah na početku bih savetovao protiv toga da se koriste odvojena tabela za bukvalno isti tip podataka jer se kosi sa nekim osnovnim pravilima dobrog dizajna baza a i stvoriće sigurno problema kasnije da ne pominjem da ne vidim nekog preteranog dobitka u performansama ako treba dodati i logiku odabira koja tabela će se koristiti.

Ono što veoma liči na ovo odvajanje podataka a u stvari ima mnogo više smisla primeniti u ovom slučaju jeste particionisanje baze.

Korisni linkovi:
http://dev.mysql.com/doc/refman/5.1/...titioning.html
http://dev.mysql.com/tech-resources/...titioning.html

Poenta cele priče je da kada se upisuju podaci u bazu MySQL automatski na osnovu PARTITION BY direktiva upisuje podatke u zasebne particije pa se tako recimo kod SELECT upita automatski već na početku zna koja od particija se pretražuje. Dakle ako se definiše particionisanje po kodu zemlje i od ukupnog broja od 2M zapisa u bazi samo 120 000 zadovoljava uslov u bilo kakvom klasičnom upitu pretraživaće se isključivo particija koja zadovoljva uslov naveden pri particionisanju. Nema potrebe da objašnjavam kolika je razlika u performansama MySQL ne podržava još uvek sve oblike particionisanja ali verzija 5.1 nudi sasvim pristojan set opcija dovoljan za većinu potreba.

[dinke]

Jel se bese kaze "premature optimization is root of all evil" ili taj lik nije radio dating sajtove ?

Elem nisam skapirao da li u prvoj varijanti imas join ali ako ne onda je ona svakako brza od druge u kojoj ga imas.

[nn.nn]

A je l' kolona country indeksirana? Ako nije, možda ne bi bilo na odmet probati. Mislim, zemalja nema ne znam koliko, pa bi u tom slučaju index trebalo da dosta pomogne.

[Dejan Topalovic]

Ako ti MySQL podrzava particionisanje, onda razmisli o toj opciji, a ako ne, onda vjerujem da bi pri toj kolicini podataka index na koloni country pomogao samo u slucaju kada selektujes zemlju sa malim brojem korisnika.

U suprotnom ti ne preostaje nista drugo, nego full table scan, ali gledaj da kolonu country stavis odmah pri pocetku, a ne pri kraju...

[mb_sa]

Originalno napisao Dejan Topalovic

U suprotnom ti ne preostaje nista drugo, nego full table scan, ali gledaj da kolonu country stavis odmah pri pocetku, a ne pri kraju...

Nadam se da ne pravim veliki offtopic.

Nisam za ovo do sada čuo, pa mi je intesantno! Jeli moze neko objašnjenje?

Hvala.

[sinisabobic]

Nisam nikad koristio ovo particionisanje pa cu samo reci da ne razdvajas tabele u users_(country).

Imao sam slican primer na jednom sajtu gde otprilike postoji ~1,5M redova i podelio sam to na ~30ak tabela (sites_com, sites_uk, ...) i isprva to je bilo super resenje ali kada su poceli da mi trebaju upiti sa rezultatima iz svih ~30 tu su nastali problemi (de da radim upit sa 30 tabela istovremeno na sajtu koji je prilicno aktivan) tako da se u potpunosti slazem sa holodoc da ne pravis vise tabela za isti tip podataka

Indeksiraj za pocetak po country u users tabeli i keshiraj sta god mozes

[bluesman]

Mislim da nisam dobro objasnio.

Moja users tabela ostaje ista i tamo če ostati svih 2M records (iako objektivno očekujem i više od 5M). A ova nova tabela će biti obična lookup tabela koja treba da skoni WHERE iz uslova pretrage.

Znači služi samo za pretrage, na primer i situaciji kada treba da pronadjem sve iz RS, tražim iz te DODATNE tabele koja ima samo 1 polje, a originalnu users tabelu JOIN-ujem. Upisi u originalnoj tabeli stoje zauvek, nista se ne brise odande jedino sto za neke najčešće uslove pretrage ne tražim direktno iz nje sa WHERE nego iz ove druge pa JOIN-ujem.

[LiquidBrain]

Predlog...

napravi tabelu users koja ima integer polje country_id.
napravi tabely country u kojoj cesh da imash zemlje...

posle

SELECT * from users, country where users.country_id = country.id and country.country_name = 'RS'

[Dejan Topalovic]

Originalno napisao mb_sa

Nadam se da ne pravim veliki offtopic.

Nisam za ovo do sada čuo, pa mi je intesantno! Jeli moze neko objašnjenje?

Hvala.

Offtopic: Svaki record ima header u kojem sadrzi pointere i ostale informacije o kolonama i sadrzaju tih kolona (fix/variable length, null/not null, data type ... ), tako da se malkice dobija na I/O performansama ukoliko je selektovana kolona na pocetku recorda umjesto na kraju... Nije svejedno da li moras proci kroz 50 pointera, da bi dosao do zeljenog na 51.-om mjestu ili da moras proci 2 pointera, da bi dosao do treceg, kojim dolazis do selektovane kolone... Ovo dolazi do izrazaja pri full table scanu i kad imas velik broj redova, kao sto je u ovom primjeru 2-5 miliona... Ja sam imao slucajeva u kojima se u SQL upitu koristi tabela sa ca. 60 kolona i 30 miliona redova, a jedna od tih kolona se koristi u WHERE uslovu... E sad, kao sto je i bilo ocekivano da ce za svaku od vrijednosti te kolone biti izvrsen full table scan, tako je i bilo, ali je bilo uzasno sporo... Nakon dublje analize, shvatio sam da je to mozda zbog toga sto se ta kolona nalazi na predzadnjem mjestu, tako da se mora proci maltene citav record, da bi se doslo do trazene vrijednosti te kolone... Nakon sto sam napravio kopiju te tabele, ali uz bitnu izmjenu - tu kolonu sam stavio na drugo mjesto - brzina izvrsavanja se poboljsala za ca. 30%

Ontopic: Index na koloni country ce pomoci, ako je selectivity za zeljenu zemlju manji od 15-20%, a u suprotnom ce biti ignorisan i izvrsice se full table scan...