IOException.de

Im ersten Teil haben wir zwei einfache Lösungsstrategien entwickelt, wie sich Umkreissuchen der Form “Suche die nächstgelegenen 15 Orte heraus!” durch einen Subquery vorfiltern lassen: Die grundsätzliche Idee ist, über eine Näherungsformel eine Selektion von 15*faktor Datensätzen zu finden, aus denen dann die echt-nächsten Orte bestimmt werden können.

Als Näherung bietet sich so die Pythagorasformel an, die in diesem Beitrag in der simplen Form (Breiten- und Längengerade gehen gleich gewichtet ein) und der verbesserten (mit Gewichtung von 1:1,57 in Deutschland) untersucht wird. Dabei gilt grundsätzlich die Frage zu klären, wie groß in beiden Fällen wohl der faktor gewählt werden muss, damit in den meisten Fällen in der Näherungsselektion auch alle 15 echt-nächsten Orte enthalten sind. Daneben interessiert natürlich, wie groß der Performancegewinn beider Strategien ausfällt.

Dazu habe ich ein kleines Testscript in node.js geschrieben, das eine ganze Reihe Testfälle generiert und durchspielt. Insgesamt wurden für 200 zufällige Orte in Deutschland oder grenznahen Regionen jeweils die 1, 5, 10, 20, 50, 100, 150 und 200 echt-nächsten Orte gesucht. Das Script ermittelt dabei für die beiden Näherungsformeln, wieviele Datensätze durch diese geliefert werden müssen, damit die gesuchten Orte auch wirklich enthalten sind. Ein mögliches Ergebnis wäre also etwa, dass wir über die einfache Pythagorasformel für den Alexanderplatz Berlin mindestens 25 Datensätze erhalten müssten, damit keiner der 15 echt-nächsten fehlte.
Als Datenbasis dient dabei die Vereinsdatenbank von meinem nodeKO-Projekt, die etwa 2600 echte Vereine enthält und somit auch recht praxisnah für diesen Versuch ist.

Die entstandenen 1600 Messdaten und Auswertungen können bei GoogleDocs eingesehen werden. Das Testscript arbeitet so, dass nie zwei Queries gleichzeitig abgesetzt werden, die Zeitmessungen sind also ohne Last.

Als erstes soll es uns aber gar nicht um die Performance gehen, sondern viel mehr um die Ermittlung eines geeigneten Faktors für die beiden Näherungen. Im Limits-Sheet sind dabei die Messwerte zusammengefasst: Will man z.B. die 20 nächsten Datensätze ermitteln, so sollte man im Mittel über die einfache Pythagorasformel mindestens 35 Datensätze selektieren, bei der verbesserten Formel reichen dagegen 21 (!). Interessant sind auch die Maxima: Nach spätestens 97 Datensätzen waren in allen Testfällen alle 20 gesuchten bei der einfachen Formel enthalten, die verbesserte kam da mit einem maximalen Limit von 30 aus. Im Mittel über alle Tests ergibt sich, dass, wer die Pythagorasformel anwendet, den faktor wenigstens bei 1.75 ansetzen sollte. Bei Hinzunahme des Verhältnisses lon/lat reicht dagegen ein Faktor von 1.05. Wichtig hierbei ist natürlich, dass stets aufgerundet wird.

Der Mittelwert ist aber nur bedingt aussagefähig. Es bleibt immer noch die Frage: Wie häufig ist mein Ergebnis wohl trotzdem falsch, wenn die obigen Werte eingesetzt werden?
Hierfür habe ich die Versuchsergebnisse anders betrachtet: Mit welcher Wahrscheinlichkeit sind alle gesuchten Datensätze wohl enthalten, wenn bei der einfachen Pythagorasformel der Faktor 2 (wir sind mal großzügig…) genutzt wird? Für mich vor allem interessant, weil ich diesen Faktor bei meinem Projekt eingesetzt hatte. Und tatsächlich: Es zeigt sich, dass der Faktor 2 in gerade mal vier von fünf Fällen die gesuchten Ergebnisse liefert. Wer die Pythagoras-Näherung einsetzt, sollte diesen Faktor also doch lieber ein wenig großzügiger bemessen.

Anders ist das bei der verbesserten Pythagorasformel: Mit Faktor 2 hätte sie in allen 200 Testfällen die gesuchten Orte auch mit selektiert. Selbst der Faktor 1.5 ist noch mehr als ausreichend und lieferte in mehr als 99 Prozent die gesuchten Datensätze. Doch wie weit kann man noch runter gehen? Bei einem Faktor von 1.3 sind immer noch sehr annehmbare 98 Prozent der Anfragen korrekt, bei 1.1 fast 97 Prozent.

Und wozu das ganze? – Natürlich um Rechenzeit zu sparen. Die ermittelten Laufzeiten sind leider nur bedingt aussagefähig. Sie geben jeweils an, wieviel Zeit erforderlich war für eben jenen Query, der zuerst alle gesuchten Datensätze enthielt. In Ausnahmefällen kann das also schon mal deutlich mehr sein, als man erhalten würde, wenn man einfach einen fixen Faktor einsetzte. Und dennoch sprechen bereits diese Zahlen für sich: Durch die verbesserte Pythagorasformel erhält man einen Performancegewinn von fast 50 Prozent, die Anwendung lohnt also.

In meinem Projekt äußerte sich der Gewinn sogar noch deutlicher. Dort hatte ich direkt auf meine Tabelle gejoint und gleichzeitig die 15 nächsten Orte gesucht. Resultat: Alle Datensätze wurden erst gejoint und anschließend reduziert. Natürlich ist ein erster möglicher Schritt die Tabelle per Subquery sofort mit der exakten Entfernungsformel zu reduzieren, das bringt auch schon einen deutlichen Gewinn. Noch schneller geht es aber mit den beiden gezeigten Näherungen.
Insbesondere ist es durch die obigen Erkenntnisse möglich, einen Teil der Last auf den Client umzulegen. So wäre ein geeignetes Vorgehen etwa, dem Client stets limit*faktor genäherte Datensätze auf seine Anfrage zurück zu geben, was für den Server sehr viel schneller als die exakte Rechnung ginge. Der Client müsste jetzt selbst den letzten Schritt gehen und für die erhaltenen Orte die exakte Entfernungsberechnung durchführen, um die echt-nächsten limit Orte zu bestimmen.

Wie auch einige Kollegen dieses Blogs nahm ich vor wenigen Wochen am node.js knockout teil, einem 48-Stunden-Hackathon. Ich versuchte mich mit meinem Projekt an einem Mashup, das als zentrales Element eine Umkreissuche beherbergen sollte.

Diesen Anwendungsfall gibt es sehr häufig: In einer SQL-Datenbank sind Geo-Koordinaten von Orten gespeichert und es soll nun ausgehend von einer beliebigen Position eine bestimmte Anzahl der nächstgelegenen gespeicherten Orte gefunden werden. Da ich mit meinem Projekt eine Datenbank für deutsche Vereine realisieren wollte, wäre etwa eine konkrete Fragestellung gewesen: “Hey, ich bin gerade beim Alexanderplatz Berlin. Such mir doch mal die 15 nächstgelegenen Vereine heraus!” Die Ergebnismenge ist hierbei nur in der Anzahl, nicht aber der Entfernung eingeschränkt. Genau umgekehrt wäre es zum Beispiel, wenn nach allen Vereinen im Umkreis von 50km gesucht würde, wo auch mehr oder weniger als 15 Resultate gefunden werden könnten. Dieser Beitrag wird nur die erste Frage behandeln. Der Begriff “Umkreissuche” sollte also in diesem Sinne verstanden werden.

Nehmen wir an, unsere Koordinaten sind in der Tabelle coords in den Spalten lat und lon gespeichert. Die 15 nächstgelegenen Orte zu einem gegebenen Punkt (c.lat, c.lon) zu finden ist gar nicht so schwer, die SQL-Abfrage findet man im Internet:

'SELECT *, (acos(sin('+c.lat+'*Pi()/180)*sin(lat*Pi()/180)+cos('+c.lat+'*Pi()/180)*cos(lat*Pi()/180)*cos(('+c.lon+'-lon)*Pi()/180))) as distance FROM coords ORDER BY distance ASC LIMIT 15'

Die Resultate, die diese Abfrage liefert, sind die 15 nächstgelegenen Orte zum Punkt c. Problem bei diesem Vorgehen ist, dass die Abfrage recht umfangreich und somit auch zeitintensiv ist, wodurch sie etwa im Falle großer Datenmengen oder zusätzlicher Joins zum Flaschenhals wird. So lief die Abfrage bei meinem Projekt mit nur 2600 gespeicherten Orten sehr schleppend und drohte das Scheitern der gesamten Applikation.

Abhilfe sollte folgende Überlegung schaffen: Ist es schneller, nicht direkt von der Tabelle coords abzufragen, sondern eine Auswahl überhaupt in Frage kommender Vereine vorher abzuschätzen? Ziel wäre es also, einen Query in folgender Form zu nutzen:

'SELECT *, (Formel von oben) AS distance FROM (SELECT * FROM coords ORDER BY abschaetzung LIMIT faktor*15) AS shortened ORDER BY distance LIMIT 15'

Es werden also über eine Näherungsformel die 15*faktor besten Datensätze vorausgewählt, aus denen dann die 15 echt nächsten gesucht werden. Die simpelste Idee einer solchen Näherungsformel wäre schlicht die Abschätzung mittels Pythagoras, bei der wir mal außer Acht lassen, dass unsere Erde ja eigentlich ein räumliches Gebilde ist. Die Abschätzungsselektion sieht dann so aus:

'SELECT * FROM coords ORDER BY SQRT(POW(('+c.lat+'-lat), 2)+POW(('+c.lon+'-lon), 2)) LIMIT '+15*faktor

Entscheidend ist natürlich: Wie groß muss wohl faktor gewählt werden, damit auch wirklich alle der 15 echt nächsten Punkte in der Vorauswahl enthalten sind? Während des 48-Stunden-Hackathons hatte ich keine Zeit, dies näher zu ergründen und probierte es mit dem Faktor 2, der nach meinem Empfinden kaum false-positives lieferte. Ob dem wirklich so ist, wird der zweite Teil dieses Beitrags zeigen.

Die Betrachtung, Längen- und Breitengrade gleich gewichtet in die Pythagoras-Formel einfließen zu lassen, ist der naivste Weg. Die false-positive-Rate ließe sich verbessern, wenn man das Verhältnis Breitengrad zu Längengrad genauer wüsste. Diese lassen sich aber nicht einfach in Kilometer umrechnen – einen konstanten Umrechnungsfaktor kann es aufgrund der Erdkrümmung nicht geben. Da wir aber ohnehin nur eine Näherungsformel zur Vorauswahl suchen, ist das auch gar nicht so schlimm, Näherungswerte reichen für eine Verbesserung der eben genutzten Pythagorasformel vollkommen.

Über den Google Maps Distance Calculator lassen sich Näherungen für Deutschland recht schnell ermitteln: Ein Breitengrad (lat) entspricht bei uns rund 70 Kilometer, Längengrade (lon) erhöhen sich alle 110 Kilometer um eins. Damit lässt sich die obige Pythagoras-Formel verbessern:

'SELECT * FROM coords ORDER BY SQRT(POW(('+c.lat+'-lat)/70, 2)+POW(('+c.lon+'-lon)/110, 2)) ASC LIMIT '+15*faktor2

Der Preis einer solchen Verbesserung der Näherungsformel ist freilich, dass die Abfrage wieder rechenintensiver wird. Interessant dürfte also sein, ob sich das ganze dann überhaupt noch lohnt. Eigentlich ist es auch gar nicht nötig, die Breiten- und Längengrade auf Kilometer runter zu rechnen, da ja eigentlich nur das Verhältnis als Gewichtung eingehen soll. Statt beide zu dividieren würde also auch ein (lonDiff)*1.57 genügen – wieder eine Operation gespart!

Noch immer wissen wir jedoch nicht, wie groß der jeweilige Faktor bei den beiden Abschätzungen sein sollte, sodass man eine möglichst geringe Fehlerquote erhält (trivialerweise sollte er natürlich wenigstens 1 sein). Wieviel weniger darf der Faktor wohl angesetzt werden, wenn man die Pythagorasformel wie oben beschrieben verbessert? Ein Stochastiker würde das wohl in dieser Form fragen: Wie groß muss der Faktor wenigstens sein, dass nur in maximal 5 Prozent aller Abfragen nicht alle 15 nächsten Vereine enthalten sind?

Im nächsten Teil wird versucht, diese Fragen durch eine Vielzahl an Tests an den Vereinsdaten zu beantworten, und geklärt werden, ob sich die Vorauswahl denn wirklich in einer Steigerung der Perfomance ausdrückt. Ohne zu viel vorweg nehmen zu wollen: Bei meinem node.js knockout Projekt betrug der Performancegewinn über 80 Prozent. Da wusste ich aber auch noch nicht, dass die einfache Pythagorasabschätzung mit Faktor 2 in jedem fünften Fall nicht alle der 15 nächstgelegenen Vereine lieferte…

Viele Datenbanken mit Personendaten enthalten Geburtsdaten. So ist eine typische Abfrage das Ermitteln von Personen, die in den nächsten Tagen Geburtstag haben oder in den vergangenen Tagen hatten. Eine solche Abfrage gestaltet sich aus verschiedenen Gründen komplizierter. Einerseits müssen alle Eigenheiten bei der Kalkulation mit Daten beachtet werden (z.B. Schaltjahre), andererseits möchte man ungern die kompletten Daten aus der Datenbank ziehen und dann erst dann mit einer weiteren Programmiersprache verarbeiten und berechnen, sondern direkt die Schnelligkeit und Funktionalität der Datenbank nutzen. Aus diesem Grund möchte ich hier meinen Versuch vorstellen, dieses Problem mit MySQL zu lösen. Ziel war es, eine Ergebnis zu bekommen, das auflistet, welche Personen innerhalb eines definierbaren Zeitraums Geburtstag hatten oder noch Geburtstag haben und wie alt die Person jeweils geworden ist oder wird. Das Feld ‘birthday’ soll hierbei ein Datenfeld vom Typ Date sein. Hier werden alle Geburtstage zwischen vergangener Woche und den kommenden zwei Wochen chronologisch aufgelistet:

SELECT
`name`,
`birthday`,
@nextBirthday := `birthday` + INTERVAL (YEAR(CURRENT_DATE) - YEAR(`birthday`) + IF(DATE_FORMAT(CURRENT_DATE, "%m%d") > DATE_FORMAT(`birthday`, "%m%d"), 1, 0)) YEAR,
@prevBirthday := @next - INTERVAL 1 YEAR,
@daysNext := DATEDIFF(@nextBirthday, CURRENT_DATE),
@daysPrev := DATEDIFF(@prevBirthday, CURRENT_DATE),
IF(ABS(@daysPrev) < @daysNext,@daysPrev +0 ,@daysNext+0) AS `days`,
IF(ABS(@daysPrev) < @daysNext,YEAR( @prevBirthday ) - YEAR( `birthday` ),YEAR( @nextBirthday ) - YEAR( `birthday` )) AS `age`
FROM
`table`
HAVING
`days` BETWEEN -7 AND 14
ORDER BY
`days` ASC

Die Grundidee ist relativ einfach. Es wird mithilfe von MySQL-Datumsfunktionen berechnet, wann die Person das nächste Mal Geburtstag hat und wann sie das letzte mal hatte. Nun wird geprüft, welches Ereignis aktueller ist und entsprechend die Werte ‘days’ und ‘age’ belegt. Mithilfe der HAVING-Klausel lässt sich außerdem die Ergebnismenge eingeschränken auf einen bestimmten Zeitraum.

Originalartikel: Abfrage von auftretenden Jahrestagen (wie Geburtstagen) mit MySQL