Vorhandender Index wird trotz USE INDEX nicht verwendet

Hallo
Ich bin am verzweifeln mit MySQL-Indizes...

Die Tabelle:

CREATE TABLE `entscheide` (
`id` int(11) NOT NULL auto_increment,
`publikationsdatum` date default NULL,
`urteilsdatum` date NOT NULL,
`geschaeftsnummer` varchar(100) character set latin1 NOT NULL,
`sachgebiet` varchar(1000) character set latin1 default NULL,
`sachgebiet_id` int(11) NOT NULL default '0',
`prozessgegenstand` varchar(1000) character set latin1 default NULL,
`sprache` varchar(3) character set latin1 default NULL,
`publikationsstatus` int(11) default '0',
PRIMARY KEY (`id`),
KEY `geschaeftsnummer` (`geschaeftsnummer`),
KEY `sachgebiet_id` (`sachgebiet_id`),
KEY `urteilsdatum` (`urteilsdatum`),
KEY `sachgebiet` (`sachgebiet`(50))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
AUTO_INCREMENT=61195 ;

Wenn ich nach "sachgebiet" sortieren will, wird der "sachgebiet"-Index
nicht verwendet:
============
EXPLAIN SELECT * FROM entscheide ORDER BY sachgebiet DESC

id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE entscheide ALL NULL NULL NULL NULL 106582 Using
filesort

Wenn ich nach "sachgebiet_id" sortieren will, wird der
"sachgebiet_id"-Index korrekt verwendet:
============
EXPLAIN SELECT * FROM entscheide ORDER BY sachgebiet_id DESC
EXPLAIN SELECT * FROM entscheide USE INDEX (sachgebiet) ORDER BY
sachgebiet DESC (gleiches Resultat!!)

id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE entscheide index NULL sachgebiet_id 4 NULL 111775


Wie bringe ich dazu, dass der Index "sachgebiet" verwendet wird? Die
Query dauert so nämlich geschlagene 30 Sekunden, während die erste Query
nach der ID, wo der Index verwendet wird, den Bruchteil einer Sekunde
benötigt...
Die Tabelle hat ca. 35'000 Einträge...

Die Hinweise in der Doku, dass man den internen Optimierer mit "USE
INDEX" zur Verwendung zwingen sollte, habe ich ausprobiert (siehe oben)
- ohne Erfolg.. ich bin wirklich langsam am Verzweifeln..

Vielen Dank für jeden Hinweis!

Beste Grüsse,
Martin Müller

Re: Vorhandender Index wird trotz USE INDEX nicht verwendet

Martin Müller schrieb:
> ... ROWS: 106582 ...
>=20
> EXPLAIN SELECT * FROM ...
> Die
> Query dauert so nämlich geschlagene 30 Sekunden, während die erste =
Query=20
> nach der ID, wo der Index verwendet wird, den Bruchteil einer Sekunde=20
> benötigt...

Vielleicht hab ich ja was falsch verstanden, aber so wie ich das lese,=20
hast du kein Index-problem sondern ein Datenmengenproblem;
die wollen ja alle gelesen werden.

Bei Mysql hab ich soetwas bisher nie feststellen können, aber bei M$=20
Datenbanken half ein "where indexfeld>0" dem Optimierer auf die Sprünge=


Werner.

Re: Vorhandender Index wird trotz USE INDEX nicht verwendet

Am 07.03.2007 18:53 schrieb Martin Müller:
> Hallo
> Ich bin am verzweifeln mit MySQL-Indizes...
>
> Die Tabelle:
>
> CREATE TABLE `entscheide` (
> `id` int(11) NOT NULL auto_increment,
> `publikationsdatum` date default NULL,
> `urteilsdatum` date NOT NULL,
> `geschaeftsnummer` varchar(100) character set latin1 NOT NULL,
> `sachgebiet` varchar(1000) character set latin1 default NULL,
> `sachgebiet_id` int(11) NOT NULL default '0',
> `prozessgegenstand` varchar(1000) character set latin1 default NULL,
> `sprache` varchar(3) character set latin1 default NULL,
> `publikationsstatus` int(11) default '0',
> PRIMARY KEY (`id`),
> KEY `geschaeftsnummer` (`geschaeftsnummer`),
> KEY `sachgebiet_id` (`sachgebiet_id`),
> KEY `urteilsdatum` (`urteilsdatum`),
> KEY `sachgebiet` (`sachgebiet`(50))
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
> AUTO_INCREMENT=61195 ;
>

Hier definierst Du einen Schlüssel für die ersten 50 Zeichen von
"sachgebiet".

> Wenn ich nach "sachgebiet" sortieren will, wird der "sachgebiet"-Index
> nicht verwendet:
> ============
> EXPLAIN SELECT * FROM entscheide ORDER BY sachgebiet DESC
>

Hier möchtest Du nach dem gesamten Inhalt von Sachgebiet sortieren.
Ich könnte mir vorstellen, dass MySQL in so einem Fall einen Index gar
nicht sinnvoll benutzen kann. Denn wenn es zwei Zeilen findet, die
laut Index gleich sind (also in den ersten 50 Zeichen übereinstimmen),
muss es ja immer noch in die Tabelle gucken, um festzustellen, was
*nach* diesen ersten 50 Zeichen passiert. Folglich ist es simpler,
gleich die Tabelle direkt zu lesen und zu sortieren.

Gegenfrage: Warum hast Du überhaupt "sachgebiet" und "sachgebiet_id"
in derselben Tabelle? Das liest sich so, als ob "sachgebiet_id" das
numerische Äquivalent zu "sachgebiet" ist - dann solltest Du aber eine
zweite Tabelle verwenden, die den IDs die textuellen Beschreibungen
zuordnet.

Re: Vorhandender Index wird trotz USE INDEX nicht verwendet

Hallo

> nicht sinnvoll benutzen kann. Denn wenn es zwei Zeilen findet, die
> laut Index gleich sind (also in den ersten 50 Zeichen übereinstimmen),
> muss es ja immer noch in die Tabelle gucken, um festzustellen, was
> *nach* diesen ersten 50 Zeichen passiert. Folglich ist es simpler,
> gleich die Tabelle direkt zu lesen und zu sortieren.

das kann ich so zwar nicht nachvollziehen, vor allem nicht, weshalb er
den Index nicht berücksichtigt, wenn ich ihn mit USE INDEX dazu zwinge,
aber however..

Ich habe nun die Länge des Feldes von 1000 auf 200 verkürzt und einen
Index der Länge 200 angelegt und nun funktioniert es...

Womit die Frage, wie man eine Tabelle mit einer Spalte, die 1000 Zeichen
Länge hat vernünftig sortiert, noch immer offen wäre, aber die stellt
sich so glücklicherweise nicht..

Gruss,
Martin.

Re: Vorhandender Index wird trotz USE INDEX nicht verwendet

Martin Müller schrieb:
> Hallo
>
>> nicht sinnvoll benutzen kann. Denn wenn es zwei Zeilen findet, die
>> laut Index gleich sind (also in den ersten 50 Zeichen übereinstimmen),
>> muss es ja immer noch in die Tabelle gucken, um festzustellen, was
>> *nach* diesen ersten 50 Zeichen passiert. Folglich ist es simpler,
>> gleich die Tabelle direkt zu lesen und zu sortieren.
>
> das kann ich so zwar nicht nachvollziehen, vor allem nicht, weshalb er
> den Index nicht berücksichtigt, wenn ich ihn mit USE INDEX dazu zwinge,
> aber however..
>

*Was* kannst Du daran nicht nachvollziehen? Nimm an, MySQL benutze den
Index zum Sortieren. Nimm weiterhin der Einfachheit halber an, der Index
sei auf die ersten 6 Zeichen der Spalte gelegt, die Spalte sei aber 20
Zeichen lang.

MySQL läuft also nun brav durch den Index und findet an Position n den
Eintrag "Müller". An Position n+1 steht auch "Müller". An Position n+2
auch... Nun kann es nicht entscheiden, ob Müller/n vor oder nach
Müller/n+1 einzusortieren ist - dazu muss es erst die kompletten
Datensätze n und n+1 *lesen* (denn in n steht "Müller-Lüdenscheid" und
in n+1 "Müller" und in n+2 "Müller-Zymmermann"). Diese Situation kann
BELIEBIG OFT vorkommen - im Grunde genommen bei jedem Datensatz. Dann
ist es aber einfacher, gleich die ganze Tabelle zu lesen und nach der
gewünschten Spalte zu sortieren - die Tabellenzugriffe sind ohnehin
nötig, und so vermeidet MySQL die nutzlosen Indexzugriffe.

Jetzt nachvollziehbar (ich habe mal angenommen, dass Du mit diesem
komischen Wort "verständlich" meinst - wenn es was ganz anderes gewesen
sein sollte, was Du sagen wolltest, dann entschuldige bitte meine
langatmigen Erklärungen).

> Ich habe nun die Länge des Feldes von 1000 auf 200 verkürzt und einen
> Index der Länge 200 angelegt und nun funktioniert es...
>

Sach ich doch.

> Womit die Frage, wie man eine Tabelle mit einer Spalte, die 1000 Zeichen
> Länge hat vernünftig sortiert, noch immer offen wäre, aber die stellt
> sich so glücklicherweise nicht..

Na also. Und die Frage, wozu Du so ähnliche Daten in derselben Tabelle
verwahrst, ist auch noch offen.

Re: Vorhandender Index wird trotz USE INDEX nicht verwendet

On Thu, 08 Mar 2007 19:07:04 +0100 Martin Müller wrote:
> Womit die Frage, wie man eine Tabelle mit einer Spalte, die 1000
> Zeichen Länge hat vernünftig sortiert, noch immer offen wäre, aber die
> stellt sich so glücklicherweise nicht..

Ggf, wie schon vorgeschlagen, eine zweite Tabelle fuer den Klartext
machen und diese ueber die ID referenzieren. Dann musst Du nur noch die
(vermutlich) sehr viel kleinere Anzahl an Datensaetzen aus der zweiten
Tabelle sortieren, und das sollte massiv schneller gehen, mit Index,
oder ohne.

Servus,
Stefan

--
http://kontaktinser.at/ - die kostenlose Kontaktboerse fuer Oesterreich

Stefan, so prachtvoll wie das Weinen.
(Sloganizer)

Re: Vorhandender Index wird trotz USE INDEX nicht verwendet

Martin Müller wrote:
>
> CREATE TABLE `entscheide` (
....
> KEY `sachgebiet` (`sachgebiet`(50))
> )
>
> Wenn ich nach "sachgebiet" sortieren will, wird der "sachgebiet"-Index
> nicht verwendet:
> ============
> EXPLAIN SELECT * FROM entscheide ORDER BY sachgebiet DESC

Mal ganz davon abgesehen, daß dein Index ja nicht das ganze Feld
indiziert; ein SELECT * ohne WHERE muß sowieso die ganze Tabelle
lesen. Index hin oder her. Es wäre auch noch zu beweisen, daß ein
Index-Scan und das Lesen der Records in Index-Reihenfolge wirklich
schneller wäre, als ein Tabellen-Scan in Storage-Reihenfolge mit
anschließender Sortierung. Schon ab wenigen hundert Records dürfte
der Table-Scan mit Sortierung schneller sein.


XL

Re: Vorhandender Index wird trotz USE INDEX nicht verwendet

Hallo

Erstmal allen Postern vielen Dank für Eure Tips und - überhaupt nicht zu
langatmigen! - Ausführungen für einen Holzfäller-DBA..

> Na also. Und die Frage, wozu Du so ähnliche Daten in derselben Tabelle
> verwahrst, ist auch noch offen.

Da habt Ihr natürlich völlig recht.. das war eigentlich die reine
Faulheit meinerseits, da eine Javascript-Tabelle per ajax bloss den
Textinhalt der Spalte übermittelt, nach der ich sortieren möchte... aber
mit einer Normalform hat das dann wohl wirklich nichts mehr zu tun..
(ich sags ja: Holzfäller).. ;-)

Gruss,
Martin.