Alter Table - physisch?

Hallo,

ich möchte eine Tabelle physisch(!) sortieren, da ich denke, das die
Barbeitung schneller geht, da ich bei Select kein "order by" angeben muss.

Es existieren u.a. 2 Spaten ia (anfang) und ie (ende). Nach derren
Differenz möchte ich sorieren. Zu diesem Zweck lege ich eine Spalte
distance an, berechne Differenz von ia ud ie und sortiere danach die
tabelle mit ALTER TABLE.
Aber lasse ich sie normal ausgeben, wurde die tabelle nicht sortiert. Wo
könnte mein Denkfehler liegen?

Hier der Code ( Eiine Funktion der der Tabellenname übergeben wird.)

function SortTab ($worktab) {
print "$worktab => Lösche DF";
$res=mysql_query("ALTER TABLE $worktab DROP distance"); // evtuell
vorhandenes Distancefeld löschen
print "...Erstelle DF";
$res=mysql_query("ALTER TABLE $worktab ADD distance DOUBLE NOT NULL"); //
Distancefeld hinzufügen
print " =>".mysql_error()." Berechne (";
$res=mysql_query("update $worktab set distance=ie-ia where ie!='0'"); // in
distance die Differenz von ie und ia eintragen
$anz=mysql_affected_rows();
print "$anz ).. Sortiere";
$res=mysql_query("ALTER TABLE $worktab ORDER BY distance ASC"); // !!!
Hier physisch sortieren
print " =>".mysql_error();
$anz=mysql_affected_rows();
print "$anz sortiert... Lösche DF";
$res=mysql_query("ALTER TABLE $worktab DROP distance");
print " =>".mysql_error()."
";
}


Gruss Jens

Re: Alter Table - physisch?

Hallo Jens,

Jens Meyer wrote:
> Hallo,
>
> ich möchte eine Tabelle physisch(!) sortieren, da ich denke, das die
> Barbeitung schneller geht, da ich bei Select kein "order by" angeben muss.

Was du da tust tut richtig weh. Ein Alter Table lockt die Table und
evtl. alle darauf zugreifenden Prozesse hängen. Desweiteren erzeugst du
unnötige IOs und naja - kurz und knapp - es ist sinnbefreit, was du tust
und es tut extrem weh mit vielen Datensätzen.

Lies dir bitte das Handbuch durch - insbesondere der Abschnitt zu
Indizes und "explain query", setze vernünftige Indizes und benutze
"order by". Das ist genau dafür gedacht - zum Sortieren.

Gruß, Frank


[---snipp---]haufen hässliches SQL etc.[---snipp---]

Re: Alter Table - physisch?

Hallo Frank,

Danke für deinen Hinweis, aber das Problem bleibt :(
diese Routine wird ca. 1 mal pro Monat aufgerufen, aber die Abfragen ca.
jede sekunde.

Vieleicht habe ich mein Problem falsch dargestellt: Hier ein Beispiel
ia ie Wert
10 300 Haus
40 50 Garten
80 90 Auto


nun habe ich einen $wert z.B. 45 (welche zwisschen 40-50 liegt und
"Garten" zurück geben soll).

Ist die Tabelle physisch sortiert (nach differenzen) , kann ich einfach
schreiben
... "select * where ia >='$wert' and '$wert' und habe recht schnell das ergebnis. Der Erste Wert ist imemr das gesuchte
ergebnis.
Ist sie nicht physisch würde zuerst "Haus" kommen, da 45 auch(!) zwischen
100 und 300 liegt.
Ich müsste jede Abfrage mit einschränkungen übergeben die das ganze
verlangsamt.

oder gibt es einen anderen Lösungsansatz. und.. Wieso wird sie nicht
physisch sortiert?

Gruss Jens

Re: Alter Table - physisch?

Jens Meyer schrieb:

> Ist die Tabelle physisch sortiert (nach differenzen) , kann ich einfach
> schreiben
> .. "select * where ia >='$wert' and '$wert'
Dein Statement gibt (ohne LIMIT) nur ein Minimum am Treffern (hier
gerade 2), das wahrscheinlich sehr schnell sortiert wird. Mit korrekter
Syntax und ein klein wenig Optimierung sparst du mehr Zeit, als das
ORDER BY kostet:

$sql = "SELECT *
WHERE {$wert} BETWEEN ia AND ie
ORDER BY ia-ie
LIMIT 0,1";

> und habe recht schnell das ergebnis. Der Erste Wert ist imemr das gesuchte
> ergebnis.
> Ist sie nicht physisch würde zuerst "Haus" kommen, da 45 auch(!) zwischen
> 100 und 300 liegt.
> Ich müsste jede Abfrage mit einschränkungen übergeben die das ganze
> verlangsamt.

Ich wette, dass du selbst mit 7- oder mehrstelligen Datensatz-Anzahlen
durch deine Pseudo-Vorsortierung nicht mehr als im
Hundertstelsekundenbereich sparen würdest. Und das zu dem Preis, dass
jede noch so kleine Änderung an den Daten deine Anwendung unbrauchbar
macht. Vergiss also diesen Schwachsinn.

> oder gibt es einen anderen Lösungsansatz. und.. Wieso wird sie nicht
> physisch sortiert?

Weil die physische Sortierung ein Mythos ist, der nach dem nächsten
DELETE/INSERT oder REPLACE kaputt ist. Es gibt einfach ohne ORDER BY
keine definierte Reihenfolge.

MfG
Niels

--
| http://www.kolleg.de · Das Portal der Kollegs in Deutschland |
| http://www.bsds.de · BSDS Braczek Software- und DatenSysteme |
| Webdesign · Webhosting · E-Commerce · Mambo Content Management |
------------------------------------------------------------ ----

Re: Alter Table - physisch?

Hallo Niels,

> Ich wette, dass du selbst mit 7- oder mehrstelligen Datensatz-Anzahlen
> durch deine Pseudo-Vorsortierung nicht mehr als im
> Hundertstelsekundenbereich sparen würdest. Und das zu dem Preis, dass
> jede noch so kleine Änderung an den Daten deine Anwendung unbrauchbar
> macht. Vergiss also diesen Schwachsinn.

es sind ca.. 800.000 Datensätze. Es kommen selten welche hinzu, und wenn,
dann viele auf einmal.
Stimmt das wirklich das dies wenig ausmacht.
Ich werde deinen Befehl mal testen, leider kann man schlecht
Geschwindigkeiten messen.


> Weil die physische Sortierung ein Mythos ist, der nach dem nächsten
> DELETE/INSERT oder REPLACE kaputt ist. Es gibt einfach ohne ORDER BY
> keine definierte Reihenfolge.

hmm .. da war ich wohl auf dem Holzweg vielleicht war deshalb die Tabelle
nicht so,
wie ich es erwartete. Ich dachte durch das "vorsortieren" helfe ich MySql.
Es gibt bei dieser Tabelle kein Delete und Insert. Wie oben gesagt, sehr
selten,
und alles auf einmal.

Danke nochmal für deine Tipp.

Gruss Jens

Re: Alter Table - physisch?

Jens Meyer wrote:


> Ich dachte durch das "vorsortieren" helfe ich MySql.

Kaum. Der schnelle Weg zu den Daten führt über den Index, und wenn=20
überhaupt, dann muß man die Daten so sortieren, daß sie für den Z=
ugriff=20
über diesen optimiert sind - was alles andere als eine alphabetische=20
Sortierung ergeben wird, sondern eine gemäß dem Index-Verfahren.

Größere Backends verfügen durchaus über entsprechende Tools oder =

Kommandos - MySQL kann sowas meines Wissens nicht. Außerdem versaut=20
sowas meist die INSERT-Performance oder verlangt in ganz erheblichem=20
Umfang zusätzlichen Plattenplatz - sowas macht man für richtig groß=
e=20
statische Tabellen, und nicht für eine harmlose Million Records.

Gruß Sevo

Re: Alter Table - physisch?

Jens Meyer schrieb:
> Hallo Niels,
>
>> Ich wette, dass du selbst mit 7- oder mehrstelligen Datensatz-Anzahlen
>> durch deine Pseudo-Vorsortierung nicht mehr als im
>> Hundertstelsekundenbereich sparen würdest. Und das zu dem Preis, dass
>> jede noch so kleine Änderung an den Daten deine Anwendung unbrauchbar
>> macht. Vergiss also diesen Schwachsinn.
>
> es sind ca.. 800.000 Datensätze. Es kommen selten welche hinzu, und wenn,
> dann viele auf einmal.

Das ist für ein DBMS gar nichts.

> Stimmt das wirklich das dies wenig ausmacht.

Du wirst den Unterschied zwischen einer Abfrage mit ORDER BY und einer
ohne wegen der Kleinheit der Differenz kaum messen können. Passenden
Index natürlich vorausgesetzt.

> Ich werde deinen Befehl mal testen, leider kann man schlecht
> Geschwindigkeiten messen.

Wenn du die Abfragen über die phpMyAdmin eingibst, wird dir die Dauer
der Abfrage angezeigt. Ansonsten hilft - wie so oft - die FAQ:
25.5. Wie kann ich die Performance zweier Befehle vergleichen?
http://www.php-faq.de/q/q-code-performance.html

MfG
Niels

--
| http://www.kolleg.de · Das Portal der Kollegs in Deutschland |
| http://www.bsds.de · BSDS Braczek Software- und DatenSysteme |
| Webdesign · Webhosting · E-Commerce · Mambo Content Management |
------------------------------------------------------------ ----

Re: Alter Table - physisch?

Hallo Niels,

> Du wirst den Unterschied zwischen einer Abfrage mit ORDER BY und einer
> ohne wegen der Kleinheit der Differenz kaum messen können. Passenden
> Index natürlich vorausgesetzt.

ia und ie sind mit index.
Ich ging nur davon aus, das ein simpler Select-Befehl ohne Extras kürzer
ausgefüjrt wird,
als einer wo noch ein Order by hinterher kommt, da er ja (zumindest meiner
Annahme nach)
noch etwas mehr machen muss.also mehr rechenzeit braucht.
Das Problem ist, das innerhalb kürzester Zeit sehr viele Anfragen kommen -
aber sogut wie
nie Neueinträge.

>> Ich werde deinen Befehl mal testen, leider kann man schlecht
>> Geschwindigkeiten messen.
>
> Wenn du die Abfragen über die phpMyAdmin eingibst, wird dir die Dauer
> der Abfrage angezeigt. Ansonsten hilft - wie so oft - die FAQ:
> 25.5. Wie kann ich die Performance zweier Befehle vergleichen?
> http://www.php-faq.de/q/q-code-performance.html

Das ist mir schon klar, bloss ärgert da einem der plattencache und andere
Dinge des Betriebssystems/Programms , so das ein und derselbe Befehl sich
erheblich in der Zeit unterscheiden, sodaß
man sich auf diese Art Ergebnisse leider nicht verlassen kann.

Gruss Jens

Re: Alter Table - physisch?

Hallo Sevo,

>> Ich dachte durch das "vorsortieren" helfe ich MySql.

>Kaum. Der schnelle Weg zu den Daten führt über den Index, und wenn
>überhaupt, dann muß man die Daten so sortieren, daß sie für den Zugriff
>über diesen optimiert sind - was alles andere als eine alphabetische
>Sortierung ergeben wird, sondern eine gemäß dem Index-Verfahren.

Naütlich haben ia und ie einen index.

>Größere Backends verfügen durchaus über entsprechende Tools oder
>Kommandos - MySQL kann sowas meines Wissens nicht. Außerdem versaut
>sowas meist die INSERT-Performance oder verlangt in ganz erheblichem
>Umfang zusätzlichen Plattenplatz - sowas macht man für richtig große
>statische Tabellen, und nicht für eine harmlose Million Records.

Neueinträge kommen vielleicht 12 mal im Jahr vor, sodaß danach ein
neusortierung gemacht werden hätte können. Es ist selten genug und
Plattenplatz ist auch genug vorhanden.

Eigentlich handelt es sich um mehrere Millionen Datensäte,
die ich aber - so eine erneute Annahme von mir - in viele
kleine aufgeteilt habe, um so den zugriff noch schneller zu machen,
da sich über ie leicht feststellen lässt in welcher die Daten sein müssen.

Oder spielt es keine Rolle, wenn eine Tabelle über index verfügt, ob
800.000 oder 56 Mil Datensätze vohanden sind? - Zeitmäßig meine ich?

Gruss Jens

Re: Alter Table - physisch?

Jens Meyer schrieb:
> Hallo Niels,
>
>> Du wirst den Unterschied zwischen einer Abfrage mit ORDER BY und einer
>> ohne wegen der Kleinheit der Differenz kaum messen können. Passenden
>> Index natürlich vorausgesetzt.
>
> ia und ie sind mit index.
> Ich ging nur davon aus, das ein simpler Select-Befehl ohne Extras kürzer
> ausgefüjrt wird,
> als einer wo noch ein Order by hinterher kommt, da er ja (zumindest meiner
> Annahme nach)
> noch etwas mehr machen muss.also mehr rechenzeit braucht.
> Das Problem ist, das innerhalb kürzester Zeit sehr viele Anfragen kommen -
> aber sogut wie nie Neueinträge.

Wieviele Datensätze bleiben nach der Einschränkung mit ia überhaupt übrig? Das sind doch sicher nur ein paar Zehntausend, oder? Da
merkst du die Sortierung gar nicht.

>> Wenn du die Abfragen über die phpMyAdmin eingibst, wird dir die Dauer
>> der Abfrage angezeigt. Ansonsten hilft - wie so oft - die FAQ:
>> 25.5. Wie kann ich die Performance zweier Befehle vergleichen?
>> http://www.php-faq.de/q/q-code-performance.html
>
> Das ist mir schon klar, bloss ärgert da einem der plattencache und andere
> Dinge des Betriebssystems/Programms , so das ein und derselbe Befehl sich
> erheblich in der Zeit unterscheiden, sodaß
> man sich auf diese Art Ergebnisse leider nicht verlassen kann.

Dennoch geben die Ergebnisse einen praktischen Anhaltspunkt für die
Größenordnung, insbesondere wenn man die Tests mehrfach in derselben
Umgebung ausführt. Diese 'Störungen' hast du ja schließlich auch im
praktischen Betrieb. Wenn die längere[tm] Abfragedauer da im Rauschen
untergeht, ist das definitiv die falsche Stelle um mit Optimierungen
anzusetzen.

MfG
Niels

BTW: In deinem Newsclient ist die Zeilenlänge zu lang eingestellt.

--
| http://www.kolleg.de · Das Portal der Kollegs in Deutschland |
| http://www.bsds.de · BSDS Braczek Software- und DatenSysteme |
| Webdesign · Webhosting · E-Commerce · Mambo Content Management |
------------------------------------------------------------ ----

Re: Alter Table - physisch?

"Jens Meyer" wrote:
> Hallo Sevo,
>
>>> Ich dachte durch das "vorsortieren" helfe ich MySql.

Stirbt denn dieser Mist niemals aus? Du kannst Daten in MySQL nicht
physisch sortieren. Gerade mal mit dem MyISAM Backend kannst du etwas
ähnliches erreichen: solange du in eine anfangs leere Tabelle nur
INSERTs machst, landen die Rows in dieser Reihenfolge im Datenfile.

Jedes folgende DELETE, UPDATE oder INSERT kann die Reihenfolge aber
wieder durcheinander bringen.

Eine solche Anordnung *kann* Vorteile haben: wenn du oft größere
zusammenhängende Ranges einer Tabelle lesen willst, führt das zum
Lesen größerer zusammenhängender Blöcke im Datenfile und je nach
unterliegendem Filesystem womöglich auch zum Lesen größerer zusam-
menhängender Bereiche auf der Festplatte. Unter dem Strich sparst
du also ein paar Bewegungen des Lesekopfes deiner Festplatte.
Allerdings gibt es eben diese "wenns" und i.d.R. entschärfen die
diversen Caches in MySQL und dem Betriebssystem die Sache sowieso.

Ungeachtet dessen gibt eine bestimmte Reihenfolge der Rows im
Datenfile *keine* Garantie für die Reihenfolge der Ergebnisse
einer Query.

> Eigentlich handelt es sich um mehrere Millionen Datensäte,
> die ich aber - so eine erneute Annahme von mir - in viele
> kleine aufgeteilt habe, um so den zugriff noch schneller zu machen,

Aua. Aua! AUA!

> Oder spielt es keine Rolle, wenn eine Tabelle über index verfügt, ob
> 800.000 oder 56 Mil Datensätze vohanden sind? - Zeitmäßig meine ich?

Es spielt fast keine Rolle. Zugriffe über den Index haben loga-
rithmischen Aufwand. Nehmen wir mal an, der Index von einhundert
Datensätzen würde in einen Indexblock passen. Dann brauchst du bei
100 Rows einen Indexzugriff um eine bestimmte Row zu finden, zwei
Zugriffe bei 10.000 Rows, 3 Zugriffe bei 1.000.000 Rows, 4 Zugriffe
bei 100.000.000 Rows. Ist das schnell genug?


XL

Re: Alter Table - physisch?

Hallo Axel,

>>
>>>> Ich dachte durch das "vorsortieren" helfe ich MySql.
>
> Stirbt denn dieser Mist niemals aus? Du kannst Daten in MySQL nicht
> physisch sortieren. Gerade mal mit dem MyISAM Backend kannst du etwas
> ähnliches erreichen: solange du in eine anfangs leere Tabelle nur
> INSERTs machst, landen die Rows in dieser Reihenfolge im Datenfile.

Habe ich ja nun gelernt :-)
(auch wenns keine Inserts und der gleichen gibt, ausser
mit Erneutem sortieren danach)

>> Eigentlich handelt es sich um mehrere Millionen Datensäte,
>> die ich aber - so eine erneute Annahme von mir - in viele
>> kleine aufgeteilt habe, um so den zugriff noch schneller zu machen,
>
> Aua. Aua! AUA!
>
>> Oder spielt es keine Rolle, wenn eine Tabelle über index verfügt, ob
>> 800.000 oder 56 Mil Datensätze vohanden sind? - Zeitmäßig meine ich?
>
> Es spielt fast keine Rolle. Zugriffe über den Index haben loga-
> rithmischen Aufwand. Nehmen wir mal an, der Index von einhundert
> Datensätzen würde in einen Indexblock passen. Dann brauchst du bei
> 100 Rows einen Indexzugriff um eine bestimmte Row zu finden, zwei
> Zugriffe bei 10.000 Rows, 3 Zugriffe bei 1.000.000 Rows, 4 Zugriffe
> bei 100.000.000 Rows. Ist das schnell genug?

hmm... als ich es damals machte, kam es mir spürbar schneller vor.
Wunschdenken?
Ich werde mal ein paar Tests fahren. Würde mich natürlich freuen wenn du
Recht hast
(und es wird wohl so sein).
Ich habe da wohl versucht etwas "kaputt"-zu optimieren. Danke für deinen
Hinweis.

Gruss Jens
... der nie nie nie wieder was versucht zu optimieren wovon er keine Ahnung
hat :-)

Re: Alter Table - physisch?

>>... Gerade mal mit dem MyISAM Backend kannst du etwas
>>ähnliches erreichen: solange du in eine anfangs leere Tabelle nur
>>INSERTs machst, landen die Rows in dieser Reihenfolge im Datenfile.

Ohne jetzt über Kosten und Nutzen dieser Aktion nachzudenken:

1. INSERT der neuen Daten in die Originaltabelle
2. Leere Kopie der Originaltabelle anlegen
3. INSERT ... SELECT in Kopie aus Original (natürlich sortiert)
4. Zugriff von aussen unterbrechen
5. umbenennen: Original in Backup; Kopie in Original
6. Zugriff wiederherstellen

Wenn das bei MyISAM Tabellen tatsächlich so funktioniert - obs wirklich
was bringt weiss ich nicht.

Re: Alter Table - physisch?

Hallo Helmut,

"Helmut Schmuckermair" schrieb im Newsbeitrag
news:2e6f5$43cfb650$54710a4d$13201@news.chello.at...
>
>>>... Gerade mal mit dem MyISAM Backend kannst du etwas
>>>ähnliches erreichen: solange du in eine anfangs leere Tabelle nur
>>>INSERTs machst, landen die Rows in dieser Reihenfolge im Datenfile.
>
> Ohne jetzt über Kosten und Nutzen dieser Aktion nachzudenken:
>
> 1. INSERT der neuen Daten in die Originaltabelle
> 2. Leere Kopie der Originaltabelle anlegen
> 3. INSERT ... SELECT in Kopie aus Original (natürlich sortiert)
> 4. Zugriff von aussen unterbrechen
> 5. umbenennen: Original in Backup; Kopie in Original
> 6. Zugriff wiederherstellen
>
> Wenn das bei MyISAM Tabellen tatsächlich so funktioniert - obs wirklich
> was bringt weiss ich nicht.

So funktioniert es, und so mach(te?) ich es auch. Ich dachte nur, das MySQL
bei ALTER TABLE ähnliches macht und wunderte mich das es nicht so ging, wie
ich dachte (wollte).

Gruss Jens

Re: Alter Table - physisch?

Helmut Schmuckermair wrote:
>
>>>... Gerade mal mit dem MyISAM Backend kannst du etwas
>>>ähnliches erreichen: solange du in eine anfangs leere Tabelle nur
>>>INSERTs machst, landen die Rows in dieser Reihenfolge im Datenfile.
>
> Ohne jetzt über Kosten und Nutzen dieser Aktion nachzudenken:

Der Nutzen ist fraglich. Die Kosten sind bekannt hoch. Gerade bei
großen Tabellen, wie sie Jens wohl hat.

> 1. INSERT der neuen Daten in die Originaltabelle
> 2. Leere Kopie der Originaltabelle anlegen
> 3. INSERT ... SELECT in Kopie aus Original (natürlich sortiert)
> 4. Zugriff von aussen unterbrechen
> 5. umbenennen: Original in Backup; Kopie in Original
> 6. Zugriff wiederherstellen

3. und 4. mußt du vertauschen. Und es reicht, Schreibzugriffe
abzublocken. Ansonsten sind 3. und 5. für sich genommen atomar.

Man kann das offline machen mit myisamchk --sort-records=...
Online (ANALYZE TABLE, OPTIMIZE TABLE) geht das gar nicht.

Eventuell kann es sich auch lohnen, eine derartige Tabelle mit
myisampack zu packen.


ABER: all diese Optimierungen sind eher esoterisch. Vernünftige
Indizes bringen normalerweise deutlich mehr. Da Jens sich nicht
zu Tabellenstruktur und Queries äußert, kann man ihm keine
Empfehlung geben. Aber mir kommt nach Lektüre seiner bisherigen
Artikel das Ganze sowieso ziemlich merkwürdig vor. Ich vermute
da gravierende Fehler in der Modellierung.


XL

Re: Alter Table - physisch?

Hallo Axel,

.....

> ABER: all diese Optimierungen sind eher esoterisch. Vernünftige
> Indizes bringen normalerweise deutlich mehr. Da Jens sich nicht
> zu Tabellenstruktur und Queries äußert, kann man ihm keine
> Empfehlung geben. Aber mir kommt nach Lektüre seiner bisherigen
> Artikel das Ganze sowieso ziemlich merkwürdig vor. Ich vermute
> da gravierende Fehler in der Modellierung.

Dem kann abgeholfen werden.
Die Tabelle(n) bestehen aus einer 5 Spalten :
ia (Anfangsnumer) -double- mit index
ie (endnummer) -double - mit index
Wert - zugeordnerter String
woher - Von Wo/Wem Stammt die Eingabe
time - Wann wurde diese Eingabe gemacht.

Kleines Beispiel
z.B.
ia ie Wert
15 0 Tastatur
23 0 Autobahn
10 30 Handy
12 20 Lampe
20 40 Monitor
.....

Im Prinzip geht es darum aus einer Nummer einen zugeordneten String zu
holen.
Dieser kann direkt sein, also ohne Endwert. wie oben bei 15 sollte
er "Tastatur" zurück geben.
Gibt man 16 ein, und es wird kein direkter Wert gefunden, so soll nach einem
gültigem nächsten Bereich gesucht werden in de die 16 vorkommt. Hier in dem
Fall
wäre es "Lampe ", da 16 zwischen 12 und 20 liegt. (aber nicht Handy!. Zwar
liegt auch
die 16 zwischen 10 und 30 aber der Abstand ist zu gross. Das war es auch
schon.

Nun habe ich mehrere tabellen z.B.
Tab0-10000 - Tab10000-20000 - Tab20000-30000 usw.

Da nur die Eingangsnummer bei der Abfrage benötigt wird, kann ich durch eine
einfach DIV-Operation
schon mal die Tabelle herausbekommen ( floor($nummer/10000)) . Dadurch
brauch ich nur noch
in der relaventen Tabelle suchen da sie überschaubar "kurz" ist. Ich war der
Meinung es sucht sich
schneller in 100.000 Datensäten als in 5 Mill. Noch dazu da sehr viele
Abfragen in kurzer Zeit kommen.

Nun kam mein Ansatz mit der physichen Sortierung. Ich dachte, wenn es
sortiert nach Differenzen vorliegt, also so:
ia ie Wert
15 0 Tastatur (diff 0)
23 0 Autobahn (diff 0)
12 20 Lampe (diff 8)
10 30 Handy (diff 20)
20 40 Monitor (diff 20)

reicht ein "select WERT from $tabelle where i$eingabe>=ia and $eingabe <= ie
limit 0,1".
Dieses würde mir Ohne extra Berechnungen innerhalb des Querys das gewünschte
Ergebnis liegern, da er hier in diesem Fall zuerst auf die Lampe trifft, und
dann
erst aufs Handy (solange es keine direkte Nummer gibt).

Das war mein ganzes Anliegen.
Mag sein, das ich mich total verrannt habe, Vielleicht sehe ich auch den
Wald vor Bäumen nicht mehr, aber ich habs für eine gute Idee gehalten, ohne
extra
Berechnungen zurecht zu kommen.

Ich hoffe es ist nicht zu kompliziert ausgedrückt.

Gruss Jens.
(der mal wieder viel zu viel geschrieben hat)

Re: Alter Table - physisch?

Jens Meyer schrieb:

> Die Tabelle(n) bestehen aus einer 5 Spalten :
> ia (Anfangsnumer) -double- mit index
> ie (endnummer) -double - mit index

Bisher hast du nur INTEGER-Werte gezeigt. Warum also DOUBLE (IIRC 80
Bytes statt 4 Bytes Speicher-/Vergleichsbedarf)

> Wert - zugeordnerter String
> woher - Von Wo/Wem Stammt die Eingabe
> time - Wann wurde diese Eingabe gemacht.

> Nun habe ich mehrere tabellen z.B.
> Tab0-10000 - Tab10000-20000 - Tab20000-30000 usw.
>
> Da nur die Eingangsnummer bei der Abfrage benötigt wird, kann ich durch eine
> einfach DIV-Operation
> schon mal die Tabelle herausbekommen ( floor($nummer/10000)) .

Diese Operation in einer interpretierten Sprache wie PHP dauert
wahrscheinlich länger als das Filtern aus einer Tabelle mit einer
optimierten DB-Engine. Packe alle in eine Tabelle. Dann bekommst du auch
keine Probleme mit Datensätzen, die ia=9000, ie=11000 enthalten (und
sag' jetzt nicht: "Sowas kommt nicht vor!". Es wird kommen).

Dadurch
> brauch ich nur noch
> in der relaventen Tabelle suchen da sie überschaubar "kurz" ist. Ich war der
> Meinung es sucht sich
> schneller in 100.000 Datensäten als in 5 Mill. Noch dazu da sehr viele
> Abfragen in kurzer Zeit kommen.

Das spart vielleicht 1 Index-Zugriff. Dauer (geschätzt) 1/100 ms.

> Nun kam mein Ansatz mit der physichen Sortierung. Ich dachte, wenn es
> sortiert nach Differenzen vorliegt, also so:
> ia ie Wert
> 15 0 Tastatur (diff 0)
> 23 0 Autobahn (diff 0)
> 12 20 Lampe (diff 8)
> 10 30 Handy (diff 20)
> 20 40 Monitor (diff 20)
>
> reicht ein "select WERT from $tabelle where i$eingabe>=ia and $eingabe <= ie
> limit 0,1".

UPDATE SET ia=14, ie=16 WHERE wert='Lampe'
=> verloren (und sag' jetzt nicht: "Sowas kommt nicht vor!". Es wird
kommen)!

> Dieses würde mir Ohne extra Berechnungen innerhalb des Querys das gewünschte
> Ergebnis liegern, da er hier in diesem Fall zuerst auf die Lampe trifft, und
> dann erst aufs Handy (solange es keine direkte Nummer gibt).

Manchmal zufällig ja. Immer sicher nein.

> Mag sein, das ich mich total verrannt habe, Vielleicht sehe ich auch den
> Wald vor Bäumen nicht mehr, aber ich habs für eine gute Idee gehalten, ohne
> extra Berechnungen zurecht zu kommen.

Bleibe auf der sicheren Seite. Du kämpfst im falschen Krieg.

MfG
Niels

--
| http://www.kolleg.de · Das Portal der Kollegs in Deutschland |
| http://www.bsds.de · BSDS Braczek Software- und DatenSysteme |
| Webdesign · Webhosting · E-Commerce · Mambo Content Management |
------------------------------------------------------------ ----

Re: Alter Table - physisch?

On Thu, 19 Jan 2006 03:45:31 +0100, Niels Braczek wrote:

>> es sind ca.. 800.000 Datensätze. Es kommen selten welche hinzu, und wenn,
>> dann viele auf einmal.
>
> Das ist für ein DBMS gar nichts.

Bis ~100K Datensätze ist es mickrig und MySQL rennt wie der Teufel. Danach
ist es nur noch verdammt schnell.

Gruß,
Matthias

Re: Alter Table - physisch?

Hallo Niels,

....
> Bisher hast du nur INTEGER-Werte gezeigt. Warum also DOUBLE (IIRC 80
> Bytes statt 4 Bytes Speicher-/Vergleichsbedarf)

das liegt an der begrenzung von integer deshalb double. Es können Nummern
im Millardenbereich vorkommen


....
> Diese Operation in einer interpretierten Sprache wie PHP dauert
> wahrscheinlich länger als das Filtern aus einer Tabelle mit einer
> optimierten DB-Engine. Packe alle in eine Tabelle. Dann bekommst du auch
> keine Probleme mit Datensätzen, die ia=9000, ie=11000 enthalten (und
> sag' jetzt nicht: "Sowas kommt nicht vor!". Es wird kommen).

den Zeitverlust bei php habe ich bisher nicht bedacht. Das Problem der
Tabellengrenzen gibt es nicht, da per Definition kein Bereich über eine
100.000der Grenze geht.

.....
> Dadurch
>> brauch ich nur noch
>> in der relaventen Tabelle suchen da sie überschaubar "kurz" ist. Ich war
>> der
>> Meinung es sucht sich
>> schneller in 100.000 Datensäten als in 5 Mill. Noch dazu da sehr viele
>> Abfragen in kurzer Zeit kommen.
>
> Das spart vielleicht 1 Index-Zugriff. Dauer (geschätzt) 1/100 ms.
>

Hier komme ich nicht ganz mit ;(
angenommen ich habe eine Tabelle mit 200.000 Einträgen.
1. Spalte durchnumeriert 1-200.000 2. Spalte irgendwelche Werte.
ist der Eingabewert X=100 und ich vergleiche
ist X > als SpalteA und X kleiner SpalteB dann muss doch mysql
mindestens 190.000 Datensätze durchkämmen, da er ja nicht weiss
wie zu jedem Wert der SpalteA der Wert der SpalteB lautet. (Analog
bei 5 Mill Datensätzen 4,99.. Mill - Das wäre schon ein enormer Zeitverlust)

Oder geht mysql da anders vor?

Gäbe es nur die SpalteA dann ist es klar, da wäre es schnell gefunden.
Deshalb mehrere Tabellen. Aber ich bin dabei das Zeitverhalten dank
Eurer Anregungen zu testen.


.....
>> reicht ein "select WERT from $tabelle where i$eingabe>=ia and $eingabe <=
>> ie
>> limit 0,1".
>
> UPDATE SET ia=14, ie=16 WHERE wert='Lampe'
> => verloren (und sag' jetzt nicht: "Sowas kommt nicht vor!". Es wird
> kommen)!

nach jedem Update (selten) wird(wurde?) die tabelle neu sortiert


....
> Bleibe auf der sicheren Seite. Du kämpfst im falschen Krieg.

Da bin ich ja dabei, Es soll ja hier nicht alles umsonst gewesen sein :)

Gruss Jens

Re: Alter Table - physisch?

"Jens Meyer" wrote:

> Die Tabelle(n) bestehen aus einer 5 Spalten :
> ia (Anfangsnumer) -double- mit index
> ie (endnummer) -double - mit index
> Wert - zugeordnerter String
> woher - Von Wo/Wem Stammt die Eingabe
> time - Wann wurde diese Eingabe gemacht.

Und was bedeuten `ia` und `ie`?
Was ist der jeweilige zulässige Wertebereich?

> Kleines Beispiel
> ia ie Wert
> 15 0 Tastatur
> 23 0 Autobahn
> 10 30 Handy
> 12 20 Lampe
> 20 40 Monitor

Falls `ia` und `ie` ein Intervall aufspannen sollen, müßte man
dann nicht (ia <= ie) fordern? Wieso gibt es hier Rows mit ie=0?

> Im Prinzip geht es darum aus einer Nummer einen zugeordneten String zu
> holen.

D.h. du hast einen (1) Parameter X und willst maximal eine Row
als Ergebnis?

> Dieser kann direkt sein, also ohne Endwert. wie oben bei 15 sollte
> er "Tastatur" zurück geben.

D.h. eine Row mit ia=X ist ein Treffer. Wenn es mehrere gibt,
ist es dann egal, welche als Ergebnis zurückkommt?

> Gibt man 16 ein, und es wird kein direkter Wert gefunden, so soll nach einem
> gültigem nächsten Bereich gesucht werden in de die 16 vorkommt.

STOP. Du verwendest Begriffe, die du nicht definiert hast. Was ist
ein "gültiger Bereich"? Ich rate: ia < X <= ie

> Hier in dem Fall
> wäre es "Lampe ", da 16 zwischen 12 und 20 liegt. (aber nicht Handy!. Zwar
> liegt auch
> die 16 zwischen 10 und 30 aber der Abstand ist zu gross. Das war es auch
> schon.

Welcher Abstand? Ich rate: (ie-ia) falls (ie!=0)

Ich rate also mal deine Suchbedingung: eine Row ist ein Treffer, wenn

ia<=X<=ie UND (ie-ia) minimal

Habe ich soweit richtig geraten? Dann würde ich vorschlagen, daß du als
erstes mal deine Daten normalisierst und alle Rows mit ie=0 auf ie=ia
setzt. Dann liefert folgende Query dein Ergebnis:

SELECT ..., ie-ia AS delta
FROM ...
WHERE X BETWEEN ie AND ia
ORDER BY delta ASC
LIMIT 1

Wenn du einen Index auf (ie, ia) hast, wird das ein Range-Scan auf dem
Index und genau 1 (ein) Zugriff auf das Datenfile. Teuer werden können
Differenzbildung und Sortierung. Deswege wäre es sinnvoll, den kleins-
ten passenden (möglichst integralen) Typ für `ia` und `ie` zu nehmen.


> Da nur die Eingangsnummer bei der Abfrage benötigt wird, kann ich durch eine
> einfach DIV-Operation
> schon mal die Tabelle herausbekommen ( floor($nummer/10000)) .

Vergiß es. Ein Index-Lookup ist viel schneller.


XL

Re: Alter Table - physisch?

Jens Meyer schrieb:
> Hallo Niels,
>
>> Bisher hast du nur INTEGER-Werte gezeigt. Warum also DOUBLE (IIRC 80
>> Bytes statt 4 Bytes Speicher-/Vergleichsbedarf)
>
> das liegt an der begrenzung von integer deshalb double. Es können Nummern
> im Millardenbereich vorkommen

Ok. 64Bit stehen nicht generell zur verfügung.

> ...
>> Diese Operation in einer interpretierten Sprache wie PHP dauert
>> wahrscheinlich länger als das Filtern aus einer Tabelle mit einer
>> optimierten DB-Engine. Packe alle in eine Tabelle. Dann bekommst du auch
>> keine Probleme mit Datensätzen, die ia=9000, ie=11000 enthalten (und
>> sag' jetzt nicht: "Sowas kommt nicht vor!". Es wird kommen).
>
> den Zeitverlust bei php habe ich bisher nicht bedacht. Das Problem der
> Tabellengrenzen gibt es nicht, da per Definition kein Bereich über eine
> 100.000der Grenze geht.

Du schriebst:
> Nun habe ich mehrere tabellen z.B.
> Tab0-10000 - Tab10000-20000 - Tab20000-30000 usw.

Das sind Zehntausender-Grenzen, nicht Hunderttausender. Aber egal, die
Entscheidung, welche Tabelle zu verwenden ist dauert wahrscheinlich
länger als der Zugriff in *eine* große Tabelle.

>> Das spart vielleicht 1 Index-Zugriff. Dauer (geschätzt) 1/100 ms.
>
> Hier komme ich nicht ganz mit ;(
> angenommen ich habe eine Tabelle mit 200.000 Einträgen.
> 1. Spalte durchnumeriert 1-200.000 2. Spalte irgendwelche Werte.
> ist der Eingabewert X=100 und ich vergleiche
> ist X > als SpalteA und X kleiner SpalteB dann muss doch mysql
> mindestens 190.000 Datensätze durchkämmen, da er ja nicht weiss
> wie zu jedem Wert der SpalteA der Wert der SpalteB lautet. (Analog
> bei 5 Mill Datensätzen 4,99.. Mill - Das wäre schon ein enormer Zeitverlust)

Axel hat das heute Mittag schon mal angerissen: Der Aufwand für
Indexzugriffe steigt logarithmisch und nicht linear. In dem von ihm
angeführten Beispiel bedeuten 100mal so viele Datensätze im Schnitt 1(!)
zusätzlichen Indexzugriff.

> Oder geht mysql da anders vor?

Natürlich.
Erste Bedingung: ia Zweite Bedingung: x (eingeschränkt auf den Bereich der bereits bestimmten Datensätze), erledigt.
Die Handvoll Datensätze sortieren, erledigt.
Den ersten ausliefern, fertig.
Geht ganz fix.

> Da bin ich ja dabei, Es soll ja hier nicht alles umsonst gewesen sein :)

Dann wollen wir's jetzt auch mal gut sein lassen. Du hast sicherlich
verstanden, dass andere Äcker fruchtbarer sind.

MfG
Niels

--
| http://www.kolleg.de · Das Portal der Kollegs in Deutschland |
| http://www.bsds.de · BSDS Braczek Software- und DatenSysteme |
| Webdesign · Webhosting · E-Commerce · Mambo Content Management |
------------------------------------------------------------ ----

Re: Alter Table - physisch?

Axel Schwenke schrieb:
> Welcher Abstand? Ich rate: (ie-ia) falls (ie!=3D0)
>=20
> Ich rate also mal deine Suchbedingung: eine Row ist ein Treffer, wenn
>=20
> ia<=3DX<=3Die UND (ie-ia) minimal
>=20
> Habe ich soweit richtig geraten? Dann würde ich vorschlagen, daß du=
als
> erstes mal deine Daten normalisierst und alle Rows mit ie=3D0 auf ie=3D=
ia
> setzt. Dann liefert folgende Query dein Ergebnis:
>=20
> SELECT ..., ie-ia AS delta
> FROM ...
> WHERE X BETWEEN ie AND ia
> ORDER BY delta ASC
> LIMIT 1
>=20
> Wenn du einen Index auf (ie, ia) hast, wird das ein Range-Scan auf dem
> Index und genau 1 (ein) Zugriff auf das Datenfile. Teuer werden könne=
n
> Differenzbildung und Sortierung. Deswege wäre es sinnvoll, den kleins=
-
> ten passenden (möglichst integralen) Typ für `ia` und `ie` zu nehme=
n.

Vielleicht macht es auch Sinn, die Differenz als eigene Spalte zu=20
führen, wenn es viele Treffer im entsprechenden "Wertebereich" gibt.=20
Dann würdest du bei den Inserts/Updates auch die entsprechende=20
Differenzspalte updaten bzw. per Trigger updaten lassen.

Die hier beschriebene Normalisierung klingt sehr gut.

--=20
HTH,
Fabian

Re: Alter Table - physisch?

Jens Meyer schrieb:

> Ich dachte durch das "vorsortieren" helfe ich MySql.

Auch die "physische Sortierung in der Tabelle" ist eigentlich nur die
"logische Sortierung im Dateisystem". Die Datei kann ja ganz dolle auf
der Festplatte rumfragmentieren.

Grüße, Matthias

Re: Alter Table - physisch?

Fabian Schladitz wrote:
> Axel Schwenke schrieb:

>> SELECT ..., ie-ia AS delta
>> FROM ...
>> WHERE X BETWEEN ie AND ia
>> ORDER BY delta ASC
>> LIMIT 1
>>
>> Wenn du einen Index auf (ie, ia) hast, wird das ein Range-Scan auf dem
>> Index und genau 1 (ein) Zugriff auf das Datenfile. Teuer werden können
>> Differenzbildung und Sortierung. Deswege wäre es sinnvoll, den kleins-
>> ten passenden (möglichst integralen) Typ für `ia` und `ie` zu nehmen.
>
> Vielleicht macht es auch Sinn, die Differenz als eigene Spalte zu
> führen, wenn es viele Treffer im entsprechenden "Wertebereich" gibt.

Nicht unbedingt. Es hat einen guten Grund, warum ich die Differenz
nicht als zusätzliche Spalte speichern will. In obiger Form kann
MySQL alle notwendigen Daten direkt aus dem Index nehmen (EXPLAIN
zeigt "using index"). Das spart jede Menge I/O.

Man könnte nun die Differenz noch als dritte Spalte in den Index
nehmen und hätte wieder diesen Vorteil. Allerdings wäre der Index
dann 1.5x so groß - ohne weitere Information zu enthalten.
Andererseits ist es absolut wünschenswert, den Index so klein wie
möglich zu halten. Idealerweise sollte der Index ins RAM passen.

@Jens: obige Query würde nochmal wesentlich schneller laufen, wenn
du eine maximale Intervallgröße festlegen würdest (k.A. ob das dein
Problem hergibt). Wenn du z.B. (ie-ia)<=100 fordern würdest, wäre

SELECT
WHERE ia BETWEEN X-100 AND X
AND ie BETWEEN X AND X+100

nochmal ein deutlicher Fortschritt.


PS: für solche rein MySQL-spezifischen Fragen haben wir nebenan
eine spezialisierte Newsgroup: de.comp.datenbanken.mysql

XL

Re: Alter Table - physisch?

Jens Meyer wrote:
> Oder spielt es keine Rolle, wenn eine Tabelle über index verfügt, ob
> 800.000 oder 56 Mil Datensätze vohanden sind? - Zeitmäßig meine ich?

Das wird in dieser Größenordnung wohl schon einen Unterschied machen.

Die wesentliche Bremse bei Datenbankanfragen ist die Festplatte. Solange
alles im Speicher gemacht werden kann, ist es schnell. Sobald viel von der
Platte gelesen werden muss wird es langsam.

Das Ganze hängt also auch stark davon ab, wie viel Speicher man zur
Verfügung hat und dass MySQL auch so konfiguriert ist, dass der Speicher
richtig genutzt wird.

Ob also 10 kleine Tabellen bzw Indizes oder 1 große im Speicher sind, wird
also wohl eher nicht so viel ausmachen. Nur wenn z.B. auf manche Tabellen
wesentlich seltener zugegriffen wird als auf andere, wird es wohl schon
was ausmachen.

Ich würde aber schon erstmal versuchen, mit optimalen Feldtypen, Indizes,
MySQL-Konfiguration und SQL-Statements das mögliche rauszuholen. Denn das
Tabellenaufteilen ist ja ziemlich umständlich und problematisch. Bei
800.000 Datensätzen mit so wenigen Feldern sollte es problemlos mit einer
Tabelle gehen.

cu, Magnum

--
Carl Magnus Rosenbaum M.A. Tel: 089 - 700 666 26
Administration - Programmierung - Weiterbildung Fax: 089 - 700 666 86
http://cmr.forestfactory.de/ Mobil: 0163 - 700 666 2
PGP Fingerprint: DEBC 3C99 EF1D 74F0 D4C7 EFF5 C268 3690 0EA1 7641

Re: Alter Table - physisch?

Hallo Magnus,

....
>
> Das wird in dieser Größenordnung wohl schon einen Unterschied machen.
>
> Die wesentliche Bremse bei Datenbankanfragen ist die Festplatte. Solange
> alles im Speicher gemacht werden kann, ist es schnell. Sobald viel von der
> Platte gelesen werden muss wird es langsam.
>

Ich habe es mal ausprobiert, und es macht einen erheblichen Unterschied,
ob ich 3000 oder 20000 Datensätze habe.
Habe mal testweise 8000 DS in einer Schleife suchen lassen, und es ergaben
sich Unterschiede im sekundenbereich.
Ich denke es liegt an der Bedinung x > spalte 1 und x < spalte 2.

Von der physichen Sortierung bin ich tatsächlich weg, es machte wirklich
keinen Unterschied, weil die Ergebnismenge recht klein ist.

> Ob also 10 kleine Tabellen bzw Indizes oder 1 große im Speicher sind, wird
> also wohl eher nicht so viel ausmachen. Nur wenn z.B. auf manche Tabellen
> wesentlich seltener zugegriffen wird als auf andere, wird es wohl schon
> was ausmachen.

Es ist wirklich so das bestimmte Bereiche seltener vorkommen.

> Ich würde aber schon erstmal versuchen, mit optimalen Feldtypen, Indizes,
> MySQL-Konfiguration und SQL-Statements das mögliche rauszuholen. Denn das
> Tabellenaufteilen ist ja ziemlich umständlich und problematisch. Bei
> 800.000 Datensätzen mit so wenigen Feldern sollte es problemlos mit einer
> Tabelle gehen.

Das Tabellen aufteilen geht recht einfach mit einem simplen DIV-Operator.


Gruss Jens