Umkreissuche sehr langsam

[Thorsten]

Morgen

Meine Umkreissuche ist leider sehr langsam. Bei z.b. 5000 Datensätzen gibt es nur noch ein Timeout.

Es gibt 2 Tabellen orte wo sich die plz mit den Koordinaten aufhalten und die Tabelle Events wo die plz und die daten zu den Events gespeichert sind.

Meine SQL abfrage schaut so aus.

SELECT
    o.ort,
    o.plz,
    e.event_id,
    e.kat_id,
    e.titel,
    e.fsa_datum,
    e.location,
    k.kategorie,
    b.thumb,
    (6367.41*SQRT(2*(1-cos(RADIANS(breite))
    *cos(".$breite.")*(sin(RADIANS(laenge))
    *sin(".$laenge.")+cos(RADIANS(laenge))
    *cos(".$laenge."))-sin(RADIANS(breite))
    *sin(".$breite."))))
    AS Distance
    FROM
    orte AS o
    JOIN
    event AS e
    ON o.plz = e.plz
    JOIN
    event_kat AS k
    ON e.kat_id = k.kat_id
    LEFT JOIN
    bilder AS b
    ON e.event_id = b.event_id OR b.event_id IS NULL
    WHERE
    e.gespert = 0
    AND
    6367.41*SQRT(2*(1-cos(RADIANS(breite))
    *cos(".$breite.")
    *(sin(RADIANS(laenge))
    *sin(".$laenge.")+cos(RADIANS(laenge))
    *cos(".$laenge."))-sin(RADIANS(breite))
    *sin(".$breite."))) <= ".$umkreis."
    ORDER BY Distance
OR e.plz = '$plz'

Ich bekomme es einfach nicht hin, diese Abfrage so zu optimieren das es erheblich schneller geht.

Die Event Tabelle sollte problemlos mehrere 100.000 Datensätze aufnehmen. Aber mit der Jetzigen Abfrage wird das unmöglich sein.

Ich verwende MYSQL 5.0.34 auf dem Webspace.

Das weitere Problem ich benötige eine Blätterfunktion für die Ergebnisse. Um die Anzahl der Datensätze zu ermitteln lasse ich fast die gleiche Abfrage 2 mal auf die DB los. Der Umkreis in dem gesucht wird liegt bei max 200km. Die geo Daten kommen von opengeodb. Und sind auf das minimum reduziert. Sprich 8180 plz in der Tabelle Orte.

Bin echt DAnkbar für jede Hilfe

[Thorsten]

ORDER BY Distance

OR e.plz = '$plz'

gehört natürlich so

OR e.plz = '$plz'
ORDER BY Distance

[ChrisB]

Hi,

SELECT
    [...],
    (6367.41*SQRT(2*(1-cos(RADIANS(breite))
    *cos(".$breite.")*(sin(RADIANS(laenge))
    *sin(".$laenge.")+cos(RADIANS(laenge))
    *cos(".$laenge."))-sin(RADIANS(breite))
    *sin(".$breite."))))

Woher kommt diese Formel?

Ausser die Berechnungen mit den "festen" Spaltenwerten nicht jedes mal erneut durchfuehren zu lassen, sondern ebenfalls in eigenen Spalten abzulegen, fallen mir spontan keine Optimierungsmoeglichkeiten ein (und ob das so viel bringt, weiss ich auch nicht mal).

Das weitere Problem ich benötige eine Blätterfunktion für die Ergebnisse. Um die Anzahl der Datensätze zu ermitteln lasse ich fast die gleiche Abfrage 2 mal auf die DB los.

Nu, das muss ja nun wirklich nicht sein - Stichwort FOUND_ROWS().

MfG ChrisB

[Thorsten]

Die Formel kommt von open geo db zumindest ist Sie dort so in der faq erklärt worden und berücksichtigt sogar die Erdkrümmung

Anzahl der Datensätze muss ich aber voher doch abfragen wie soll ich sonst Start und end als Limit sätzen bei der oben genannten abfrage.

Dafür verwende ich mysql_num_rows.

Ich Glaube das die Rechnung für jedes Event mal vorhanden PLZ durchführt. Sprich bei 5 erfasten Events 40900 mal.

Ich bin auch schon am überlegen ob ich mir ein Tabelle anlegen die alle Ergebnisse beinhaltet, die hätte dann ein paar mehr Datensätze, aber ich hätte dann nur einzelne Selects ohne Rechnungen. Und ganz wichtig der Vollscan auf die Tabelle währe verschwunden.

Mh für weitere Ideen immer Dankbar bin.

[dedlfix]

echo $begrüßung;

Anzahl der Datensätze muss ich aber voher doch abfragen wie soll ich sonst Start und end als Limit sätzen bei der oben genannten abfrage.

Bei der ersten Anfrage fängst du einfach blind mit mit einem LIMIT 0,x an, wobei x die Anzahl der maximal darzustellenden Zeilen ist. Wie die unlimitierte Anzahl der Ergebnismenge zu ermitteln ist, ist im zweiten Teil der Antwort zu lesen. Diese Zahl brauchst du ja nur um einen Pager (Seiten-Navigationselement) auf Seite 1 bis Seite n zu beschränken. Wenn jemand zu Fuß (sei es aus Absicht, oder weil solch ein Seitenaufruf noch "von früher" z.B. in seinen Lesezeichen hängt) eine Seitenzahl > n anfordert, dann lass das Script ruhig so arbeiten, als sei das normal. Es tritt dabei kein Schaden ein, es ergibt nur eine leere Ergebnismenge, weil das LIMIT ins Leere fasst.

Es sind eventuell kleine Korrekturen z.B. für $aktuelleSeite erforderlich. Diese kannst du generell mit

$aktuelleSeite = max(1, min($aktuelleSeite, ceil($unlimitierteAnzahl / $ZeilenProSeite)));

in den Bereich 1..n zurückholen, wenn sie mal da rausgelaufen ist. Diese Korrektur brauchst du aber erst für den Pager anzuwenden, damit der nicht "komisch" aussieht. Für das LIMIT ist sie nicht erforderlich.

Dafür verwende ich mysql_num_rows.

Für ein mysql_num_rows() müssen alle Ergebnisdatensätze zum Client übertragen werden, damit diese Funktion sie zählen kann. Sie anschließend wegzuwerfen oder daraus ein LIMIT zu Fuß nachzubilden ist eine unnötige Ressourcenverwendung. Ein COUNT() in der Datenbank muss nur diesen einen Wert zurückliefern. Du möchtest aber von allen passenden Datensätzen nur dessen Anzahl sowie einen Teil der Ergebnismenge, den du mit LIMIT einschränkst. Genau für den Fall ist das Gespann SQL_CALC_FOUND_ROWS und FOUND_ROWS() vorgesehen. Damit erreichst du genau dein Ergebnis ohne unnötigen Overhead. Anwendungsbeispiel an der verlinkten Handbuchstelle.

echo "$verabschiedung $name";

[fastix®]

Moin!

Ich habe genau das schon mal gemacht, aber mit einer Ur-Version der GeoDB.

1. Schau nach, ob alles, was in where- oder Join-Klauseln steht einen Index hat.

Wenn das nicht reicht:

2. Ich hab das mal so gelöst, dass ich mit der Datenbank die Orte in einem passenden Viereck raussuche und dann erst mit PHP prüfe, ob die wirklich auch im Kreis sind.

http://dbinterface,de/geo/

Und das geht ziemlich schnell. (über 15.000 Orte)

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

[Thorsten]

Moin!

Ich habe genau das schon mal gemacht, aber mit einer Ur-Version der GeoDB.

1. Schau nach, ob alles, was in where- oder Join-Klauseln steht einen Index hat.

Ein Index ist gesätzt.

Wenn das nicht reicht:

2. Ich hab das mal so gelöst, dass ich mit der Datenbank die Orte in einem passenden Viereck raussuche und dann erst mit PHP prüfe, ob die wirklich auch im Kreis sind.

http://dbinterface,de/geo/

Und das geht ziemlich schnell. (über 15.000 Orte)

Das wird probleme geben da ich dann die Ergebnis mänge in einer Schleife zur DB schicken muss und das kann bei einem Umkreiss durchaus  1000 mal der fall sein. Glaube nicht das es sinnig ist.»»

Kann man mit einem Subselect nicht verhinder, das er die Rechnung pro Event immer wiederholt weil das ist ja eh blödsinn.
Weil die Rechnung ansich geht ja rasend schnell. Wenn ich nur den Umkreiss berechne und mir alle plz in dem kreis ausgeben lasse dauert das grade mal 0.4 sek. Bloß der join auf die event Tabelle kostet dann richtig Zeit weil wie schon gesagt bei 5 Events wird die Rechnung 40900 mal durchgeführt was nicht sein muss.

Wenn ich den Select aber Teile habe ich wieder das Problem mit der 2 Abfrage die ich dann wieder in einer Schleife laufen lassen muss.

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix®

[Ilja]

yo,

ich gebe zu, dass ich auch nicht der klassen-primus von sql-schreibstil bin, aber deine schreibeweise ist grausam, viel zu sehr gestreckt.

SELECT o.ort, o.plz
FROM orte AS o
INNER JOIN event AS e ON o.plz = e.plz
INNER JOIN event_kat AS k ON e.kat_id = k.kat_id
LEFT JOIN bilder AS b ON e.event_id = b.event_id OR b.event_id IS NULL
WHERE e.gespert = 0
AND 6367.41*SQRT(2*(1-cos(RADIANS(breite))
    *cos(".$breite.")
    *(sin(RADIANS(laenge))
    *sin(".$laenge.")+cos(RADIANS(laenge))
    *cos(".$laenge."))-sin(RADIANS(breite))
    *sin(".$breite."))) <= ".$umkreis."
OR e.plz = '$plz'
ORDER BY Distance
;

ok, das reicht erst mal für testzwecke, du musst die projektion erst enimal nicht mit lauter spalten verkomplizieren, für die geschwindigkeit ist sie in aller regel zu vernachläßigen.

nnu aber zu deinem problem,  tuning bedeutet probieren geht über studieren. ich kann das nicht oft genug betonen, es gibt keine patentlösung. unterschiedliche dbms, versionen, designs und dateninhalte verhalten sich anders und macht es schwierig, allgemeingültige regeln aufzustellen.

aber das erste was ich machen würde, die OR bedingung in einen UNION ALL auslagern, sprich du hast zweimal die gleiche abfrage. beide abfragen würde ich auf performance erst einmal einzeln testen. Auch die Sortierung würde ich erst einmal wegfallen lassen.

SELECT o.ort, o.plz
FROM orte AS o
INNER JOIN event AS e ON o.plz = e.plz
INNER JOIN event_kat AS k ON e.kat_id = k.kat_id
LEFT JOIN bilder AS b ON e.event_id = b.event_id OR b.event_id IS NULL
WHERE e.gespert = 0
AND 6367.41*SQRT(2*(1-cos(RADIANS(breite))
    *cos(".$breite.")
    *(sin(RADIANS(laenge))
    *sin(".$laenge.")+cos(RADIANS(laenge))
    *cos(".$laenge."))-sin(RADIANS(breite))
    *sin(".$breite."))) <= ".$umkreis."
UNION ALL
SELECT o.ort, o.plz
FROM orte AS o
INNER JOIN event AS e ON o.plz = e.plz
INNER JOIN event_kat AS k ON e.kat_id = k.kat_id
LEFT JOIN bilder AS b ON e.event_id = b.event_id OR b.event_id IS NULL
WHERE e.plz = '$plz'
;

Im ersten Union teil benutzt due funktionen in der WHERE klausel, das führt in aller regel zu dem problem, dass ein index nicht genutzt werden kann, es sei den, es ist ebenfalls eni funktionaler index. keine ahnung, ob mysql das in der version kann. Auf jeden fall musst du dir den ausführungsplan anschauen, den das dbms ausführt und dann sieht man weiter.

Kritisch könnte auch der LEFT JOIN sein, versuche beiden abfragen des Unions mal ohne ihn, vor allem mit der OR verbindnug drinne.

Letztlich musst du dir auch überlegen, ob du die gleiche ergebnismenge nicht auch mit einer "besseren" abfrage bekommen kannst.

Ilja

[Thorsten]

yo,

ich gebe zu, dass ich auch nicht der klassen-primus von sql-schreibstil bin, aber deine schreibeweise ist grausam, viel zu sehr gestreckt.

Jede so wie er es mag oder?

SELECT o.ort, o.plz
FROM orte AS o
INNER JOIN event AS e ON o.plz = e.plz
INNER JOIN event_kat AS k ON e.kat_id = k.kat_id
LEFT JOIN bilder AS b ON e.event_id = b.event_id OR b.event_id IS NULL
WHERE e.gespert = 0
AND 6367.41*SQRT(2*(1-cos(RADIANS(breite))
    *cos(".$breite.")
    *(sin(RADIANS(laenge))
    *sin(".$laenge.")+cos(RADIANS(laenge))
    *cos(".$laenge."))-sin(RADIANS(breite))
    *sin(".$breite."))) <= ".$umkreis."
OR e.plz = '$plz'
ORDER BY Distance
;

ok, das reicht erst mal für testzwecke, du musst die projektion erst enimal nicht mit lauter spalten verkomplizieren, für die geschwindigkeit ist sie in aller regel zu vernachläßigen.

nnu aber zu deinem problem,  tuning bedeutet probieren geht über studieren. ich kann das nicht oft genug betonen, es gibt keine patentlösung. unterschiedliche dbms, versionen, designs und dateninhalte verhalten sich anders und macht es schwierig, allgemeingültige regeln aufzustellen.

aber das erste was ich machen würde, die OR bedingung in einen UNION ALL auslagern, sprich du hast zweimal die gleiche abfrage. beide abfragen würde ich auf performance erst einmal einzeln testen. Auch die Sortierung würde ich erst einmal wegfallen lassen.

SELECT o.ort, o.plz
FROM orte AS o
INNER JOIN event AS e ON o.plz = e.plz
INNER JOIN event_kat AS k ON e.kat_id = k.kat_id
LEFT JOIN bilder AS b ON e.event_id = b.event_id OR b.event_id IS NULL
WHERE e.gespert = 0
AND 6367.41*SQRT(2*(1-cos(RADIANS(breite))
    *cos(".$breite.")
    *(sin(RADIANS(laenge))
    *sin(".$laenge.")+cos(RADIANS(laenge))
    *cos(".$laenge."))-sin(RADIANS(breite))
    *sin(".$breite."))) <= ".$umkreis."
UNION ALL
SELECT o.ort, o.plz
FROM orte AS o
INNER JOIN event AS e ON o.plz = e.plz
INNER JOIN event_kat AS k ON e.kat_id = k.kat_id
LEFT JOIN bilder AS b ON e.event_id = b.event_id OR b.event_id IS NULL
WHERE e.plz = '$plz'
;

Im ersten Union teil benutzt due funktionen in der WHERE klausel, das führt in aller regel zu dem problem, dass ein index nicht genutzt werden kann, es sei den, es ist ebenfalls eni funktionaler index. keine ahnung, ob mysql das in der version kann. Auf jeden fall musst du dir den ausführungsplan anschauen, den das dbms ausführt und dann sieht man weiter.

Wie kann ich mir den Ausführungsplan genau anschauen? Mit Explain?

Kritisch könnte auch der LEFT JOIN sein, versuche beiden abfragen des Unions mal ohne ihn, vor allem mit der OR verbindnug drinne.

Letztlich musst du dir auch überlegen, ob du die gleiche ergebnismenge nicht auch mit einer "besseren" abfrage bekommen kannst.

Ich habe jetzt deine 2 Abfrage mal losgelassen vorher die gesätzen Indexe entfernt.
Bei 17200 Datensätzen bekahm ich zumindest mal eine Antwort dauer ca. 20 sek.

Weiter habe ich dann ein Index auf event.plz gesätzt und auf orte.plz. dabei gab es keine Antwort mehr der Server hat sich wechgehangen warum auch immer.

Also habe ich nur einen Index auf event. plz gesätzt und siehe da inhalb von 2 sek eine Antwort. Jetzt werde ich mal langsam die Datensätze erhöhen mal sehen wie sich das verhält.

Den Left join habe ich herausgenommen kein unterschied.

Ich danke dir erstmal für deine sehr Hilfreichen Beitrag und werde weiter Testen :)

Ilja

[Thorsten]

Es scheint ein kleiner Fehler in deiner Abfrage vorhanden zu sein,
da ich das Ergebnis 2 mal bekomme.

Oder habe ich jetzt nicht aufgepaßt?

[Ilja]

yo,

Jede so wie er es mag oder?

zumteil ja, zum anderen wertet es sicherlich die scripte auf, je mehr leute damit gut umgehen und sie lesen können. gute lsbarkeit von sql-anweisungen ist ein anstrebsames ziel in meinen augen.

Wie kann ich mir den Ausführungsplan genau anschauen? Mit Explain?

schau einfach unter der myql doku nach, ich komme aus der anderen welt, nämlich von oracle und kann dir die frage nicht aus dem kopf heraus beantworten.

Also habe ich nur einen Index auf event. plz gesätzt und siehe da inhalb von 2 sek eine Antwort. Jetzt werde ich mal langsam die Datensätze erhöhen mal sehen wie sich das verhält.

2 sekunden hört sich doch schon viel besser an, aber wie gesagt, den ausführnugsplan anschauen.

kurz nch ei hinweiss, dass du manche detsätze pollet bekommst, das liegt am UNION ALL, ein UNION würde doppelte Datensätze filtern, die frage ist, ob du das auch willst.

Auch würde es mich interessieren, ob mysql funktionale indexe kann.

Ilja

[Struppi]

Meine SQL abfrage schaut so aus.

..

6367.41*SQRT(2*(1-cos(RADIANS(breite))
    *cos(".$breite.")
    *(sin(RADIANS(laenge))
    *sin(".$laenge.")+cos(RADIANS(laenge))
    *cos(".$laenge."))-sin(RADIANS(breite))
    *sin(".$breite."))) <= ".$umkreis."
    ORDER BY Distance
OR e.plz = '$plz'

Hier Frage ich mich, warum du Konstante Werte von der DB Berechnen läßt.

Ich bekomme es einfach nicht hin, diese Abfrage so zu optimieren das es erheblich schneller geht.

Der 1. Schritt ist Explain, dazuz muss man aber auch Wissen welche Felder einen Index haben.

Struppi.

[Norbert]

Hallo,

zuerst ein Tip, Winkelfunktionen werden durch Naeherungsgleichungen
bestimmt, sowas dauert eben seine Zeit, ist also eher kontraproduktiv.

Du hast Gradangaben Nord und Ost und in der Anfrage auch Angaben in Nord und Ost.
Warum berechnest Du nicht gleich den Abstand in Grad?

SQRT((Ost1 - Ost2) * (Ost1 - Ost2) + (Nord1 - Nord2) * (Nord1 - Nord2)) AS Abstand

Wieviel Grad dem gesuchten Umkreis entsprechen, musst Du natuerlich vorab ermitteln.
Hier kannst Du dann auch die Erdkruemmung mit einfliessen lassen.

Gruss und Dank
Norbert

[Thorsten]

Wieviel Grad dem gesuchten Umkreis entsprechen, musst Du natuerlich vorab ermitteln.
Hier kannst Du dann auch die Erdkruemmung mit einfliessen lassen.

Magst du mir hir die Formel verraten? Da ich irgendwie nix finde oder falsch suche.

Gruss und Dank
Norbert

[Struppi]

Wieviel Grad dem gesuchten Umkreis entsprechen, musst Du natuerlich vorab ermitteln.
Hier kannst Du dann auch die Erdkruemmung mit einfliessen lassen.

Magst du mir hir die Formel verraten? Da ich irgendwie nix finde oder falsch suche.

Magst du uns die nötigen Informationen verraten um dir evtl. helfen zu können?

Struppi.

[Thorsten]

Magst du mir hir die Formel verraten? Da ich irgendwie nix finde oder falsch suche.

Magst du uns die nötigen Informationen verraten um dir evtl. helfen zu können?

Also jetzt mal so fast ganz von vorne weil das wohl nicht zum ziel führt.

Ich bin jetzt dabei die Rechnung zu ändern, weil diese einfach viel zu komplex ist und auch viel Zeit dauert wie mir ja schon mitgeteil wurde.

In der Tabelle Orte stehen PLZ Laenge und Breitenangabe.
Breite und Länge sind in Grad angegeben.
Um in der Datenbank nicht so viel zu Rechnen habe ich die oben stehende Formel übernommen.
SQRT(($laenge - o.laenge) * ($laenge - o.laenge) + ($breite - o.breite) * ($breite - o.breite))  <= ".$umkreis_grad."

Den angegebenene Umkreis muss ich also in Grad umrechnen.
Das funktioniert am Equator so

$umkreis_grad = 360 / 40008 * $umkreis;
Da ich aber weiter Nördlich Wohne stimmt der Wert 400008 nicht mehr, da der Umkreis sich der Erde ja kleiner wird.
Als anhaltspunkt wo sich denn der jenige befindet, der den Umkreis angegeben hat, habe ich seine PLZ damit kann ich ganz ein mir die Breiten und Längengrade aus der Tabelle Orte holen.

Aber ich kenne jetzt diese Formel einfach nicht.

Um das ganze noch zu vervollständigen Poste ich euch noch mal mein Datenbankdesign.

Ach und nein ich möchte nicht den Wirkungsgrad von Bomben berechnen XD. Wobei die Bezeichnung schon heftig währe. ( Events )

Das ist die Tabele Orte

CREATE TABLE orte (
  ort\_id int(11) NOT NULL,
  staat varchar(2) collate latin1_general_ci NOT NULL,
  bundesland varchar(2) collate latin1_general_ci NOT NULL,
  regierungsbezirk varchar(50) collate latin1_general_ci NOT NULL,
  landkreis varchar(50) collate latin1_general_ci NOT NULL,
  verwaltungszusammenschluss varchar(100) collate latin1_general_ci NOT NULL,
  ort varchar(100) collate latin1_general_ci NOT NULL,
  laenge double NOT NULL,
  breite double NOT NULL,
  plz smallint(5) NOT NULL,
  PRIMARY KEY  (ort\_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Und das die Tabelle event

CREATE TABLE event (
  event\_id int(11) NOT NULL auto_increment,
  kat\_id int(11) NOT NULL,
  user\_id int(11) NOT NULL,
  erstellt date NOT NULL,
  titel varchar(200) collate latin1_general_ci NOT NULL,
  location varchar(200) collate latin1_general_ci NOT NULL,
  strasse varchar(200) collate latin1_general_ci NOT NULL,
  hausnummer varchar(10) collate latin1_general_ci NOT NULL,
  plz varchar(5) collate latin1_general_ci NOT NULL,
  ort varchar(200) collate latin1_general_ci NOT NULL,
  hotline varchar(200) collate latin1_general_ci NOT NULL,
  hotmail varchar(250) collate latin1_general_ci NOT NULL,
  thotline varchar(200) collate latin1_general_ci NOT NULL,
  thotmail varchar(250) collate latin1_general_ci NOT NULL,
  fsa\_datum date NOT NULL,
  ugb\_datum date NOT NULL,
  fsu\_zeit varchar(20) collate latin1_general_ci NOT NULL,
  ugb\_zeit varchar(20) collate latin1_general_ci NOT NULL,
  beschreibung text collate latin1_general_ci NOT NULL,
  freigabe varchar(200) collate latin1_general_ci NOT NULL,
  einlasskreterien text collate latin1_general_ci NOT NULL,
  abendkasse varchar(100) collate latin1_general_ci NOT NULL,
  ticketvorverkauf varchar(100) collate latin1_general_ci NOT NULL,
  gespert int(1) NOT NULL,
  spergrund text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (event\_id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8181 ;

So und jetzt nochmal zur Zielbeschreibung.

Die Tabelle Event kann groß werden. Es muss Problemlos funktionieren mit ein Paar 100.000 Datensätzen.

Dabei kann die suche eingeschränkt werden.
Und zwar nur nach denen die nicht gespert sind also gespert = fals / 0
Und es müssen nur die Berechnet werde die in der Zukunft oder am gleichen Tag statt finden. Eine Ausgabe von Events des lätzten jahres brauche ich nicht. Zumindest nicht an dieser Stelle.

Wenn jemand den Inhalt der Tabelle orte haben möchte einfach nur sagen.

Struppi.

[Struppi]

Also jetzt mal so fast ganz von vorne weil das wohl nicht zum ziel führt.

Ich bin jetzt dabei die Rechnung zu ändern, weil diese einfach viel zu komplex ist und auch viel Zeit dauert wie mir ja schon mitgeteil wurde.

Was ist denn das Ergebnis von Explain gewesen?
Das wäre interessant, denn so wie die Tabellenstruktur aussieht sind hier erhebliche Verbesserungen möglich.

Struppi.

[Thorsten]

Was ist denn das Ergebnis von Explain gewesen?
Das wäre interessant, denn so wie die Tabellenstruktur aussieht sind hier erhebliche Verbesserungen möglich.

Ich habe mit Explain nocht nicht wirklich gearbeitet, daher hoffe ich das mein Ergebnis so Richtig ist.

Array
(
    [0] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => o
            [type] => ALL
            [possible_keys] =>
            [key] =>
            [key_len] =>
            [ref] =>
            [rows] => 8180
            [Extra] => Using where
        )

[1] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => e
            [type] => ALL
            [possible_keys] =>
            [key] =>
            [key_len] =>
            [ref] =>
            [rows] => 8180
            [Extra] => Using where
        )

)

[Struppi]

Ich habe mit Explain nocht nicht wirklich gearbeitet, daher hoffe ich das mein Ergebnis so Richtig ist.

Ich kenne nur die Ausgabe von phpMyAdmin, aber so wie ich das deuten würde, wird in deiner Abfrage kein einziger Schlüssel verwendet, das dürfte der Grund für die Performance Probleme sein.

Struppi.

[Thorsten]

Ich kenne nur die Ausgabe von phpMyAdmin, aber so wie ich das deuten würde, wird in deiner Abfrage kein einziger Schlüssel verwendet, das dürfte der Grund für die Performance Probleme sein.

Ich habe im Moment alle Schlüssel rausgenommen da es nur noch fehler gab.

Stellenweise wurde es mit Schlüssel erheblich langsamer.

Jetzt habe ich Aktuell das Problem das die Abfrage auch mit einem Fehler endet. Und zwar mit dem das der Erlaubte Speicher voll währe.
Und das Lokal auf dem XAMPP der 32MB erlaubt hat.

Naja liegt wohl mal wieder an der neuen Abfrage.

SELECT
    e.plz,
    e.ort,
    e.location,
    e.fsa_datum
    FROM
    orte AS o
    JOIN event AS e ON e.plz = o.plz
    WHERE
    SQRT(($laenge - o.laenge) * ($laenge - o.laenge) + ($breite - o.breite) * ($breite - o.breite))  <= ".$umkreis_grad."

Kennst du die Formel? Da jetzt im Moment der Der Wert von Umkreis_grad fehlhaft ist.

[Alexander (HH)]

Moin Moin!

Ich habe im Moment alle Schlüssel rausgenommen da es nur noch fehler gab.

Damit sind dann auch wohl die Indizes weg. Dann kommt etwas, was Datenbankler gerne als "Full Table Scan" bezeichnen. Die DB-Engine muß sich Zeile für Zeile durch die gesamte Tabelle wühlen. Das ist in Sachen Performance so in etwa die Höchststrafe. Zusammen mit Joins (jeder mit jedem zeilenweise durchprobieren) wundert es mich, dass die DB überhaupt noch zuckt.

Alexander

[Thorsten]

Damit sind dann auch wohl die Indizes weg. Dann kommt etwas, was Datenbankler gerne als "Full Table Scan" bezeichnen. Die DB-Engine muß sich Zeile für Zeile durch die gesamte Tabelle wühlen. Das ist in Sachen Performance so in etwa die Höchststrafe. Zusammen mit Joins (jeder mit jedem zeilenweise durchprobieren) wundert es mich, dass die DB überhaupt noch zuckt.

Der primärschlüssel auf event_id und ort_id ist immer noch vorhanden.

Der Full Tabelle Scan findet sowie statt da leider keiner der gesätzten Indexe von Explain genutzt wurde.

[Struppi]

Damit sind dann auch wohl die Indizes weg. Dann kommt etwas, was Datenbankler gerne als "Full Table Scan" bezeichnen. Die DB-Engine muß sich Zeile für Zeile durch die gesamte Tabelle wühlen. Das ist in Sachen Performance so in etwa die Höchststrafe. Zusammen mit Joins (jeder mit jedem zeilenweise durchprobieren) wundert es mich, dass die DB überhaupt noch zuckt.

Der primärschlüssel auf event_id und ort_id ist immer noch vorhanden.

Es geht nicht um den Primärschlüssel

Der Full Tabelle Scan findet sowie statt da leider keiner der gesätzten Indexe von Explain genutzt wurde.

es gibt ja auch keine in deiner Tabelle. Du musst natürlich die setzten, die gebraucht werden.

Struppi.

[Thorsten]

es gibt ja auch keine in deiner Tabelle. Du musst natürlich die setzten, die gebraucht werden.

Warum sollte ich eine Index sätzten das ist doch blödesinn.

Es wird keiner verwendet, weil die Rechnung in der WHERE bedingung gemacht wird.

MYSQL kann keine funktionale Indexe / Glaube ich zumindest.

Wenn ich einen Index sätzte geht garnichts mehr der Speicher ist überfüllt oder ich muss den Server neu starten da er nicht mehr Reagiert.

[Thorsten]

Habe die Abfrage auf das Minimum reduziert und bekomme das Ergebniss sehr schell geliefert.

SELECT
    plz
    FROM
    orte
    WHERE
    SQRT(('8.66659371820483' - laenge) * ('8.66659371820483'- laenge) + ('53.0688712237578' - breite) * ('53.0688712237578' - breite))  <= '2.24955008998'

Antwort in 0.0019 sek.

Und das bei einem Umkreis von 250km.

Der Join auf die Event Tabelle kostet dann richtig Zeit /Speicher.

Da bei 5 Events die Rechnung 40900 mal durchgeführt wird und das ist doch falsch. Er muss dieses nur 8180 mal durchführen.
Er brauch dann auch kein Full table scan auf die Tabelle Events machen.

Wie bau ich das jetzt in der gleiche Abfrage ein das er diese nicht mehr macht? Kann man das mit einem Subselect machen?#

Wenn ich das weiß, haben wir doch das Problem gelöst.

[Struppi]

es gibt ja auch keine in deiner Tabelle. Du musst natürlich die setzten, die gebraucht werden.

Warum sollte ich eine Index sätzten das ist doch blödesinn.

Weil deine Abfrage keinen benutzt, soweit ich deinen Explain output bewerten kann. z.b. wäre plz ein guter Kandiat.

Es wird keiner verwendet, weil die Rechnung in der WHERE bedingung gemacht wird.

Es geht ja nicht um die Rechnung, sondern wie schnell die DB an die Daten kommt.

MYSQL kann keine funktionale Indexe / Glaube ich zumindest.

Ist ja auch gar nicht nötig.

Wenn ich einen Index sätzte geht garnichts mehr der Speicher ist überfüllt oder ich muss den Server neu starten da er nicht mehr Reagiert.

Dann machst du was falsch.

Struppi.

[Kalle_B]

Moin,

warum so kompliziert gedacht? Was spricht dagegen, bei der Flächenbestimmung ein Quadrat zu wählen statt einem Kreis?

In meinem Veranstaltungskalender mache ich das so. Abhängig von den gewählten Kilometern berechne ich die Länge von ... bis ... und die Breite von ... bis ... die die gesuchten Orte haben dürfen. Und dann geht das Ruckizucki in der DB.

Es kann sowieso kein Mensch die Luftlinie nehmen, da liegen Berge und Flüsse ohne Brücken im Wege. Soll heißen, dass du trotz des großen Aufwandes keine Aussage über die wirkliche Entfernung machen kannst.

Lieben Gruß, Karl-Heinz

[FraFu]

Hallo!

Es kann sowieso kein Mensch die Luftlinie nehmen, da liegen Berge und Flüsse ohne Brücken im Wege. Soll heißen, dass du trotz des großen Aufwandes keine Aussage über die wirkliche Entfernung machen kannst.

Du kennst ja die Anforderung gar nicht. Vielleicht ist das was fürs Militär und er möchte ausrechnen wie weit sich Streubomben auswirken oder so.

mfg
  frafu

[Kalle_B]

Hallo!

Du kennst ja die Anforderung gar nicht. Vielleicht ist das was fürs Militär und er möchte ausrechnen wie weit sich Streubomben auswirken oder so.

Dann habe ich ihn hoffentlich kräftig verstreut, ähh, verwirrt.

[ChrisB]

Hi,

Du kennst ja die Anforderung gar nicht. Vielleicht ist das was fürs Militär und er möchte ausrechnen wie weit sich Streubomben auswirken oder so.

Den Abwurf einer Streubombe als "Event" zu bezeichnen, wuerde zu einigen Militaers ja vielleicht durchaus passen.

MfG ChrisB

[Sven Rautenberg]

Moin!

Ich starte nochmal ganz oben im Thread, in der Hoffnung auf Beachtung... :)

Meine Umkreissuche ist leider sehr langsam. Bei z.b. 5000 Datensätzen gibt es nur noch ein Timeout.

Das ist angesichts deines Monsterquerys nicht so verwunderlich.

Wenn es um Performance geht, dann ist alles hinderlich, was die Datenbank für die konkrete Abfrage immer wieder neu berechnen muß, obwohl man es problemlos schon selbst vorausberechnen könnte.

Da ist zunächst deine Monster-Entfernungsformel:
1. Die Formel wird sinnloserweise ZWEIMAL gerechnet, einmal für die Spalte selbst, und einmal für die WHERE-Bedingung.
2. Die Angaben der Spalten breite und laenge jeweils von Grad in Radiant umzurechnen ist unnötig, wenn diese Spalten den Wert direkt umgerechnet enthalten würden.
3. Multiplikation mit 6367,41 erscheint ebenfalls überflüssig - das ist nur dazu da, hinterher genaue Kilometerangaben zu erhalten.
4. Sofern du nicht planst, Events im Umkreis mehrerer tausend Kilometer auszugeben, dürfte der gute alte Pythagoras a²+b²=c² die Berechnung vermutlich deutlich beschleunigen. Da hast du in Deutschland zwar das "Problem", dass die Breiten nicht so lang sind, wie die Längen, aber das kriegt man mit einem Mittelwertfaktor recht gut kompensiert. Alternativ könntest du natürlich auch das Gauss-Krüger-Koordinatensystem verwenden, damit hättest du die Probleme dann gar nicht.

Dann dein JOIN zwischen PLZ des Events und PLZ der Ortstabelle: Den halte ich für überflüssig, weil auch datentechnisch zweifelhaft. Produziere Redundanz, speichere die für die Umkreissuche zu nutzenden Koordinaten direkt im Event-Datensatz. Das spart dir den JOIN. Denn es ist nicht anzunehmen, dass sich a) die Positionen von Postleitzahlen verändern, und b) wenn sich bei den PLZ was tut, dann aufgrund von Umstrukturierungen bei der Post, nicht weil wir Kontinentaldrift haben. Das Event, einmal geplant, hat seinen festen Ort, egal was die Post macht.

Wenn du Redundanz produzierst, indem du die Koordinaten bei der Speicherung des Events ermittelts und dazuspeicherst, hast du auch deutlich weniger Einschränkungen bei deiner PLZ-Tabelle. Die darf dann so ausführlich werden, wie du magst, und muß nicht auf Performance getrimmt werden - denn sie wird nur für das Auffinden der Koordinaten eines Events benutzt, das darf gerne eine Sekunde dauern.

Im Resultat hast du dann nur noch eine Suche in einer Tabelle. Die wäre aufgrund der WHERE-Bedingung für den Umkreis, die sich nicht indizierbar machen läßt, leider ein Full-Table-Scan - mit dem Anfügen von einschränkenden Vorbedingungen (Event muß in der Zukunft liegen, darf nicht gesperrt sein) könnte das aber im Ergebnis schon schnell genug sein.

Alternativ: Die Umkreis-Einschränkung aus dem WHERE rauslassen, stattdessen den Entfernungswert des Pythagoras in die abgefragten Daten übernehmen, danach sortieren lassen und mit LIMIT nur die dichtesten Events verwenden. Damit ergibt sich automatisch eine Einschränkung des Umkreises - jetzt nicht mehr nach Entfernung, sondern es werden einfach die X dichtesten Events ausgegeben.

Alternativ könnte man als Nachbrenner-Filter auch mit HAVING arbeiten, um die Events nach Entfernung auszusortieren.

- Sven Rautenberg

[Struppi]

Das ist angesichts deines Monsterquerys nicht so verwunderlich.

Die sieht jetzt so aus:
https://forum.selfhtml.org/?t=165133&m=1077188

Die Tabellenstruktur
https://forum.selfhtml.org/?t=165133&m=1077157

Ich glaub das sind ein paar Tipps schon drin. Meine bescheidenen Kenntnisse über DB Abfragen, lassen mich vermuten, dass ihm darüber hinaus noch indizes fehlen, um die Abfrage zu optimieren

Struppi.

[MudGuard]

Hi,

Meine Umkreissuche ist leider sehr langsam. Bei z.b. 5000 Datensätzen gibt es nur noch ein Timeout.
Das weitere Problem ich benötige eine Blätterfunktion für die Ergebnisse. Um die Anzahl der Datensätze zu ermitteln lasse ich fast die gleiche Abfrage 2 mal auf die DB los. Der Umkreis in dem gesucht wird liegt bei max 200km.

Noch eine Idee:

eine zusätzliche Tabelle, die die (einmalig berechneten) Entfernungen zwischen den einzelnen PLZ enthält.
Die Entfernung ändert sich ja nicht, also braucht sie nicht jedesmal neu berechnet werden.

Um die Tabelle nicht unnötig aufzublähen, könnte man nur die PLZ-Kombis aufnehmen, deren Entfernung kleiner als 200km ist.

Die geo Daten kommen von opengeodb. Und sind auf das minimum reduziert. Sprich 8180 plz in der Tabelle Orte.

Das wären dann 8180*8180 = 66912400 Datensätze maximal - bei Reduzierung auf die mit Entfernung kleiner 200 aber wesentlich weniger (z.B. müssen die PLZ aus Bayern/Baden-Württemberg nicht mit denen aus Mecklenburg-Vorpommern/Schleswig-Holstein/Hamburg/Bremen kombiniert werden, da die Entfernung sicher größer als 200km ist.

cu,
Andreas