Witam

Mam ciekawy problem (wydajnościowy)

Muszę sprawdzać integralność danych tabeli (Czy ktoś czegoś nie usunął nie pozamieniał itd)...

Tabela ma strukturę:



Jednym z wymogów jest wykrywanie czy w tabeli czasem nie ma "dziur". Co rozumiem przez dziurę:

Jeśli mamy rekordy o id 1,2,3,4,5,6,7,8,9,... to jest wszystko ok. Ale jeśli mamy 1,2,3,5,6,7,8,9,... (brakuje 4) to jest problem - rekord 4 został usunięty.

I teraz o co chodzi: Czy ktoś zna wydajny sposób na określenie tego, że w tej tabeli brakuje właśnie rekordu numer 4. Czyli chodzi mi o dowolny skrypt/funkcje/pomysł (najlepiej w samym SQL), który zwracałby w takim konkretnym przypadku "4".

Zaznaczam że samo wykrycie dziur (że są) nie jest problemem wystarczy pobrać AUTOINCREMENT i policzyć wiersze... i już wiemy czy są dziury czy nie - ale mi chodzi o określenie ID których brakuje...

Oczywiście można pobrać wszystko i sprawdzać po kolei... ale ta tabela będzie spora a sprawdzanie ma się odbywać często...

Bardzo proszę o pomysły i pomoc

[SQL] pobierz, plaintext 
SET @zm=0;
SELECT idzm, roz FROM (
SELECT ID-@zm AS roz, (@zm+1) AS idzm, @zm:=ID FROM TABELA ORDER BY ID ASC
) podsel WHERE podsel.roz > 1
[SQL] pobierz, plaintext 

Super to jest coś - to jest pomysł

A jak z wydajnością tego przy paru mln rekordów?

Wiem że to sama sobie baza zrobi i raczej nie powinno to zbyt długo trwać Ale lepiej zapytać

No cóż.... baza musi te wszystkie miliony rekordów przelecieć, nie ma bata. Ile to zajmie? Nie wiem, wygeneruj milion rekordów i sprawdź. Sam chętnie się przekonam

czekaj... własnie sprawdziłem to na swojej tabeli. Mam tam ponad 500tys rekordów. Czas trwania: 0.333 sekundy

OK dam znać - ale będę mógł dopiero jutro Dzięki za pomoc - w sumie jak się da to walnąć nawet bez procedury to będzie kozak

Jak pisałem dla pół miliona trwało to bardzo krótko.
Procedura również tu przecież nie jest potrzebna. Bo i po co? Możesz to spokojnie na zwykłym zapytaniu wykonać

Hmm też się pokusiłem o to by sprawdzić, co prawda na mniejszej liczbie i znalazłem pewną przeszkodę

w tabeli miałem:

2
3
4
5
6
7
8
9
10
13
14
15
....

Czyli bez 1,11,12

SELECT zwrócił:

idzm roz
1 2
11 3

Czyli nie znalazł 12

Ale w sumie możną ją wydedukować i obliczyć już w PHP na podstawie:

11 - różnica to 3 zatem kolejny rekord to 11+3=14 (należy jeszcze odjąć 2 bo zaczynaliśmy od SET @zm = 0;)

Zatem z każdego zwróconego rekordu bierzemy zakres (od idzm do idzm+(roz-2))

Co da:

idzm roz id
1 2 1
11 3 11,12

I to mi wystarczy w zupełności

Dzięki!!
Jutro napisze jak śmiga na bazie 5mln+

No cóż... wierzyłem w ciebie i wiedziałem, że z tym "niesamowitym problemem" poradzisz już sobie sam

Hej,

Ja miałem podobny problem ale w inny sposób go rozwiązałem choć moje rozwiązanie raczej nie nada się przy 5mln wpisów ale przy małych seriach się sprawdza:

1. Sprawdzamy maksymalną wartość id jaka jest w tabeli w której są luki.
2. Tworzymy sobie tymczasową tabelę zawierającą id
3. Wypełniamy tabelę serią od 1 do max (przy 5mln to by trochę mogło zająć - u mnie pętla robiąca 100 000 insertów wykonuje się w 14s na słabym serwerze)
4. Stosujemy takie zapytanie:

[SQL] pobierz, plaintext 
SELECT ID FROM seria WHERE id NOT IN (SELECT TABELA.ID FROM TABELA);
[SQL] pobierz, plaintext 

kiciafu jak sam słusznie zauważyłeś, Twoje rozwiązanie nie nadaje się dla dużych danych.
Również stosowanie go do małych danych jest mało eleganckie. Bo i po co tworzyć dane tymczasowe, skoro nie trzeba

@Sephirus i jak testy?

Na wstępie przepraszam, że dopiero teraz piszę... ale naprawdę mam "jumanji" w pracy i wyleciało mi w końcu z głowy

Co do twojej metody nospor...

1. Sprawdziliśmy ją porównawczo z innymi metodami znalezionymi gdzieś w sieci... rezultaty... W każdej próbie na różnych silnikach DB... twoja bije resztę będąc conajmniej 2x szybsza

2. Testy robiliśmy potem na dwóch silnikach MyISAM i InnoDB (z różnymi konfiguracjami indeksów, pól i samych dziur):
- Na MyISAM po odpowiednich zabiegach konfiguracyjnych (by można było zrobić tak by sobie ta metoda działała "w tle") i obciążeniu metoda radziła sobie świetnie

> 100mln rekordów (sporo pól i indeksów) - ~ 1,5 minuty

- Na InnoDB (te same warunki) było nieco gorzej ale i tak wyśmienicie:

> 100mln rekordów - ~ 4 minuty

Tak więc reasumując

Wielkie dzięki - o to chodziło
Kubuś rządzi! ;P

100mln rekordów 1,5 minuty - nieźle
InnoDB zawsze działa wolniej od MyISAM. Swoją drogą zawsze mnie to dziwiło czemu - skoro przelecenie po rekordach powinno mnie więcej tak samo szybkie niezależnie czy MyIsam czy InnoDB. Coś w tym InnoDB musieli skaszanić

Mógłbyś pokazać inne rozwiązania, które znaleźliście? Ciekawi mnie, jak inaczej można było to załatwić.

Jedno się ostało jeszcze bo reszta poszła do śmieci od razu

To robi po prostu joina na tej samej tablicy i zwraca rekordy gdzie jest NULL w drugiej ;P sprytne ale wolniejsze ok 2x...

[SQL] pobierz, plaintext 
SELECT a.id + 1 AS lost_id FROM tabela a
LEFT JOIN tabela b ON a.id + 1 =b.id
WHERE b.id IS NULL
ORDER BY a.id
[SQL] pobierz, plaintext 

A co do InnoDB to samo w sobie jest wolniejsze jakby porównać z MyISAM dla jednego procesu - zapis jest dużo wolniejszy itd... Wydaje mi się, że to wszystko się wiąże z indeksami nawet jeśli lecimy po PK... :/ Ale cóż tak już jest. My ogólnie wybraliśmy MyISAM jednak z pewnymi usprawnieniami i założeniami

Faktycznie sprytne No ale czemu wolniejsze to raczej logiczne.

No to z oczywistych względów. Ale właśnie zastanawia mnie prosty odczyt. No teoretycznie nie powinno być różnicy a jednak jest. No nic, trza nam z tym żyć