Wie optimiert man mysql date_format() für Geschwindigkeit im where clause?

Question

Ich versuche, eine relativ große MySQL (MyISAM) Tabelle mit 220.000 Zeilen zu optimieren. Die Tabelle selbst ist nicht so groß - etwa 23,5MB groß. Also, was ist das eigentliche Problem? - ich habe eine Abfrage wie diese:

SELECT * FROM Tabelle WHERE DATE_FORMAT(datum_feld, '%m%d') = '1128' LIMIT 10

Ich habe versucht, einen Index auf datum_feld zu setzen, aber EXPLAIN zeigt, dass der Index überhaupt nicht genutzt wurde ... ich denke, das ist nicht so seltsam wegen des DATE_FORMAT(). Also plane ich, eine weitere Spalte hinzuzufügen, die die Daten als '%m%d' enthält, und einen Index darauf zu setzen. Der einzige Grund, warum ich das nicht machen möchte, ist wegen der Daten-Duplizierung.
Übrigens, ich verwende datum_feld als Geburtsdatum-Feld und ich brauche das datum_feld immer als %Y-%m-%d oder einfach nur %m%d.

Haben Sie einen besseren Vorschlag, wie Sie die obige Abfrage optimieren können? Vielen Dank im Voraus!!!

Einige Informationen:

MySQL Version: 5.0.51b-log
Betriebssystem: Slackware 12.1
CPU: Pentium III (Coppermine) mit 996.783Mhz
RAM: 512MB DDR
HDD: 80GB SATA

P.S. Ich habe versucht, eine weitere Spalte hinzuzufügen, die die Daten als %m%d enthält. Die Ergebnisse sind sehr gut, aber ich mag diesen Ansatz immer noch nicht. Ich warte auf weitere Vorschläge!

Answer 1

Wenn Sie immer ein Wildcard-Jahr benötigen, wie bei Ihrer Abfrage, bin ich mir nicht sicher, ob MySQL einen Index auf einem Datum-/Datetime-Feld verwenden kann.

Wenn es sich nur um Daten handelt, können Sie jedoch eine time_dimension-Tabelle erstellen und diese im Voraus mit einem Kalender für die nächsten Jahre füllen. Ich habe eine gespeicherte Prozedur, um das zu tun, falls Sie eine benötigen.

create table time_dimension (
 dbdate date primary key,
 year int NOT NULL,
 month int  NOT NULL , 
 day int NOT NULL,
 KEY(year),
 KEY(month);
 KEY(day);
);

Sie würden Ihre große Datentabelle mit dieser relativ kleinen Tabelle verknüpfen und nach deren Feld filtern. z.B.

SELECT * FROM data_table d 
  inner join time_dimension t 
    on d.date_field=t.dbdate 
where t.day=28 and t.month=11 LIMIT 10

Dies nutzt das Filtern auf der kleinen time_dimension und die Verknüpfungen auf date_field = dbdate werden normalerweise Indexe verwenden.