Index mit Länge auf Textfeld

Hallo

Ich habe in einer MyISAM-Tabelle ein Feld mit der Information, ob der
Eintrag zur Publikation freigegeben ist. Die Werte können verschiedene
Formen haben:

- nicht publiziert: '0'
- publiziert: '1'
- in bestimmten Sprachen publiziert: die Sprachkürzel, getrennt durch
Begrenzungszeichen; z.B.: '#de@#en@#it@#fr@'

Die Abfrage lautet dann beispielsweise:

SELECT id FROM tabelle
WHERE art='seite'
AND (publizieren='1'
OR publizieren LIKE '%#fr@%'
OR publizieren LIKE '%#en@%')

Die Werte im 'publizieren'-Feld werden meistens '0' oder '1' sein, die
Applikation soll aber auch mit vielen Sprachen zurechtkommen, wass dann
zu längeren Strings in diesem Feld führen kann. Die Tabelle wird
typischerweise so zwischen 100 und 5000 Zeilen umfassen.

Nun möchte ich für dieses Feld einen Index anlegen. Das MySQL-Handbuch
empfiehlt diesen aus Performance-Gründen klein zu halten. Wenn ich nun
aber den Index z.B. auf 4 Zeichen begrenze, müssen ja Einträge mit
längeren 'publizieren'-Strings trotzdem gefunden werden. Ich bin also
etwas unsicher, wie hier zu verfahren ist...

- Index mit z.B. 4 Zeichen anlegen
- Index mit der vollen Länge anlegen (255 Zeichen)
- Feld 'publizieren' in feste Länge ändern (z.B. CHAR(100)), so dass die
gesamte Tabelle ein starres Format bekommt

Die Variante, eine separate Tabelle für die Einzelsprachen anzulegen,
habe ich schon ausprobiert - die Performance hat sich dabei nicht
merklich verändert, sogar eher verschlechtert; und die ganze Applikation
entsprechend umzuschreiben ist mir im laufenden Betrieb ein zu grosses
Risiko.

Ich bin dankbar für eine Einschätzung oder einen Hinweis auf leicht
verständliche Infos zu dem Thema!

--
Markus

Re: Index mit Länge auf Textfeld

Am 27.09.2006 16:42 schrieb Markus Ernst:
> Hallo
>
> Ich habe in einer MyISAM-Tabelle ein Feld mit der Information, ob der
> Eintrag zur Publikation freigegeben ist. Die Werte können verschiedene
> Formen haben:
>
> - nicht publiziert: '0'
> - publiziert: '1'
> - in bestimmten Sprachen publiziert: die Sprachkürzel, getrennt durch
> Begrenzungszeichen; z.B.: '#de@#en@#it@#fr@'
>

Schlechtes DB-Design. Benutze lieber eine zweite Tabelle, die für jede
Kombination aus Artikel und Sprache einen Eintrag enthält

Dein Modell erschwert Updates und

> Die Abfrage lautet dann beispielsweise:
>
> SELECT id FROM tabelle
> WHERE art='seite'
> AND (publizieren='1'
> OR publizieren LIKE '%#fr@%'
> OR publizieren LIKE '%#en@%')
>

Abfragen. Denn zum einen ist LIKE ohnehin nicht schnell, zum anderen
verhindert es in dieser Form die Nutzung eines Index auf "publizieren"

> Die Werte im 'publizieren'-Feld werden meistens '0' oder '1' sein, die
> Applikation soll aber auch mit vielen Sprachen zurechtkommen, wass dann
> zu längeren Strings in diesem Feld führen kann. Die Tabelle wird
> typischerweise so zwischen 100 und 5000 Zeilen umfassen.

Ok, ich nehme meinen Performance-Einwand zurück - bei diesen
Datenmengen spielt das keine Rolle. Trotzdem: Das Update von
"publizieren" ist eine Qual in diesem Modell.

>
> Nun möchte ich für dieses Feld einen Index anlegen. Das MySQL-Handbuch

Wozu? Für die LIKE-Abfragen kann ihn MySQL nicht benutzen.

> empfiehlt diesen aus Performance-Gründen klein zu halten. Wenn ich nun
> aber den Index z.B. auf 4 Zeichen begrenze, müssen ja Einträge mit
> längeren 'publizieren'-Strings trotzdem gefunden werden. Ich bin also
> etwas unsicher, wie hier zu verfahren ist...

Mit einem anderen Datenmodell. s.o.

> Die Variante, eine separate Tabelle für die Einzelsprachen anzulegen,
> habe ich schon ausprobiert - die Performance hat sich dabei nicht
> merklich verändert, sogar eher verschlechtert;

Unwahrscheinlich. Ich glaube nicht, dass bei dieser Datenmenge
Performanceunterschiede überhaupt auffallen.

> und die ganze Applikation
> entsprechend umzuschreiben ist mir im laufenden Betrieb ein zu grosses
> Risiko.

Du hättest sie vielleicht von vornherein ... na gut.

>
> Ich bin dankbar für eine Einschätzung oder einen Hinweis auf leicht
> verständliche Infos zu dem Thema!
>
Hier in der NG wird immer wieder auf Literatur zu Normalformen
verwiesen, Googel ist dein Freund.

Re: Index mit Länge auf Textfeld

Markus Ernst schrieb:

> - nicht publiziert: '0'
> - publiziert: '1'
> - in bestimmten Sprachen publiziert: die Sprachkürzel, getrennt durch
> Begrenzungszeichen; z.B.: '#de@#en@#it@#fr@'

Aua! Du speicherst unterschiedliche Daten(typen) in ein und demselben
Feld. Und hast normalisiert auch nicht.

> Die Abfrage lautet dann beispielsweise:
>
> SELECT id FROM tabelle
> WHERE art='seite'
> AND (publizieren='1'
> OR publizieren LIKE '%#fr@%'
> OR publizieren LIKE '%#en@%')

Aua! Hier kann kein Index benutzt werden.

> Die Werte im 'publizieren'-Feld werden meistens '0' oder '1' sein,...

Aua! Du speicherst größtenteils 0 und 1 in einem VARCHAR(255)-Feld.

> die
> Applikation soll aber auch mit vielen Sprachen zurechtkommen, wass dann
> zu längeren Strings in diesem Feld führen kann.

Aua! Irgendwann reicht dann VARCHAR(255) evtl. nicht mehr.

> Nun möchte ich für dieses Feld einen Index anlegen. Das MySQL-Handbuch
> empfiehlt diesen aus Performance-Gründen klein zu halten.

Und sagt auch, dass bei LIKE '%irgendwas' kein Index benutzt werden kann:



| Die folgenden SELECT-Statements benutzen keine Indexe:
| mysql> select * from tabelle where schluessel_spalte LIKE "%Patrick%";

Also brauchst du dir nicht weiter Gedanken über das Ganze machen,
solange du die DB nicht normalisierst.

gruss, heli

Re: Index mit Länge auf Textfeld

Christian Kirsch schrieb:
> Am 27.09.2006 16:42 schrieb Markus Ernst:
>> Hallo
>>
>> Ich habe in einer MyISAM-Tabelle ein Feld mit der Information, ob der
>> Eintrag zur Publikation freigegeben ist. Die Werte können verschiedene
>> Formen haben:
>>
>> - nicht publiziert: '0'
>> - publiziert: '1'
>> - in bestimmten Sprachen publiziert: die Sprachkürzel, getrennt durch
>> Begrenzungszeichen; z.B.: '#de@#en@#it@#fr@'
>>
>
> Schlechtes DB-Design. Benutze lieber eine zweite Tabelle, die für jede
> Kombination aus Artikel und Sprache einen Eintrag enthält
>
> Dein Modell erschwert Updates und
>
>> Die Abfrage lautet dann beispielsweise:
>>
>> SELECT id FROM tabelle
>> WHERE art='seite'
>> AND (publizieren='1'
>> OR publizieren LIKE '%#fr@%'
>> OR publizieren LIKE '%#en@%')
>>
>
> Abfragen. Denn zum einen ist LIKE ohnehin nicht schnell, zum anderen
> verhindert es in dieser Form die Nutzung eines Index auf "publizieren"
>
>> Die Werte im 'publizieren'-Feld werden meistens '0' oder '1' sein, die
>> Applikation soll aber auch mit vielen Sprachen zurechtkommen, wass dann
>> zu längeren Strings in diesem Feld führen kann. Die Tabelle wird
>> typischerweise so zwischen 100 und 5000 Zeilen umfassen.
>
> Ok, ich nehme meinen Performance-Einwand zurück - bei diesen
> Datenmengen spielt das keine Rolle. Trotzdem: Das Update von
> "publizieren" ist eine Qual in diesem Modell.
>
>> Nun möchte ich für dieses Feld einen Index anlegen. Das MySQL-Handbuch
>
> Wozu? Für die LIKE-Abfragen kann ihn MySQL nicht benutzen.
>
>> empfiehlt diesen aus Performance-Gründen klein zu halten. Wenn ich nun
>> aber den Index z.B. auf 4 Zeichen begrenze, müssen ja Einträge mit
>> längeren 'publizieren'-Strings trotzdem gefunden werden. Ich bin also
>> etwas unsicher, wie hier zu verfahren ist...
>
> Mit einem anderen Datenmodell. s.o.
>
>> Die Variante, eine separate Tabelle für die Einzelsprachen anzulegen,
>> habe ich schon ausprobiert - die Performance hat sich dabei nicht
>> merklich verändert, sogar eher verschlechtert;
>
> Unwahrscheinlich. Ich glaube nicht, dass bei dieser Datenmenge
> Performanceunterschiede überhaupt auffallen.
>
>> und die ganze Applikation
>> entsprechend umzuschreiben ist mir im laufenden Betrieb ein zu grosses
>> Risiko.
>
> Du hättest sie vielleicht von vornherein ... na gut.
>
>> Ich bin dankbar für eine Einschätzung oder einen Hinweis auf leicht
>> verständliche Infos zu dem Thema!
>>
> Hier in der NG wird immer wieder auf Literatur zu Normalformen
> verwiesen, Googel ist dein Freund.

Danke für die schnelle und klare Antwort! Dann werd ich mal in den Apfel
beissen und das Ding ein weiteres mal umschreiben...

--
Markus

Re: Index mit Länge auf Textfeld

Helmut Chang schrieb:
> Markus Ernst schrieb:
>
>> - nicht publiziert: '0'
>> - publiziert: '1'
>> - in bestimmten Sprachen publiziert: die Sprachkürzel, getrennt durch
>> Begrenzungszeichen; z.B.: '#de@#en@#it@#fr@'
>
> Aua! Du speicherst unterschiedliche Daten(typen) in ein und demselben
> Feld. Und hast normalisiert auch nicht.
>
>> Die Abfrage lautet dann beispielsweise:
>>
>> SELECT id FROM tabelle
>> WHERE art='seite'
>> AND (publizieren='1'
>> OR publizieren LIKE '%#fr@%'
>> OR publizieren LIKE '%#en@%')
>
> Aua! Hier kann kein Index benutzt werden.
>
>> Die Werte im 'publizieren'-Feld werden meistens '0' oder '1' sein,...
>
> Aua! Du speicherst größtenteils 0 und 1 in einem VARCHAR(255)-Feld.
>
>> die Applikation soll aber auch mit vielen Sprachen zurechtkommen, wass
>> dann zu längeren Strings in diesem Feld führen kann.
>
> Aua! Irgendwann reicht dann VARCHAR(255) evtl. nicht mehr.
>
>> Nun möchte ich für dieses Feld einen Index anlegen. Das MySQL-Handbuch
>> empfiehlt diesen aus Performance-Gründen klein zu halten.
>
> Und sagt auch, dass bei LIKE '%irgendwas' kein Index benutzt werden kann:
>
>
>
> | Die folgenden SELECT-Statements benutzen keine Indexe:
> | mysql> select * from tabelle where schluessel_spalte LIKE "%Patrick%";
>
> Also brauchst du dir nicht weiter Gedanken über das Ganze machen,
> solange du die DB nicht normalisierst.
>
> gruss, heli

Danke für die schnelle und klare Antwort! Dann werd ich mal in den Apfel
beissen und das Ding ein weiteres mal umschreiben...

--
Markus

Re: Index mit Längeauf Textfeld

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)