From: Marcel Kurz
Date: April 19 2004 9:00am
Subject: RE: Index auf datetime Feld?
List-Archive: http://lists.mysql.com/mysql-de/493
Message-Id: <002801c425ec$cdcab520$6200a8c0@skthinkpad>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

hi georg,
>=20
> und wie sieht die Query aus?=20
SELECT count(page) AS hits, sum(size) AS traffic, month(datetime)=20
AS monat, year(datetime) AS jahr FROM affiliate_statistik_neu USE=20
INDEX(datetime) WHERE datetime >'2003-01-01 00:00:00' AND=20
datetime < '2003-12-31 23:59:59'  GROUP BY monat, jahr ORDER=20
by datetime DESC;
wobei ich auch schon mit "BETWEEN" gearbeitet hab macht aber=20
kein unterschied.
was mir noch aufgefallen ist wenn der Zeitraum nur 1 Monat ist dann=20
nimmt er den INDEX laut EXPLAIN bei gr=F6sseren Zeitr=E4umen nimmt=20
er Ihn nicht

>Wie sieht das Create Table=20
CREATE TABLE affiliate_statistik_neu (
  page varchar(200) NOT NULL default '',
  ip varchar(15) NOT NULL default '',
  aid varchar(12) NOT NULL default '',
  UID varchar(20) NOT NULL default '',
  size int(10) NOT NULL default '0',
  datetime datetime NOT NULL default '0000-00-00 00:00:00',
  KEY page (page),
  KEY aid (aid),
  KEY size (size),
  KEY datetime (datetime)
) TYPE=3DMyISAM;

>under der  Explain output aus?=20
mysql> explain SELECT count(page) AS hits, sum(size) AS traffic,=20
month(datetime) AS monat, year(datetime) AS jahr FROM=20
affiliate_statistik_neu USE INDEX(datetime) WHERE datetime >
'2003-01-01 00:00:00' AND datetime < '2003-12-31 23:59:59' =20
GROUP BY monat, jahr ORDER by datetime DESC;

EXPLAIN-AUSGABE:
table =3D affiliate_statistik_neu
type =3D ALL
possible_keys =3D datetime
key =3D NULL
key_len =3D NULL
ref =3D NULL
rows =3D 3141347
Extra =3D where used; Using temporary; Using filesort

wenn ich aber nur einen Monat ausw=E4hle dann nimmer er den
Index:
mysql> explain SELECT count(page) AS hits, sum(size) AS traffic,
 month(datetime) AS monat, year(datetime) AS jahr FROM=20
affiliate_statistik_neu USE INDEX(datetime) WHERE datetime >
'2004-01-01 00:00:00' AND datetime < '2004-01-31 23:59:59' =20
GROUP BY monat, jahr ORDER by datetime DESC;

EXPLAIN-AUSGABE:
table =3D affiliate_statistik_neu
type =3D range
possible_keys =3D datetime
key =3D datetime
key_len =3D 8
ref =3D NULL
rows =3D 450848
Extra =3D where used; Using temporary; Using filesort

WARUM ?

>Welche Version benutzt du?
MYSQL Version: 3.23.57


>=20
> Wenn Du eine Antwort auf Dein Problem erwartest, solltest Du=20
> dieses auch=20
> f=FCr den ML-Leser verst=E4ndlich beschreiben.
hoffe das war jetzt verst=E4ndlicher...

beste gr=FC=DFe
marcel