Duży problem z dużą bazą Opcje

[budda1989]

Witam serdecznie, opis sytuacji:

posiadam tabelę z prezentacjami, oraz inną tabelę ze zdjęciami do prezentacji.
dużo prezentacji wygasło, a ich zdjęcia zaśmiecają mi tabelę zdjęć tworząc zestaw powyżej pół miliona rekordów.

Chciałbym odchudzić bazę, jednak mam duży problem. Napisałem sobie skrypt, który pobiera z tabeli prezentacje ID prezentacji których status to zero i usuwa row z tabeli zdjęcia. Jednak skrypt działa tak wolno, że musiałbym poczekać z miesiąc aż skonczy się to robić.

Zastanawiam się nad napisaniem jednego zapytania sql które:
1)z tabeli prezentacje pobiera id_prezentacje gdzie status = 0
2) z tabeli zdjecia usówa rekordy w których id_prezentacje = pobrane ID

podejmowałem próby z zapytaniami zagnieżdżonymi jednak nie udało mi się rozwiązać problemu.

przykład zapytania które testowałem:

[SQL] pobierz, plaintext 
UPDATE `zdjecia` SET STATUS=0 WHERE `id_prezentacja` = (SELECT `id_prezentacja` FROM `prezentacja` WHERE STATUS=0)
[SQL] pobierz, plaintext 

zgłasza mi błąd

- Subquery returns more than 1 row

Nie wiem naprawdę jak to napisać, i co więcej, czy przy takiej ilosci rekordów ( prezentacje = 300 000, zdjecia = 500 000 ) da się to zrobić 1 zapytaniem

Uprzejmie proszę o pomoc

PS. urządza mnie jedno z dwóch rozwiązań, update zdjęcia z nieaktywnej prezentacji i oznaczenie jego status=0 LUB usunięcie zdjęć z nieaktywnych prezentacji

Ten post edytował budda1989 28.07.2012, 20:40:20

[bpskiba]

nie ten operator

...WHERE `id_prezentacja` IN (SELECT...

[rzymek01]

zamiast IN lepiej używać EXISTS, bo IN przetrzymuje wszystkie dane odnośnie wybranych wierszy/kolumn,
a exists ustawia jedynie flagę true/false, co jest po pierwsze szybsze, a po drugie, przy dużych bazach, w ogóle daje kryterium wykonalności zapytania w rozsądnym czasie

[BaN]

Wygląda na to, że zdjęcia są przyporządkowane do więcej niż jednej prezentacji, wiec może zdarzyć się sytuacja, że dane zdjęcie jest przyporządkowane do dwóch prezentacji, w jednej status=0, w drugiej - nie. Czy na pewno wtedy zdjęcie ma mieć status=0? Jeśli nie, to zapytanie z IN będzie raczej nieprawidłowe, z EXISTS też, raczej należałoby wtedy zastosować warunek NOT EXISTS:

[SQL] pobierz, plaintext 
UPDATE zdjecia
SET `status`=0
WHERE NOT EXISTS (SELECT 1 FROM prezentacja WHERE prezentacja.id_prezentacja=zdjecia.id_prezentacja AND prezentacja.status<>0)
[SQL] pobierz, plaintext 

Kasowanie:

[SQL] pobierz, plaintext 
DELETE FROM zdjecia
WHERE NOT EXISTS (SELECT 1 FROM prezentacja WHERE prezentacja.id_prezentacja=zdjecia.id_prezentacja AND prezentacja.status<>0)
[SQL] pobierz, plaintext 

Jeżeli prezentacja.status przyjmuje tylko dwie wartości 0 i 1, to można pozmieniać te zapytania i zamiast prezentacja.status<>0 wpisać prezentacja.status=1

Co do czasu działania, to zapytania powinny działać przyzwoicie o ile na zdjecia.id_prezentacja założony jest indeks, zaś w tabeli prezentacja indeks na dwóch polach (id_prezentacja,status), ale jak jest tylko na id_prezentacja, to nie powinno to stanowić dużej różnicy

[budda1989]

zamiast IN lepiej używać EXISTS, bo IN przetrzymuje wszystkie dane odnośnie wybranych wierszy/kolumn,
a exists ustawia jedynie flagę true/false, co jest po pierwsze szybsze, a po drugie, przy dużych bazach, w ogóle daje kryterium wykonalności zapytania w rozsądnym czasie

Dziękuję za zainteresowanie tematem oraz odpowiedź, jednak podane polecenie wywala mi wszystkie rekordy z tabeli zdjęcia. Coś na pewno robię źle

[SQL] pobierz, plaintext 
SELECT `id_zdjecia` FROM `zdjecia` WHERE EXISTS (SELECT `id_prezentacja` FROM `prezentacja` WHERE `status`=0);
[SQL] pobierz, plaintext 

próbuję narazie selectem, aby zobaczyć czy robi się to co ma się robić, boję się w ciemno dawać update

BaN, dziękuję za odpowiedź
Jedno zdjęcie może być przypisane tylko do 1 prezentacji, natomiast jedna prezentacja może mieć nawet 50 zdjęć.

zastanawiam się jak powinien wyglądać select

[SQL] pobierz, plaintext 
SELECT `id_prezentacja`
FROM `prezentacja`
WHERE NOT EXISTS (SELECT 1 FROM `prezentacja` WHERE `prezentacja`.`id_prezentacja`=`zdjecia`.`id_prezentacja` AND `prezentacja`.`status`=1)
[SQL] pobierz, plaintext 

Wywala mi błąd o nieistniejącej tabeli #1054 - Unknown column 'zdjecia.id_prezentacja' in 'where clause'

prezentacja.status przyjmuje tylko 2 wart 0 i 1

na zdjecia.id_prezentacja NIE jest założony indeks
w tabeli prezentacja indeks to id_prezentacja, w tabeli zdjecia indeks to id_zdjecia, ale w tabeli zdjecia znajduje sie tez pole id_prezentacja.


skopiowałem sobie tab ze zdjęciami i na niej teraz testuje, niestety wywala ten sa błąd przy delete
:/

Ten post edytował budda1989 28.07.2012, 22:36:53

[BaN]

...
zastanawiam się jak powinien wyglądać select

z zapytania, które podałeś nie wiem co chcesz wyciągnąć, jeśli chcesz zobaczyć dane o zdjęciach, które są przyporządkowane do prezentacji, które mają status 0, to zmienia się tylko początek zapytania

[SQL] pobierz, plaintext 
SELECT *
FROM zdjecia
WHERE NOT EXISTS (SELECT 1 FROM prezentacja WHERE prezentacja.id_prezentacja=zdjecia.id_prezentacja AND prezentacja.STATUS=1)
[SQL] pobierz, plaintext 

...
na zdjecia.id_prezentacja NIE jest założony indeks

W zapytaniu uaktualniającym czy kasującym może to nie mieć znaczenia, bo i tak baza musi wszystkie zdjęcia sprawdzić, ale ten indeks jest raczej wymagany jeśli chcesz wyciągnąć wszystkie zdjęcia do danej prezentacji, bo są to pewnie zapytania typu:

[SQL] pobierz, plaintext 
SELECT ...
FROM zdjecia
WHERE id_prezentacja=... /* być może jeszcze uwględniasz pole status? */
[SQL] pobierz, plaintext 

Zatem lepiej założyć indeks, bo przy takim zapytaniu baza musi sprawdzić wszystkie wiersze jeśli nie ma indeksu

[budda1989]

Witam ,dziękuję za odpowiedzi, niestety nadal cały czas "Unknown column 'zdjecia.id_prezentacja' in 'where clause'"
pomimo, iż na 100% nazwy wpisałem dobrze, próbowałem ręcznie + klikając na odpowiednią nazwę kolumny w phpmyadmin... :/

[BaN]

Jakie dokładnie zapytanie wykonujesz, że masz taki błąd? Jeżeli te które podałeś poprzednio:

[SQL] pobierz, plaintext 
SELECT `id_prezentacja`
FROM `prezentacja`
WHERE NOT EXISTS (SELECT 1 FROM `prezentacja` WHERE `prezentacja`.`id_prezentacja`=`zdjecia`.`id_prezentacja` AND `prezentacja`.`status`=1)
[SQL] pobierz, plaintext 

to logiczne, że jest błąd, bo dwa razy podałeś tabelę prezentacja, tabela zdjecia nie jest uwzględniana, więc nie może być warunku z odwołaniem do pola tej tabeli, podałem przecież przykłady, które powinny działać