gescheiter Index auf datetime-Spalte

Hallo,

in einer Tabelle mit >3Mio DS wird nach dayfmonth(datum), month(datum)
und year(datum) selektiert und gruppiert.

Die Spalte 'datum' ist vom type datetime und hat einen index. Dieser
wird nicht genutzt, die queries sind dementsprechend langsam.

Wie kann die datum-Spalte gescheit indieziert werden?
Mir schweben sep. Indexe auf Jahr, Monat und Tag des Datums vor.
Allerdings habe ich nicht nicht gefunden, wie man das am besten anstellt.


Jens

Re: gescheiter Index auf datetime-Spalte

Hallo,

Jens Puruckherr wrote:
>
> in einer Tabelle mit >3Mio DS wird nach dayfmonth(datum), month(datum)
> und year(datum) selektiert und gruppiert.
>
> Die Spalte 'datum' ist vom type datetime und hat einen index. Dieser
> wird nicht genutzt, die queries sind dementsprechend langsam.
>
> Wie kann die datum-Spalte gescheit indieziert werden?
> Mir schweben sep. Indexe auf Jahr, Monat und Tag des Datums vor.
> Allerdings habe ich nicht nicht gefunden, wie man das am besten anstellt.

Die Antwort wäre: du brauchst einen funktionalen Index.
Aber leider unterstützt das Mysql nicht so wirklich.


Bye

--
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
(Ferenc Mantfeld)

Re: gescheiter Index auf datetime-Spalte

Am 27.06.2007 09:03 schrieb Jens Puruckherr:
> Hallo,
>
> in einer Tabelle mit >3Mio DS wird nach dayfmonth(datum), month(datum)
> und year(datum) selektiert und gruppiert.
>
> Die Spalte 'datum' ist vom type datetime und hat einen index. Dieser
> wird nicht genutzt, die queries sind dementsprechend langsam.
>

MySQL kennt keine funktionalen Indizes.

> Wie kann die datum-Spalte gescheit indieziert werden?
> Mir schweben sep. Indexe auf Jahr, Monat und Tag des Datums vor.
> Allerdings habe ich nicht nicht gefunden, wie man das am besten anstellt.

Was daran liegt, dass es so nicht geht. PostgreSQL z.B. kann das :-(
Eine (scheußliche) Möglichkeit wäre, das Datum zu zerlegen und in drei
Spalten zu speichern (tag, monat, jahr).

--
Christian

Re: gescheiter Index auf datetime-Spalte

Christian Kirsch schrieb:

> MySQL kennt keine funktionalen Indizes.

Ja, das habe ich mittlerweile auch erkennen müssen.

> Eine (scheußliche) Möglichkeit wäre, das Datum zu zerlegen und in drei
> Spalten zu speichern (tag, monat, jahr).

Das scheint wohl der einzigste Weg zu sein...
Ich rüste das gerade nach. Graussig ... und noch 2 Applikaitonen anfassen...

Jens

Re: gescheiter Index auf datetime-Spalte

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: gescheiter Index auf datetime-Spalte

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: gescheiter Index auf datetime-Spalte

Andreas Kretschmer wrote:
> begin Andreas Kretschmer schrieb:
>> begin Jens Puruckherr schrieb:
>>> Hallo,
>>>
>>> in einer Tabelle mit >3Mio DS wird nach dayfmonth(datum), month(datum)
>>> und year(datum) selektiert und gruppiert.
>>>
>>> Die Spalte 'datum' ist vom type datetime und hat einen index. Dieser
>>> wird nicht genutzt, die queries sind dementsprechend langsam.
>>
>> PostgreSQL kann funktionale Indexe, damit wäre es kein Problem. MySQL
>
> Wobei ich der Ordnung halber dazusagen muß, daß es in diesem speziellen
> Fall ein Problem gibt: wenn der timestamp MIT Zeitzone ist, sind
> funktionen wie extract(dow from timestamptz) nicht IMMUTABLE und daher
> nicht für Indexe geeignet. Oder aber man gibt explizit eine Zeitzone an.
> Bräuchte man also den Index dann für verschiedene Zeitzonen, müßte man
> für jede Zeitzone die Indexe anlegen...

Hmm, stimmt allerdings.


> Wenn, wie vorgeschlagen, Jens nun für dayofmonth(), month() und year()
> unterschiedliche Spalten anlegt und Abfragen mit unterschiedlicher
> Zeitzone kommen, dürfte es hier auch zu Fehlern kommen.

Stellt sich die Frage, warum das überhaupt datetime ist, wenn anscheinend
nur ein Datum daraus genutzt wird. date wäre dann hier angebrachter und
dann hat man wiederum keine Probleme mit dem funktionalen Index.

Aber ok, für alles andere müsste man die Applikation kennen.


Bye

--
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
(Ferenc Mantfeld)

Re: gescheiter Index auf datetime-Spalte

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: gescheiter Index auf datetime-Spalte

Andreas Scherbaum schrieb:

> Stellt sich die Frage, warum das überhaupt datetime ist, wenn anscheinend
> nur ein Datum daraus genutzt wird. date wäre dann hier angebrachter und
> dann hat man wiederum keine Probleme mit dem funktionalen Index.

Stimmt datetime, ist Quark das hab ich schon korrigiert.
Funktionale indexe habe ich dennoch nicht.

> Aber ok, für alles andere müsste man die Applikation kennen.

Es werden täglich diverse Aktionen geloggt.
Die Abfrage kann für einen selektierten Tag, Monat oder Jahr erfolgen.

Das mit den Zeitzonen kann ignoriert werden, es ist eine interne Anwendung.


Jens

Re: gescheiter Index auf datetime-Spalte

Am 27.06.2007 16:40 schrieb Jens Puruckherr:
> Andreas Scherbaum schrieb:
>
>> Stellt sich die Frage, warum das überhaupt datetime ist, wenn anscheinend
>> nur ein Datum daraus genutzt wird. date wäre dann hier angebrachter und
>> dann hat man wiederum keine Probleme mit dem funktionalen Index.
>
> Stimmt datetime, ist Quark das hab ich schon korrigiert.
> Funktionale indexe habe ich dennoch nicht.
>
>> Aber ok, für alles andere müsste man die Applikation kennen.
>
> Es werden täglich diverse Aktionen geloggt.
> Die Abfrage kann für einen selektierten Tag, Monat oder Jahr erfolgen.
>
> Das mit den Zeitzonen kann ignoriert werden, es ist eine interne Anwendung.

Mittelfristig (ich nehme mal an, Du willst jetzt nicht unbedingt die
DB wechseln ;-) könnte Dir in dem Fall vielleicht Partitionierung
helfen: Die Daten einfach nach Jahr oder Jahr/Monat partitionieren,
das könnte den Zugriff beschleunigen.

--
Christian

Re: gescheiter Index auf datetime-Spalte

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: gescheiter Index auf datetime-Spalte

Jens Puruckherr wrote:
> Christian Kirsch schrieb:
>
>> MySQL kennt keine funktionalen Indizes.
>
> Ja, das habe ich mittlerweile auch erkennen müssen.
>
>> Eine (scheußliche) Möglichkeit wäre, das Datum zu zerlegen und in drei
>> Spalten zu speichern (tag, monat, jahr).
>
> Das scheint wohl der einzigste Weg zu sein...
> Ich rüste das gerade nach. Graussig ...

Trigger können dabei helfen, das Grauen unter der Oberfläche zu
verstecken.

> und noch 2 Applikaitonen anfassen...

OK, das bleibt dir nicht erspart.


XL

Re: gescheiter Index auf datetime-Spalte

Andreas Kretschmer schrieb:

>> Mittelfristig (ich nehme mal an, Du willst jetzt nicht unbedingt die
>> DB wechseln ;-) könnte Dir in dem Fall vielleicht Partitionierung
>> helfen: Die Daten einfach nach Jahr oder Jahr/Monat partitionieren,
>
> Aber auch nur mittelfristig, oder? Nach meinem Wissen gilt
> table-Partitionierung in 5.1 noch nicht als 'ready for production', oder
> liege ich da flasch?

Hmm klingt interessant. Damit habe ich mich noch gar nicht befasst.
Für eine interne Anwendung könnte ich mit dem Beta-Status vielleicht
leben ... danke ... ich schaue es mit mal an.

Jens

Re: gescheiter Index auf datetime-Spalte

"Jens Puruckherr" schrieb
> Andreas Scherbaum schrieb:
> Es werden täglich diverse Aktionen geloggt.
> Die Abfrage kann für einen selektierten Tag, Monat oder Jahr
> erfolgen.
>
> Das mit den Zeitzonen kann ignoriert werden, es ist eine interne
> Anwendung.

Dann könnte meiner Meinung nach ein vorangestelltes
where datum >= $anfang and datum <= $ende
die zu bearbeitende Datenmenge über den Index eingrenzen.

mysql> explain select count( * ) from bla where month( d ) = 7 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bla
type: index
possible_keys: NULL
key: d
key_len: 4
ref: NULL
rows: 127845
Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> explain select count( * ) from bla where d > now() and month(
d ) = 7 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bla
type: range
possible_keys: d
key: d
key_len: 4
ref: NULL
rows: 2
Extra: Using where; Using index
1 row in set (0.00 sec)

Gruß
Andreas

Re: gescheiter Index auf datetime-Spalte

On Jun 27, 9:03 am, Jens Puruckherr wrote:
> Hallo,
>
> in einer Tabelle mit >3Mio DS wird nach dayfmonth(datum), month(datum)
> und year(datum) selektiert und gruppiert.
>
> Die Spalte 'datum' ist vom type datetime und hat einen index. Dieser
> wird nicht genutzt, die queries sind dementsprechend langsam.
>
> Wie kann die datum-Spalte gescheit indieziert werden?
> Mir schweben sep. Indexe auf Jahr, Monat und Tag des Datums vor.
> Allerdings habe ich nicht nicht gefunden, wie man das am besten anstellt.
>
> Jens

Wenn du nach Jahr (zB 2005) selektieren willst, dann verwende folgende
Syntax:

SELECT ... FROM ... WHERE datum >=3D "2005-01-01" AND datum <=3D
"2005-31-12"

In diesem Fall wird der Index benutzt.
Wenn du stattdessen schreibst: ... WHERE YEAR(datum)=3D2005
wird er nicht benutzt.

Geht in ähnlicher Weise auch für MONTH. Leicht nachzuprüfen mit
EXPLAIN.

Re: gescheiter Index auf datetime-Spalte

markus.baumgartner@liwest.at schrieb:

> Wenn du nach Jahr (zB 2005) selektieren willst, dann verwende folgende
> Syntax:
>
> SELECT ... FROM ... WHERE datum >= "2005-01-01" AND datum <=
> "2005-31-12"
>
> In diesem Fall wird der Index benutzt.
> Wenn du stattdessen schreibst: ... WHERE YEAR(datum)=2005
> wird er nicht benutzt.

Ja, habe mittlerweile die Queries umgestellt danke.
Lustigerweise werden immer noch teilweise full table scans bevorzugt.
Naja, die Abfrageschwindigkeit hat sich zumindest bei den kritischen
Queries erhöht.

Jens