# Mysql: Lücken finden



## tklustig (3. Juni 2018)

Hallo,
folgendes sql-Script soll alle Lücken auspüren, die durch Löschvorgänge im PrimaryKey entstanden sind. Die maximale Anzahl an Datensätzen stimmt nämlich nicht mit dem höchsten PK-Eintrag überein. Leider haut das Script nicht hin. Weiß jemand Abhilfe?

```
SELECT COUNT(tabelle2.id)+1 as POS, tabelle1.id as POS2
FROM id_stadt as tabelle1 LEFT JOIN id_stadt as tabelle2
ON POS2>tabelle2.id
GROUP BY POS2
ORDER BY POS2 ASC;
```
Ich bekomme folgende Fehlermeldung

```
Unbekanntes Tabellenfeld 'POS2' in on clause
```


----------



## ComFreek (3. Juni 2018)

Versuche mal tabelle1.id statt POS2 in der ON Clause. Anscheinend sind Aliasse in der Projektionen in der Tat noch nicht in der ON-Clause verfügbar. (Projektionen [SELECT …] und damit einhergehend Alias-Setzung wird konzeptionell sowieso erst am Ende vor oder nach dem ORDER BY ausgeführt.)


----------



## tklustig (3. Juni 2018)

Hab ich bereits probiert. Das Script stürzt ab, soll heißen, der MySQL-Server meldet irgendwann ma' ein TimeOut..


----------



## Bratkartoffel (4. Juni 2018)

Hi,

ich vermute, dass ihm das "ON tabelle1.id > tabelle2.id" zu schaffen macht. Hier hast du enorm grosse Datenmenden, dein ON ist nicht restriktiv genug. Hier wird ihm whsl der Arbeitsspeicher ausgehen, weshalb er zu swappen anfängt und somit in den Timeout läuft.

Versuche mal mit zusätzliche Filtern oder temporären Tabellen zu arbeiten.

Rein aus Interesse: Wie viele Datensätze befinden sich in den beiden Tabellen?

Grüsse,
BK


----------



## ComFreek (4. Juni 2018)

Wenn für deinen PK sowieso schon ein Index vorliegt, der eine sortierte Ausgabe unterstützt, dann würde ich eine iterative Lösung mit einer Clientsprache probieren:

```
SELECT id FROM id_stadt ORDER BY ID
```
In der Clientsprache (JS, PHP, C++, was du eben nutzt) dann einfach iterativ einen Zähler hochzählen. Das ist eine O(N) Lösung, besser kannst du nicht werden. Insbesondere ist das SQL-Query auch geschenkt, wenn der Index existiert.

(Ich bin mir sicher, dass das Iterieren bestimmt auch irgendwie in SQL geht, aber ich vermute stark, dass du die Lösung schneller in einer anderen Sprache gebastelt bekommst.)


----------



## Yaslaw (4. Juni 2018)

Geht in die gleiche Richtung. Ich habe mal ein Script geschrieben, um die erste nicht gebrauchte ID zu ermitteln: [SQL] First not used Id

Wenn man den Ansatz von NEXT_ID nimt, kann man daraus auch das folgende ienfache Script erstellen

```
select 
	*,
	next_id - id - 1 as missing_count
from
	(
		select 
			t1.id,
			(select min(t2.id) from test t2 where t2.id > t1.id) as next_id
		from 
			test t1
) dat
where not id+1 = next_id
```
Testinhlat der Tabelle Test:

```
ID
--
1 
2 
4 
8
```
Und das Resultat der Abfrage:

```
id | next_id | missing_count
----------------------------
 2 |       4 |             1
 4 |       8 |             3
```


----------



## tklustig (4. Juni 2018)

Bratkartoffel hat gesagt.:


> Hi,
> 
> ich vermute, dass ihm das "ON tabelle1.id > tabelle2.id" zu schaffen macht. Hier hast du enorm grosse Datenmenden, dein ON ist nicht restriktiv genug. Hier wird ihm whsl der Arbeitsspeicher ausgehen, weshalb er zu swappen anfängt und somit in den Timeout läuft.
> 
> ...


3679


----------



## tklustig (4. Juni 2018)

Yaslaw hat gesagt.:


> Geht in die gleiche Richtung. Ich habe mal ein Script geschrieben, um die erste nicht gebrauchte ID zu ermitteln: [SQL] First not used Id
> 
> Wenn man den Ansatz von NEXT_ID nimt, kann man daraus auch das folgende ienfache Script erstellen
> 
> ...



Folgendes Script läuft ebenfalls in einen Timeout rein, zumindest auf meinem RaspberryPi bei einer Tabelle mit >14000 Datensätzen. Auf meinem Hauptrechner mit 8 GByte RAM hingegen läuft es durch, wenngleich es relativ lange dauert, bis er das Ergebnis liefert

```
select
    *,
    next_id - id - 1 as missing_count
from
    (
        select
            t1.id,
            (select min(t2.id) from test t2 where t2.id > t1.id) as next_id
        from
            test t1
) dat
where not id+1 = next_id
```

...
und folgendes Script liefert nur die erste Lücke, nicht alle:

```
SELECT
    MIN(newIds.newId) AS firstNotUsedId
FROM
    -- jeweils die nächst höhere ID ermitteln
    (SELECT id + 1 AS newId FROM test) AS newIds
    -- und mit der Tabelle zurückverknüpfen
    LEFT JOIN (SELECT id FROM test ) AS ids
        ON newIds.newId = ids.id
WHERE
    ids.id IS NULL;
```

Trotzdem Danke ob deiner Hilfe. Dass der Pi relativ wenig RAM hat führt halt immer wieder zu Nachteilen....
Beispielsweise wird folgender jQuery-Request ebenfalls quälend langsam aufgebaut, zumindest unter lighttpd. Unter Apache(XAMPP) läuft der Code flüssig:

```
function rotiere_pic(photo_aktuell) {
                var anzahl = $('#photos img').length;
                photo_aktuell = photo_aktuell % anzahl;

                $('#photos img').eq(photo_aktuell).fadeOut(function () {
                    $('#photos img').each(function (i) {
                        $(this).css(
                                'zIndex', ((anzahl - i) + photo_aktuell) % anzahl
                                );
                    });
                    $(this).show();
                    setTimeout(function () {
                        rotiere_pic(++photo_aktuell);
                    }, 750);
                });
            }
```
Dieser Thread kann als erfolgreich gelöst geschlossen werden


----------



## ComFreek (4. Juni 2018)

Offtopic:



tklustig hat gesagt.:


> Beispielsweise wird folgender jQuery-Request ebenfalls quälend langsam aufgebaut, zumindest unter lighttpd. Unter Apache(XAMPP) läuft der Code flüssig:


Ich sehe da keinen (HTTP-)Request. Lässt du den Pi die Seite nur ausliefern oder betrachtest du die Seite mit einem Browser auch auf dem Pi?
Wenn du Ersteres, sollte es nicht am Pi liegen, außer der Browser lädt die Bilder nicht direkt hintereinander, sondern on-demand beim ersten jeweiligen Einblenden aufgrund eines zIndex-Wechsels. Dann könntest du die Bilder im Voraus laden. (Dazu gibt es einige Möglichkeiten, einfach mal nach "prefetch images" googeln.)


----------



## tklustig (4. Juni 2018)

Ich hoste die Seite nur auf meinem Pi. Das OS(LINUX) hat *weder *eine GUI *noch *einen Browser. Die Seite wird außerhalb des lokalen Netzwerkes über einen Browser geladen. Wenn es stimmt, was du sagst, woran könnte es dann liegen?? Die "prefetch images"-Option werde ich mir ma' anschauen....


----------



## ComFreek (4. Juni 2018)

tklustig hat gesagt.:


> Wenn es stimmt, was du sagst, woran könnte es dann liegen??


Du könntest z. B. in der Entwicklerkonsole nachsehen, was genau am Laden ist und was den Seitenaufbau verlangsamt. Aber öffne am besten hierzu einen neuen Thread.


----------



## tklustig (4. Juni 2018)

Mach ich. Dennoch ein letzer Einwand hier mit der Bitte um Feedback:
Wenn ich das Script über den localhost starte, habe ich keinerlei Verzögerung. Starte ich es online, also vom Pi, dauert es ewig, bis alles geladen ist. Wenn ich den jquery-Code entferne, ist die Seite auch auf meinem Pi ruckzuck geladen. Es ist also definitiv jQuery in Verbindung mit dem Pi, das den Seitenaufbau enorm verzögert. Hier die  Url !


----------



## Bratkartoffel (5. Juni 2018)

tklustig hat gesagt.:


> Starte ich es online, also vom Pi, dauert es ewig, bis alles geladen ist. Wenn ich den jquery-Code entferne, ist die Seite auch auf meinem Pi ruckzuck geladen. Es ist also definitiv jQuery in Verbindung mit dem Pi, das den Seitenaufbau enorm verzögert.


Nein, ich denke dass es eher deine Bilder sind, welche den Seitenaufbau verzögern. Je nachdem wie gross deine Leitung ist, dauert es halt ein paar Sekunden, bis ~7 MB geladen sind.




Grüsse,
BK


----------



## ComFreek (5. Juni 2018)

tklustig hat gesagt.:


> Wenn ich das Script über den localhost starte, habe ich keinerlei Verzögerung.


Ah, dein anderer Server ist sogar lokal auf deinem PC, d. h. die Bilder werden beim Ausliefern faktisch nur in deinem RAM hin- und hergeschoben, bis sie der Browser an seiner gewünschten Stelle vorfindet.

Betreibst du den Pi auch im lokalen Netzwerk?


----------



## tklustig (5. Juni 2018)

Yuup. Apache (127.0.0.1) und lighttpd( Webserber PI) sind in denselbem lokalen Netzwerk. Rufe ich den Webserver des PIs  von meinem lokalen Netzwerk über einen Browser mit der DynDNS-Url auf gibt es keinerlei Verzögerung. Rufe ich den Webserver außerhalb des lokalen Netzwerkes mit einem Browser auf, kommt es zu der Verzögerung....


----------



## Bratkartoffel (5. Juni 2018)

tklustig hat gesagt.:


> von meinem lokalen Netzwerk über einen Browser mit der DynDNS-Url


Dein Router erkennt, dass du effektiv intern bleibst und routet das ganze gleich wieder zurück zum Raspi. Von daher gehts halt quasi direkt, ohne deinen Upload auszulasten.

Grüsse,
BK


----------



## tklustig (5. Juni 2018)

Also habe ich nur die Möglichkeit, die Datengröße der Bilder zu verringern, oder? Obwohl: Der Pi hat doch 1 GByte RAM, die Bilder nehmen insgesamt 7 MByte in Anspruch. Warum dauert das so lange? Wie groß darf die Datenmenge maximal sein, damit der Pi das flüssig lädt??


----------



## Bratkartoffel (5. Juni 2018)

Hi,

es liegt *nicht *am RPI oder RAM, sondern an deiner Leitung. Wenn du ein 6000er DSL hast, dann hast du meines Wissens nach nur um die 700 KBit/s Upload. Wenn man das umrechnet (naiv, ohne Overhead oder sonstiges) dann kommst du auf maximal ~90 kb/s. Bei deinen 7 MB an Daten würde das dann 80 Sekunden dauern.

Da bei dir das Laden etwa 30 Sekunden dauert gehe ich davon aus, dass du um die 250 KB/s Upload hast. (Also 2000 KBits Upload Leitung)

Grüsse,
BK


----------



## tklustig (5. Juni 2018)

Warum ist eigentlich die Uploadrate entscheidend? Ich transferiere doch keinerlei Daten auf den Webserver, sondern fordere sie nur an...
Zu Hause habe ich eine 32 MBit DSL Leitung, außerhalb stehen 7,2 Mbit zur Verfügung, Download, nicht Upload!


----------



## Bratkartoffel (5. Juni 2018)

Hi,

wenn du von ausserhalb Resourcen von dir daheim anforderst, dann ist das aus Sicht deines Webservers ein Upload 

Grüsse,
BK


----------



## tklustig (6. Juni 2018)

Okay. Die Lösung liegt also darin, das Bild moi_sw_at_home.jpg, das mit 3,5 MByte mit Abstand größte, zu entfernen.... Vielen Dank für Deine Erklärungen. Weiß jetzt Bescheid...


----------



## ComFreek (6. Juni 2018)

Was stellen die Bilder denn da? Du kannst sie freilich auch weiter komprimieren mit JPEG, natürlich zulasten der Bildqualität.


----------

