Brisanje većeg broja slogova iz MySQL baze

[Dragi Tata]

Recimo da imam tabelu i da korisnik može da vrši neke prilično složene (i skupe) pretrage nad tom tabelom, a ako mu padne napamet i da izbriše sve rezultate neke od tih pretraga.

U prvom prototipu, jednostavno pamtim id-ove svih slogova koji su pronađeni pretragom i onda ako korisnik tako odluči u jednoj petlji brišem slogove po id-u. Ovo naravno radi, ali gledam da to uradim malo efikasnije/elegantnije. Koliko kapiram, MySQL podržava IN sintaksu, pa bih mogao nešto kao:

DELETE FROM tabela WHERE id IN (1, 5, 8...)

Koliko vidim, jedino ograničenje za IN je max_allowed_packet, ali ne znam kako bi ovo uticalo na performanse.

Da li bi "prepared statement" možda bilo bolje rešenje?

Mišljenja, ideje?

Hvala unapred.

[Petar Marić]

Koliko znam ne postoji brži način, osim ako ne brišeš celu tabelu (tada je AFAIK TRUNCATE brži).

Mislim da te max_allowed_packet promenljiva (podrazumevano je 16MB) ne treba da brine, osim ako nemaš nekoliko miliona id-ova u IN klauzuli.

[ivanhoe]

koliko ja znam IN koristi indexe... koristim ga vrlo cesto upravo za te "delete all checked" operacije i nisam nikad imao nekih problema, niti problema sa perfomansama...cak mislim da je to brze od petlje koja brise jedan po jedan rekord cak i kad je sql izraz prepared (sto do nedavno nije moglo iz php-a, pa sam zato i navikao na IN), jer se ipak sve odvija interno u mysql-u...

u svakom slucaju sa indexiranom tabelom IN radi vrlo lepo..

[jablan]

A dodatna tabela (idpretrage, id) ti ne odgovara?

[Dragi Tata]

@Petar, ivanhoe:

Hvala, to mi savetuje i DBA.

A dodatna tabela (idpretrage, id) ti ne odgovara?
Misliš da rezultate pretrage čuvam u posebnoj tabeli, pa ako korisnik reši da briše da udarim Delete po subqueriju iz te tabele?

Interesantan pristup, samo je problem što obično ima mnogo pretraga a malo brisanja. Dokle da držim te pretrage u bazi i kad da ih brišem? Mislim da temp tabela ne dolazi u obzir jer korisnik vrši pretragu tokom jedne (http) konekcije a briše u drugoj.

[jablan]

Misliš da rezultate pretrage čuvam u posebnoj tabeli, pa ako korisnik reši da briše da udarim Delete po subqueriju iz te tabele?
Da, da. Mislim, ne tvrdim da je to rešenje bolje, samo ga navodim kao jedno od mogućih.

Što se brisanja tiče, sve zavisi od prirode aplikacije, količine podataka, učestalosti brisanja i punjenja itd. Jedno od rešenja je da se koristi timestamp i da se brišu sve pretrage starije od nekog određenog intervala.

[Dragi Tata]

Kao što rekoh u mom slučaju ima mnogo više pretraga nego brisanja, pa se bojim da tako nešto ne bi bilo uputno, ali generalno gledano nije loša ideja.

Thx.

[dinke]

Mislim da je ovo prvo resenje optimalnije po nacinu upotreba indexa od ovog sa subquerijem. Koliko se secam literature (nisam preterano koristio subquerije u praksi), do MySQL-a 5.1 optimizacija je bolje funkcionisala kada se ne koriste podupiti.

Ukratko:

delete from foo where id in (1,2,3)

je optimalnije nego

delete from foo where id in (select id from temp_table).

Koga ne mrzi moze i da proba sa malo vecim brojem recorda i odgovarajucim explainom :)

[degojs]

^Jeste, ali šalješ mnogo više podatka preko "žice" do baze ako je IN lista duga.. Petlja je najlošije rešenje, jer za svaki ID moraš ponovo da se konektuješ na bazu - connection pooling u takvom slučaju je verovatno obavezan, ako su performanse bitne.

Jedno od rešenja jeste da koristiš upit:

DELETE FROM t1 WHERE ID IN ( .. originalni upit [izmenjen da selektuje samo ID].. ).

Čime si izbegao da šalješ gomilu ID-ova u IN klauzi, ne treba ni pomoćna tabela, itd. Ovako može jer nema potrebe da navodiš "novu" listu ID-ova u IN klauzi, pošto, čini mi se, reče da brišeš sve zapise koji su prethodno bili vraćeni nekim upitom - samo ponovi taj isti, malo izmenjen, SELECT u podupitu za IN. Ako još možeš da taj upit izraziš sa prepared statement-om, ne izgleda mi loše rešenje, posebno što bi baza kod brisanja već mogla da ima keširane potrebne ID-ove nakon prvog upita.

Treba probati :)

[ivanhoe]

mozda neka pametnija baza, mysql ce taj originalni upit da ponovi jer query nije identican (mora do poslednjeg karaktera da bude isti da bi mysql koristio cache)

[misk0]

Ima li sanse da zapamtis WHERE uslove koji su kreirali taj set koji se vidi na ekranu? To bi bilo najdjelotvornije, ali mozda to ne mozes dobiti.

[degojs]

^Pa nije li to isto kao ono što sam ja predložio kao moguće rešenje?

[zextra]

@misk0: pitanje je koliko je pomenuti query za dobijanje tog seta podataka zaista skup. Ako nije baš previše skup (ili ako je query keširan), onda je degojs-ovo rešenje najčistije.

A šta ako je situacija sledeća: jedan korisnik uradi search, i odluči da obriše ceo set podataka koji prolaze određeni filter. Drugi korisnik u međuvremenu doda novi zapis koji takođe prolazi pomenuti filter, a prvi korisnik zatim obriše sve zapise koji prolaze isti filter.

[misk0]

@degojs: da, samo nisam isprva dobro shvatio tvoj post.

@zextra: da... interesantna situacija, medjutim ne shvatam jos dobro potrebu za brisanjem 'odredjenog seta' podataka u smislu - brisi SVE. Mislim, shvatam ali ne dajem mu neku 'veliku vaznost' jer obicno se vazni podaci brishu 'jedan po jedan' a ne 'u skupu'.
Sad, ako skup cine 3 recorda - OK, onda je IN klauzula najbolje rjesenje, ali ako ih je 300 onda nisam siguran da jedan covjek moze biti mnogo siguran u odluku i da kaze 'oki, obishi ih sad svih 300' a da opet u tom momentu se moze desiti da neki drugi korisnik doda bas record koji upada u taj uslov.
Ne znam da li postoji izraz 'cijena podataka' ili 'vaznost' a to je ono sto sam pokusao objasniti.

[Dragi Tata]

Degojsovo rešenje je sjajno ako je MySQL zaista u stanju da kešira rezultat, ali ne pije vodu u mom slučaju. U nekim slučajevima pretrage koristimo naš indeks koji nije deo MySQL baze i tad jednostavno ne postoji nikakav SQL upit, već samo dobijemo niz id-a.