[MySQL] Sortierung

[Hral]

Hallo allerseits,

ich habe eine Tabelle, welche ungefähr so aussieht (nach Datum aufsteigend sortiert):

id      datum
1       2011-06-28
2       2011-06-30
3       2011-07-05
4       2011-07-08
5       2011-08-11
6       2011-09-14
7       2011-09-15
8       2011-09-16
9       2011-09-17

Ich möchte aus dieser Tabelle 5 Einträge auswählen - bestimmt werden diese durch 1 Datum und die 4 vorherigen Daten. Die Ausgabe soll aber aufsteigend sortiert sein.

Absteigende Sortierung ist einfach

  
SELECT * FROM tabelle  
   WHERE datum <= '2011-09-15'  
   ORDER BY datum DESC  
   LIMIT 0,5  

Gibt die richtigen Zeilen nur in der falschen Reihenfolge:

id      datum
7       2011-09-15
6       2011-09-14
5       2011-08-11
3       2011-07-05
2       2011-06-30

Lass ich direkt aufsteigend sortieren, bekomme ich jedoch die falschen Zeilen

  
SELECT * FROM tabelle  
   WHERE datum <= '2011-09-15'  
   ORDER BY datum ASC  
   LIMIT 0,5  

id      datum
1       2011-06-28
2       2011-06-30
3       2011-07-05
4       2011-07-08
5       2011-08-11

Was ja auch logisch ist.

Der einzige Weg, den ich bisher gefunden hab gefällt mir nicht. Gibt es einen besseren?

  
SELECT * FROM  
(   SELECT * FROM tabelle i  
       WHERE i.datum <= '2011-09-15'  
       ORDER BY i.datum DESC  
       LIMIT 0,5  
) a  
ORDER BY a.datum ASC  

Regards

Hral
MySQL-Version: 5.0.7

[fastix®]

Moin!

Und, was hindert Dich daran in dem die Datenbank abfragenden Skript die Ausgabe herumzudrehen?

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[fastix®]

Moin!

Absteigende Sortierung ist einfach

SELECT * FROM tabelle
   WHERE datum <= '2011-09-15'
   ORDER BY datum DESC
   LIMIT 0,5

>   
> Gibt die richtigen Zeilen nur in der falschen Reihenfolge:  
  
Falls es in SQL sein muss:  
  
Das Handbuch, Kapitel "[Unterabfragen in der FROM-Klausel](http://dev.mysql.com/doc/refman/5.0/en/from-clause-subqueries.html)" hilft Dir.  
  
~~~sql
  
SELECT `id`, `datum`  
FROM (  
      SELECT `id`, `datum` FROM `tabelle`  
         WHERE `datum` <= '2011-09-15'  
         ORDER BY `datum` DESC  
         LIMIT 0,5  
     )  
ORDER BY `datum`;  

(oder so ähnlich ... ungetestet)

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[Hral]

SELECT id, datum
FROM (
      SELECT id, datum FROM tabelle
         WHERE datum <= '2011-09-15'
         ORDER BY datum DESC
         LIMIT 0,5
     )
ORDER BY datum;

  
Ich mag mich täuschen, aber entspricht das nicht meiner letzten Lösung? Ich hatte gehofft ich hätte was übersehen, denn die 2fache Sortierung mißfällt mir.  
  

> Und, was hindert Dich daran in dem die Datenbank abfragenden Skript die Ausgabe herumzudrehen?  
  
Ich schreibe an einer Erweiterung eines bestehenden CMS auf PHP-Basis.  
  
Die bestehenden Funktionen erlauben ein zeilenweises Auslesen des SQL-Ergebnisses und direktes Übergeben an eine Template-Variable (Array). Die Template-Engine erlaubt aber kein Ausgeben des Arrays in umgekehrter Reihenfolge. Daher möchte ich das Ergebnis in der korrekten Reihenfolge aus der Datenbank bekommen. Andernfalls müsste ich erst ein Array mit dem Ergebnis füllen und dieses dann rückwärts durchlaufend ans Template übergeben, und das gefällt mir noch weniger.  
  
Danke  
  
Hral

[suit]

Ich mag mich täuschen, aber entspricht das nicht meiner letzten Lösung?

Sehe ich auch so, ja.

Ich hatte gehofft ich hätte was übersehen, denn die 2fache Sortierung mißfällt mir.

Wieso? Glaubst du, das DBMS könnte damit nicht umgehen?

Wenn du kein anderes Sortierkriterium hast, muss das herhalten, was du hast.

Die Template-Engine erlaubt aber kein Ausgeben des Arrays in umgekehrter Reihenfolge.

Gute Engine[sic!]

[Hral]

Vielen Dank,

diese Infos helfen mir weiter! (ernst gemeint - auch wenn Sarkasmus durchklingen mag)

Die Idee mit datediff finde ich sehr spannend, dürfte Performance technisch aber auch nicht besser sein, als die 2te Sortierung. Oder täusche ich mich da?

Zu der Erweiterung der Funktionen des CMS ist zusagen, das mein "Projekt" bisher keine Änderung am Core des CMS notwendig macht(e). Ich befürchte, das eine Erweiterung der Core-Funktionen (wenn auch nur innerhalb meines Projektes) häufiger ein Update meines Codes erzwingt, wenn das CMS ein Update erhält, als wenn ich den Core nicht verändere.

Außerdem kommt hinzu, das ich mich noch nicht mit der Entwicklung/Erweiterung von Template-Engines beschäftigt habe und die mangelnde Dokumentation, des von mir verwendeten CMS, senkt meine Motivation, es anhand dieses CMS zu tun, doch enorm.

Nochmal vielen Dank

Hral

[fastix®]

Moin!

Die Idee mit datediff finde ich sehr spannend, dürfte Performance technisch aber auch nicht besser sein, als die 2te Sortierung. Oder täusche ich mich da?

Das hängt davon ab wie oft MySQL die Subtraktion durchführen muss. Allerdings bezweifle ich, dass bei 5 Datensätzen ein Unterschied besteht, den Du messen kannst. Ich empfehle zur Messung des Zeitunterschiedes die Abfrage so 10.000 bis 100.000 mal zu wiederholen.

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[dedlfix]

Hi!

Allerdings bezweifle ich, dass bei 5 Datensätzen ein Unterschied besteht, den Du messen kannst. Ich empfehle zur Messung des Zeitunterschiedes die Abfrage so 10.000 bis 100.000 mal zu wiederholen.

Du wirst den Unterschied auch mit weit größeren Anzahlen nicht messen können, denn bei Wiederholungen kommt das Ergebnis aus dem Query-Cache.

Lo!

[fastix®]

Moin!

Du wirst den Unterschied auch mit weit größeren Anzahlen nicht messen können, denn bei Wiederholungen kommt das Ergebnis aus dem Query-Cache.

Stimmt auch wieder. Kann man aber zumindest auf einen Testsystem abschalten.

http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html sagt:

"To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default."

http://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html sagt:

"To reduce overhead significantly, also start the server with query_cache_type=0 if you will not be using the query cache. "

mysql> SET GLOBAL query_cache_size = 0;
schaltet  den Cache ab

oder:

mysql> SET SESSION query_cache_type = OFF;
schaltet den Cache für den aktuellen Client ab (eventuell nur aktuelle Verbindung)

oder :

mysql> SELECT SQL_NO_CACHE ...
weist mySQL an, den Cache für die Abfrage zu ignorieren.

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[dedlfix]

Hi!

"To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default."

Na sowas, dass man ihn erstmal aktivieren muss, hätte ich jetzt nicht gedacht.

Lo!

[dedlfix]

Hi!

"To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default."
Na sowas, dass man ihn erstmal aktivieren muss, hätte ich jetzt nicht gedacht.

Andererseits muss man auch noch beachten, dass der Vorgang der Client-Server-Kommunikation auch nicht ohne ist und durch Laufzeitschwankungen das Messergebnis, besonders bei so kleinen Unterschieden, ziemlich verfälschen kann. Die Funktion BENCHMARK() kann nützlich sein oder das Ausführen der Schleife in einer Stored Procedure. Jedoch lohnt sich der ganze Aufwand bei 5 Datensätzen überhaupt nicht. Wichtig ist nur, wenn 5 Datensätze aus sehr vielen gewählt werden sollen, dass dann ein Index auf dem Auswahlkriterium angelegt ist, dessen Verwendung man mit EXPLAIN an einer realistischen Datenmenge überprüfen sollten. So ein Index spart auch die Sortierung, weil die Daten im Index ja bereits sortiert liegen.

Lo!

[fastix®]

Moin!

Naja. Ich denke wir machen den letzten Typo raus und geben als Lösungen:

SELECT `id`, `datum`, datediff('9999-12-31',`datum`) as `temp` FROM `tabelle`  
           WHERE `datum` <= '2011-09-15'  
           ORDER BY `temp`  
           LIMIT 0,5;

oder:

SELECT `id`, `datum`  
FROM (  
      SELECT `id`, `datum` FROM `tabelle`  
         WHERE `datum` <= '2011-09-15'  
         ORDER BY `datum` DESC  
         LIMIT 0,5  
     )  
ORDER BY `datum`;

an, die ähnlich performant sein dürften.

Noch spannender wäre eine Spalte rev_datum einzufügen, diese mit einem Index zu versehen, einmalig

UPDATE tabelleSETrev_datum=datediff('9999-12-31',datum);

auszuführen (und das bei jedem Eintrag/Update für die Zeile zu wiederholen) und dann

SELECT `id`, `datum` FROM `tabelle`  
           WHERE `datum` <= '2011-09-15'  
           ORDER BY `rev_datum`  
           LIMIT 0,5;

in Auftrag zu geben. Lohnt aber nur dann wenn die Anzahl und Ergebnismenge der SELECTS hoch ist und die der INSERTS/UPDATES niedrig. Bricht aber mit den Regeln der Normalisierung.

Vermutlich benötigt der Aufwand zu Lösung 3 mehr Arbeits- und Rechenzeit als jemals durch sämtliche Abfragen verursacht wird ...

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[fastix®]

Moin!

Ich mag mich täuschen, aber entspricht das nicht meiner letzten Lösung?

Im Ergebnis NICHT.

Ich hatte gehofft ich hätte was übersehen, denn die 2fache Sortierung mißfällt mir.

Du willst 5 Daten aus der Tabelle. Ein bestimmtes und vier kleinere. Limit kann aber keine negativen Zahlen.

die 2fache Sortierung mißfällt mir.

Was glaubst Du wie lange MySQL braucht um eine im Arbeitsspeicher stehenden Tabelle mit 5 Zeilen
zu sortieren?

Alternative: Füge der Tabelle eine Spalte hinzu, welche eine Differenz zu einem Datum nach dem Ende des Universums (oder halt nur in 1000 Jahren) beinhaltet und sortiere nach dieser ...

SELECT `id`, `datum`, SELECT('5000-01-01'-`datum` as `temp`) FROM `tabelle`  
          WHERE `datum` <= '2011-09-15'  
          ORDER BY `temp`  
          LIMIT 0,5;

(oder so ähnlich ...)

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[suit]

Alternative: Füge der Tabelle eine Spalte hinzu, welche eine Differenz zu einem Datum nach dem Ende des Universums (oder halt nur in 1000 Jahren) beinhaltet und sortiere nach dieser ...

SELECT('5000-01-01'-datum as temp)

Das Jahr 5000 ist vermutlich weder das Ende des Universums noch "in 1000 Jahren" - Math-FAIL :D

[fastix®]

Moin!

Das Jahr 5000 ist vermutlich weder das Ende des Universums noch "in 1000 Jahren" - Math-FAIL :D

Du warst auch schon besser. Früher hässtest Du auf datediff() hingewiesen. Heute musste ich das selbst machen.

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[fastix®]

Moin!

SELECT `id`, `datum`, SELECT([link:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff@title=datediff]('5000-01-01',`datum`) as `temp`) FROM `tabelle`  
           WHERE `datum` <= '2011-09-15'  
           ORDER BY `temp`  
           LIMIT 0,5;  

Kommt wohl besser ...

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[fastix®]

Moin!

Was gibt es denn als Prämie?

Ich meine, ich sitze hier als "Amateur" und helfe den "Profis" scheinbar unlösbare Probleme mit zwei oder keiner zusätzlichen Zeilen zu lösen ...

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix

[fastix®]

Moin!

Ich schreibe an einer Erweiterung eines bestehenden CMS auf PHP-Basis.

Die bestehenden Funktionen erlauben ein zeilenweises Auslesen des SQL-Ergebnisses und direktes Übergeben an eine Template-Variable (Array).

Hübsch- Und warum soll sich das nicht erweitern lassen? Wenn Du das CMS erweiterst, dann kannst Du auch den Funktionssatz erweitern. Wo ist das Problem?

MFFG (Mit freundlich- friedfertigem Grinsen)

fastix