Schrift
Start · Board · Anwendungen/Programme/Skripte in Perl · Datenbanken und Verzeichnisdienste
[thread]3463[/thread]

Effiziente SELECTs in MySQL: Insertion order determinieren?



<< |< 1 2 3 >| >> 24 Einträge, 3 Seiten
ptk
 2004-06-08 16:02
#32214 #32214
User since
2003-11-28
3645 Artikel
ModeratorIn
[default_avatar]
Ich habe hier eine grosse MySQL-Tabelle mit ueber 13*10^6 Datensaetzen, welche nach Datum sortiert eingefuegt wurden. Die Id wird per auto_increment erzeugt. Ich moechte nun zu einem Datum den naechsten Datensatz herausfinden. Mit diesem SELECT geht das ganz gut und schnell:
Code: (dl )
1
2
3
4
5
6
7
mysql> select accesslog_id from accesslog where accessdate >= "2004-01-01 00:00:00" limit 0,1;
+--------------+
| accesslog_id |
+--------------+
|      3127136 |
+--------------+
1 row in set (0.06 sec)

Allerdings verlasse ich mich darauf, dass die interne Sortierung mit der accesslog_id-Sortierung uebereinstimmt. Nach ein bisschen Googlen sieht es so aus, als ob man sich nicht 100%ig darauf verlassen kann. Die richtige Methode wuerde so aussehen:
Code: (dl )
1
2
3
4
5
6
7
mysql> select min(accesslog_id) from accesslog where accessdate >= "2004-01-01 00:00:00";
+-------------------+
| min(accesslog_id) |
+-------------------+
|           3127136 |
+-------------------+
1 row in set (2 min 26.32 sec)

(oder alternativ mit ORDER BY). Das ist aber ziemlich langsam. Weiss jemand Vorschlaege, Alternativen ...?
foobar
 2004-06-08 16:08
#32215 #32215
User since
2003-08-04
69 Artikel
BenutzerIn
[default_avatar]
Guckst du hier: http://dev.mysql.com/doc/mysql/en/MySQL_Optimisation.html
The three chief virtues of a programmer are: Laziness, Impatience and Hubris
[Larry Wall]
Gast Gast
 2004-06-08 16:29
#32216 #32216
Wenn Du ein Perl-Script einsetzen würdest könntest Du alle mit dem Datum übereinstimmenden 'accesslog_id' in @foo einlesen und mit shift sort @foo die richtig ID auslesen.
Thorium
 2004-06-08 16:36
#32217 #32217
User since
2003-08-04
232 Artikel
BenutzerIn
[Homepage] [default_avatar]
Da Datumsabfragen in WHERE-Statements extrem langsam sind versuch, das Datum in eine Nummer umzuwandeln und diese dann in ein indexiertes Attribut zu schreiben.
dann machst du dein Select auf das Nummernfeld und nicht das datumsfeld.
Wie man das in MySQL tut hab ich keinen Plan :)\n\n

<!--EDIT|Thorium|1086698275-->
Per|li|nist der; -en, -en <zu ↑...ist>: a) Anhänger, Vertreter der radikalen Perlinisten die Perl als die einzig wahre Sprache ansehen; b) Mitglied einer perlinistischen Community.
Oesi50
 2004-06-08 16:54
#32218 #32218
User since
2004-05-15
33 Artikel
BenutzerIn
[default_avatar]
hallo ptk,

hast Du es schon mal mit einem Index für das Datumsfeld versucht?

Eine andere Variante wäre, per Cron-Job(nachts) die Tabelle nach dem Datumsfeld zu sortieren.

> mysql> select accesslog_id from accesslog where accessdate >= "2004-01-01 00:00:00" limit 0,1;

> 1 row in set (0.06 sec)

Wie lange dauert es denn, wenn Du nach einem Datum suchst, was ganz am Ende der Tabelle steht?

@Dieter

Das würde ich nicht machen, da könnte Dir der Speicher platzen.
:-(

@Thorium

da bietet sich der Unix-Timestamp als Integer an.
Eventuell auch mit Cron als Extraspalte.
Oder man verwendet TIMESTAMP als Datentyp, der wird intern als UNSIGNED INT gespeichert.(spart auch noch 4 * 13*10**6 Byte)\n\n

<!--EDIT|Oesi50|1086700129-->
Gast Gast
 2004-06-08 17:22
#32219 #32219
[quote=Oesi50,08.06.2004, 14:54]@Dieter
Das würde ich nicht machen, da könnte Dir der Speicher platzen.
[/quote]
Nööö ...
ich lese sowas immer Step-By-Step aus (max. 1000 Datensätze per Durchlauf) und damit wird selbst der kleinste Speicher fertig :)
ptk
 2004-06-08 17:34
#32220 #32220
User since
2003-11-28
3645 Artikel
ModeratorIn
[default_avatar]
[quote=Oesi50,08.06.2004, 14:54]hallo ptk,

hast Du es schon mal mit einem Index für das Datumsfeld versucht?
[/quote]Ja. describe tabellenname sagt:
Code: (dl )
1
2
3
4
+--------------+------------------+------+-----+---------------------+----------------+
| Field        | Type             | Null | Key | Default             | Extra          |
+--------------+------------------+------+-----+---------------------+----------------+
| accessdate   | datetime         |      | MUL | 0000-00-00 00:00:00 |                |

Quote
Eine andere Variante wäre, per Cron-Job(nachts) die Tabelle nach dem Datumsfeld zu sortieren.
Ja, die Anwendung wird nachts angeschmissen, aber ich habe auch Anwendungen mit einer aehnlichen Problematik, die interaktiv laufen sollten.
Quote
> mysql> select accesslog_id from accesslog where accessdate >= "2004-01-01 00:00:00" limit 0,1;

> 1 row in set (0.06 sec)

Wie lange dauert es denn, wenn Du nach einem Datum suchst, was ganz am Ende der Tabelle steht?

Wie man erwarten wuerde: je naeher am Ende, desto akzeptabler die Antwortzeiten:
Code: (dl )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select min(accesslog_id) from accesslog where accessdate >= "2004-06-01 00:00:00";
+-------------------+
| min(accesslog_id) |
+-------------------+
|          12700047 |
+-------------------+
1 row in set (14.72 sec)

mysql> select min(accesslog_id) from accesslog where accessdate >= "2004-06-07 00:00:00";
+-------------------+
| min(accesslog_id) |
+-------------------+
|          13419582 |
+-------------------+
1 row in set (1.41 sec)

Quote
@Thorium

da bietet sich der Unix-Timestamp als Integer an.
Eventuell auch mit Cron als Extraspalte.
Oder man verwendet TIMESTAMP als Datentyp, der wird intern als UNSIGNED INT gespeichert.(spart auch noch 4 * 13*10**6 Byte)

Wenn ich die Dokumentation richtig verstehe, wird TIMESTAMP automatisch auf die Zeit der letzten Aenderung gesetzt? Dann kann ich TIMESTAMP nicht verwenden.

Das Problem wird auch nicht die evtl. groessere Datenstruktur sein, sondern die grosse Anzahl zu sortierender Daten, im schlimmsten Fall eben knapp 13*10**6 (und es werden taeglich mehr!). Ironischerweise kommt man, wenn man eine reine Textdatei per binaerer Suche durchsuchen wuerde, wesentlich schneller an das Ergebnis heran...
ptk
 2004-06-08 18:06
#32221 #32221
User since
2003-11-28
3645 Artikel
ModeratorIn
[default_avatar]
[quote=Dieter,08.06.2004, 15:22][quote=Oesi50,08.06.2004, 14:54]@Dieter
Das würde ich nicht machen, da könnte Dir der Speicher platzen.
[/quote]
Nööö ...
ich lese sowas immer Step-By-Step aus (max. 1000 Datensätze per Durchlauf) und damit wird selbst der kleinste Speicher fertig :)[/quote]
Wuerdest du LIMIT verwenden? Ich versuche es gerade mit
Code: (dl )
1
2
3
4
5
6
7
8
9
10
$i=0;
while(1) {
    warn $i;
    $sql = qq{SELECT accesslog_id FROM accesslog WHERE accessdate >= "2004-01-01 00:00:00" LIMIT $i,1000};
    $sth = $g_dbh->prepare($sql);
    $sth->execute;
    $x = $sth->fetchall_arrayref;
    last if not @$x;
    $i+=1000;
}'

Am Anfang werden die Debugpoints noch schnell ausgedruckt, aber je groesser $i wird, desto langsamer wird das SELECT. Und wie es aussieht, wird dieser Code laenger brauchen als die Variante mit MIN(...).
Oesi50
 2004-06-08 18:19
#32222 #32222
User since
2004-05-15
33 Artikel
BenutzerIn
[default_avatar]
@dieter
wie geht das '@foo einlesen und mit shift sort @foo die richtig ID auslesen' step by step?

@ptk

timestamp wird nur dann automatisch gesetzt, wenn
1. es die erste timestampspalte ist
und
2. bei INSERT, UPDATE, REPLACE usw. diese Spalte nicht explizit angesprochen wird.\n\n

<!--EDIT|Oesi50|1086704489-->
Thorium
 2004-06-08 18:24
#32223 #32223
User since
2003-08-04
232 Artikel
BenutzerIn
[Homepage] [default_avatar]
Wie gesagt.
Leg ein weiteres attribut mit dem Namen "int_time" mit Unsigned INT an und speichere die Dati da rein. Dann kannst du einen Index drüberlegen und bist viel, viel schneller.
Per|li|nist der; -en, -en <zu ↑...ist>: a) Anhänger, Vertreter der radikalen Perlinisten die Perl als die einzig wahre Sprache ansehen; b) Mitglied einer perlinistischen Community.
<< |< 1 2 3 >| >> 24 Einträge, 3 Seiten



View all threads created 2004-06-08 16:02.