mySQL: Nicht-Verwendung eines Index

[Michael Schröpl]

Hallo Leute,

irgendwie vermute ich akute Betriebsblindheit bei mir,
aber auch unzureichende Vertrautheit mit den Möglich-
keiten von mySQL.

Ich habe eine Tabelle:

mysql> show create table t_nref;

CREATE TABLE t\_nref
  (d\_id   varchar(50) binary NOT NULL default '',
   d\_ref  varchar(30) binary NOT NULL default '',
   d\_time int(11)            NOT NULL default '0',
   UNIQUE KEY i\_nref\_id\_ref   (d\_id,d\_ref),
          KEY i\_nref\_ref\_time (d\_ref,d\_time),
          KEY i\_nref\_time     (d\_time)
   ) TYPE=MyISAM

Nun möchte ich in einer WHERE-Klausel nach d_ref
filtern und dabei über den Index i_nref_ref_time zugreifen (das sollte m. E. gehen, weil d_ref das
erste Feld des kombinierten Indexes ist).

Das macht mySQL aber nicht:

mysql> explain select * from t_nref where d_ref = 123;
+--------+------+-----------------+------+---------+------+--------+------------+
| table  | type | possible_keys   | key  | key_len | ref  | rows   | Extra      |
+--------+------+-----------------+------+---------+------+--------+------------+
| t_nref | ALL  | i_nref_ref_time | NULL |    NULL | NULL | 134235 | where used |
+--------+------+-----------------+------+---------+------+--------+------------+
1 row in set (0.09 sec)

"explain" zeigt mir, daß mySQL einen full table scan
für diese Abfrage durchführen würde - obwohl es sich
durchaus bewußt ist, daß "i_nref_ref_time" ein seman-
tisch geeigneter Schlüssel wäre.
Der Index projeziert auch recht ordentlich (im Mittel
würden 25 Treffer pro Schlüsselwert heraus kommen).

Versuchsweise habe ich auch mal einen separaten Index
nur über t_nref angelegt.
"explain" zeigt dann diesen Index ebenfalls in der ent-
sprechenden Spalte an, verwendet ihn aber auch nicht.

Alles andere, was ich mit mySQL und Indexen bisher
gemacht habe, funktioniert tadellos (auch in exakt
dieser Datenbank auf dieser Maschine).

Ich bin völlig ratlos.
Was könnte ich tun, um mehr Informationen zu bekommen?

Jeder Hinweis, der zur Entwirrung meiner Gedanken
beiträgt, wäre mir äußerst recht.

Viele Grüße
      Michael

[Michael Schröpl]

... ups, das war nicht ganz perfekt:

Versuchsweise habe ich auch mal einen separaten Index
nur über t_nref angelegt.

das sollte "nur über d_ref" heißen - also die Spalte,
die ich mit WHERE abfragen will.

Viele Grüße
      Michael

[Cheatah]

Hi,

mal ganz kurz nebenbei:

CREATE TABLE t\_nref

Als Tabellenname ist das nicht wirklich sprechend... ;-)

Ich bin völlig ratlos.

Ich auch; zumal auch in der Doku nur eine überschaubare Menge an Fällen aufgezählt wird, in denen ein Index nicht verwendet wird, und diese Fälle allesamt nicht zuzutreffen scheinen. Das einzige, was mir auffällt (was ich aber von MySQL nicht als Problem erwarten würde) ist, dass Du die Spalte als VARCHAR angelegt hast[1] und als NUMBER abfragst. Kannst Du das mal testweise vereinheitlichen?

Cheatah

[1] Wenn es sich, wie mir scheint, um eine Referenz handelt, sollte es eh ein NUMBER-Typ sein ;-)

[Michael Schröpl]

Hallo Cheatah,

CREATE TABLE t\_nref
Als Tabellenname ist das nicht wirklich sprechend... ;-)

Alle Tabellen in diesem System heißen "t_*", alle
Spalten heißen "d_*".

Das einzige, was mir auffällt (was ich aber von MySQL
nicht als Problem erwarten würde) ist, dass Du die
Spalte als VARCHAR angelegt hast[1] und als NUMBER
abfragst.

Oh - ich verstehe! Darauf hatte ich nicht geachtet.

Und es ist in der Tat die Lösung des Problems:

mysql> describe select * from t_nref where d_ref = 123;
+--------+------+-----------------+------+---------+------+--------+------------+
| table  | type | possible_keys   | key  | key_len | ref  | rows   | Extra      |
+--------+------+-----------------+------+---------+------+--------+------------+
| t_nref | ALL  | i_nref_ref_time | NULL |    NULL | NULL | 135838 | where used |
+--------+------+-----------------+------+---------+------+--------+------------+
1 row in set (0.01 sec)

mysql> describe select * from t_nref where d_ref = "123";
+--------+------+-----------------+-----------------+---------+-------+------+------------+
| table  | type | possible_keys   | key             | key_len | ref   | rows | Extra      |
+--------+------+-----------------+-----------------+---------+-------+------+------------+
| t_nref | ref  | i_nref_ref_time | i_nref_ref_time |      30 | const |    1 | where used |
+--------+------+-----------------+-----------------+---------+-------+------+------------+
1 row in set (0.00 sec)

mySQL konvertiert zwar implizit den Datentyp, schaltet
dabei aber den Indexzugriff ab - darauf muß man erst
mal kommen ...

[1] Wenn es sich, wie mir scheint, um eine Referenz
handelt, sollte es eh ein NUMBER-Typ sein ;-)

Wahr - aber die Tabelle stammt nicht von mir und wird
von einem closed-source-Produkt einer Schwesterfirma
angelegt und mit Daten versorgt ... ich schreibe nur
die Suchmaschine über diese Daten.

Vielen Dank - das hat mir sehr geholfen!
       Michael

[Cheatah]

Hi,

CREATE TABLE t\_nref
Als Tabellenname ist das nicht wirklich sprechend... ;-)
Alle Tabellen in diesem System heißen "t_*", alle
Spalten heißen "d_*".

und "nref" bedeutet...? :-)

Versteh mich bitte nicht falsch, es ist natürlich euer Bier, wie ihr die Nomenklatur aufstellt. Denk aber auch daran, dass mal ein neuer Techniker hinzukommen kann, ein Unbeteiligter, der das System praktisch "auf den ersten Blick" verstehen muss.

Das einzige, was mir auffällt (was ich aber von MySQL
nicht als Problem erwarten würde) ist, dass Du die
Spalte als VARCHAR angelegt hast[1] und als NUMBER
abfragst.
Und es ist in der Tat die Lösung des Problems:

Echt? Wow... ich hatte ehrlich gesagt nicht ernsthaft damit gerechnet :-) Gerade MySQL, welches immer schön still im Hintergrund Auto-Typecasts macht, sogar bei CREATE TABLE. Nun ja.

mySQL konvertiert zwar implizit den Datentyp, schaltet
dabei aber den Indexzugriff ab - darauf muß man erst
mal kommen ...

Allerdings :-)

[1] Wenn es sich, wie mir scheint, um eine Referenz
handelt, sollte es eh ein NUMBER-Typ sein ;-)
Wahr - aber die Tabelle stammt nicht von mir

Ja, das kenne ich. Sowas ist immer ärgerlich *g*

Cheatah

[Michael Schröpl]

Hallo Cheatah,

und "nref" bedeutet...? :-)

"Nachrichten-Referenz".

Versteh mich bitte nicht falsch, es ist natürlich euer Bier,
wie ihr die Nomenklatur aufstellt.

Hätte ich diese Tabelle anlegen dürfen, dann hießen die Felder anders.

Bei meinen in dieses System eingebauten Tabellen habe ich mich aller-dings an die t_/d_-Konvention gehalten, um keine unnötigen Brüche in
die Nomenklatur zu bringen.
Außerdem gibt es zu _meinen_ Tabellen einen ausführlich kommentierten
Quelltext aller CREATE TABLE-Statements ... zu der besagten Tabelle
habe ich nur, was mySQL mir darüber erzählt.

Denk aber auch daran, dass mal ein neuer Techniker hinzukommen
kann, ein Unbeteiligter, der das System praktisch "auf den ersten
Blick" verstehen muss.

Ich habe die besagte Firma mehr als nur im Verdacht, daß sie genau das
verhindern will ... :-\

Und es ist in der Tat die Lösung des Problems:
Echt? Wow... ich hatte ehrlich gesagt nicht ernsthaft damit
gerechnet :-) Gerade MySQL, welches immer schön still im
Hintergrund Auto-Typecasts macht, sogar bei CREATE TABLE. Nun ja.

Genau der type cast scheint ja das Problem zu sein.

In http://www.mysql.com/doc/M/y/MySQL_indexes.html stand
allerdings m. E. nichts Entsprechendes drin - bisher jedenfalls. ;-)

mySQL konvertiert zwar implizit den Datentyp, schaltet
dabei aber den Indexzugriff ab - darauf muß man erst
mal kommen ...
Allerdings :-)

Ich kann es sogar nachvollziehen. Allerdings sollte das eher ein bug
sein - wenn man die Reihenfolge im Query Optimizer umstellt (erst den
type cast, dann die Index-Entscheidung), sollte es ja kein Problem sein.

Viele Grüße
      Michael
(mit nunmehr laufender "related news search" :-)

[Cheatah]

Hi,

und "nref" bedeutet...? :-)
"Nachrichten-Referenz".

dann wäre "t_nachrichten_referenz" oder z.B. "t_news_ref" deutlich sprechender... Da ich Dir das eigentlich nicht zu sagen brauche, erwähne ich es nur für die Akten und eventuelle Mitleser ;-)

Versteh mich bitte nicht falsch, es ist natürlich euer Bier,
wie ihr die Nomenklatur aufstellt.
Hätte ich diese Tabelle anlegen dürfen, dann hießen die Felder anders.

Meine Verwunderung basierte insbesondere auf Unkenntnis der Tatsache, dass das DB-Layout nicht von Dir stammt. Trotzdem empfehle ich einen Refactoring-Schritt.

Bei meinen in dieses System eingebauten Tabellen habe ich mich aller-dings an die t_/d_-Konvention gehalten, um keine unnötigen Brüche in die Nomenklatur zu bringen.

Konsequenz ist sinnvoll, auch wenn das Ergebnis konsequent sinnarm ist.

Außerdem gibt es zu _meinen_ Tabellen einen ausführlich kommentierten
Quelltext aller CREATE TABLE-Statements ...

Für alle Mitleser: Das ist extrem wichtig. Tabellenstruktur, Bedeutung und Inhalt der Tabelle, Bedeutung und Inhalte der einzelnen Spalten, eventuelle Semantiken (die zwar vermieden werden sollten, aber nicht immer können), Abhängigkeiten mit anderen Tabellen, evtl. Herkunft des Inhaltes, potentielle sowie übliche Statements, Beschreibung wann welcher Index verwendet wird usw. - all das gehört dokumentiert. Idealerweise legt man pro Tabelle eine Datei namens "tabellenname.sql" an, welche in einem allgemein zugänglichen Verzeichnis liegt - und regelmäßig gesichert wird.

Denk aber auch daran, dass mal ein neuer Techniker hinzukommen
kann, ein Unbeteiligter, der das System praktisch "auf den ersten
Blick" verstehen muss.
Ich habe die besagte Firma mehr als nur im Verdacht, daß sie genau das
verhindern will ... :-\

Oder es waren einfach nur Stümper am Werk. Oft ist das auch der Grund, weshalb der Quellcode nicht freigegeben wird: die Entwickler schämen sich dafür.

Diesen Effekt sieht man auch oft bei Leuten, die die rechte Maustaste sperren wollen, um "ihren Quälcode zu schützen" ;-)

Genau der type cast scheint ja das Problem zu sein.
In http://www.mysql.com/doc/M/y/MySQL_indexes.html stand
allerdings m. E. nichts Entsprechendes drin - bisher jedenfalls. ;-)

Hast Du das Problem mal an MySQL geschickt? Sowas gehört eindeutig dokumentiert - bzw. gebugfixt, denn ich unterstelle einfach mal, dass es sich hier um einen Bug handelt. Es gibt IMHO keinen Grund, weshalb ein Index nicht verwendet werden soll, nachdem die zu suchende Konstante(!) einen Typecast erfahren hat.

Ich kann es sogar nachvollziehen. Allerdings sollte das eher ein bug
sein - wenn man die Reihenfolge im Query Optimizer umstellt (erst den
type cast, dann die Index-Entscheidung), sollte es ja kein Problem sein.

Eben...

Cheatah