(MySQL) Index auf BLOBs arbeitet falsch(?)

[Cheatah]

Hi,

mehr oder weniger ist dies informativ gedacht; vielleicht weiß aber auch jemand, wie das Problem zu lösen ist.

Vorgeschichte: Ich habe in einer MySQL-Tabelle (V 3.23, glaube ich) einen mehrspaltigen Index angelegt, welcher auch über einige TEXT-Spalten ging (ich habe mit Datentypen experimentiert). Das Statement dazu sah etwa wie folgt aus:

CREATE INDEX indexname ON tabellenname (zahlenfeld, ip (15), page (250), nochwasanderes)

"ip" war als TINYTEXT, "page" als TEXT definiert. Die Breite der Spalte "ip" ergibt sich aus der Maximallänge einer IP-Adresse (logisch).

Füllen konnte ich beide Spalten hervorragend, und nach allen Regeln der Kunst stimmte der Inhalt auch exakt mit dem überein, was drin stehen sollte. Nchdem der Index jedoch angelegt war, brachte ein Billig-Select der Art

SELECT page, count(*)
FROM tabelle
WHERE zahlenfeld=? AND ip=?
GROUP BY page

kein Ergebnis mehr - ohne den Index kam wieder das, was ich erwartet hatte. Auch das Entfernen der Spalte "nochwasanderes" im Index änderte dies nicht. Ein EXPLAIN auf das Statement verriet mir, dass genau dieser Index verwendet werden sollte.

Nun ja, ich sollte noch erwähnen, dass der Inhalt der Spalte "ip" meist auf "127.0.0.1" lautete und damit die Länge 15 nicht erreichte. Wenn ich die Indexdefinition auf "ip (9)" geändert hatte, brachte das aber dummerweise auch nichts. Erst die (sinnvolle) Umstellung auf VARCHAR ermöglichte es, trotz Index zu den richtigen Ergebnissen zu kommen.

Ist das ein Phänomen, oder einfach eine typische Eigenschaft von BLOBs? Die Doku verriet mir nur, dass bei BLOBs dem Index eben Längen mitgegeben werden müssen, nicht aber, dass es dann "Besonderheiten" bei der Funktionsweise gibt. Oder ist das ein (mittlerweile gefixter) Bug?

Cheatah

[xwolf]

Hi,

CREATE INDEX indexname ON tabellenname (zahlenfeld, ip (15), page (250), nochwasanderes)

"ip" war als TINYTEXT, "page" als TEXT definiert. Die Breite der Spalte "ip" ergibt sich aus der Maximallänge einer IP-Adresse (logisch).

Füllen konnte ich beide Spalten hervorragend, und nach allen Regeln der Kunst stimmte der Inhalt auch exakt mit dem überein, was drin stehen sollte. Nchdem der Index jedoch angelegt war, brachte ein Billig-Select der Art

SELECT page, count(*)
FROM tabelle
WHERE zahlenfeld=? AND ip=?
GROUP BY page

Hast du daran gedacht, deine Nichtnumerischen Datenbankfelder zu quoten?

Ich hab mir fuer solche Abfragen eine Routine gebaut, die so aussieht:

(REadDBStructure entaehlt dabei ein Hash, worin nur steht, von welchem Typ
welches Datenbankfeld ist)

sub MakeRequest {
  my $table = shift;
  my $request = shift;
  my $like = shift;
  my @input = split(/,\s*/,$request);
  my %hash;
  my $i;
  my %STRUCTURE = ReadDBStructure($CONFIG{'DB_STRUCTURE_FILE'});
  my $query;
  my ($keyname, $value);
  my $this;
  my %result;
  my %realresult;
  my $count;
  my $key;

if (not $table) {
    return;
  }
  if ($request) {
    for ($i=0; $i<=$#input; $i++) {
      ($keyname, $value) = split(/ = /,$input[$i],2);
      if ($like) {
        # $value = "%".$value."%";
        $this = $value;
      } else {
        if ($STRUCTURE{$table}{'struc'}{$keyname} !~ /^(TINYINT|SMALLINT|MEDIUMINT|INTEGER|BIGINT)/) {
          $this = $dbh->quote($value);
        } else {
          $this = $value;
          $this =~ s/[^0-9.,-]//gi;
        }
      }
      if ($like) {
        if ($query) {
          $query .= "\nAND $keyname LIKE '%".$this."%'";
        } else {
          $query .= "$keyname LIKE '%".$this."%'";
        }
      } else {
        if ($query) {
          $query .= "\nAND $keyname = $this";
        } else {
          $query .= "$keyname = $this";
        }

}
    }
    $query = "SELECT * FROM $table\nWHERE $query";
  } else {
    $query = "SELECT * FROM $table";
  }
  my $sth = $dbh->prepare($query);
  $sth->execute();
  while (my $ref = $sth->fetchrow_hashref()) {
    $count++;
    foreach $key (keys %$ref) {
      $result{$count}{$key} = $ref->{$key};
    }
  }
  $sth->finish();
  return %result;
}

Ciao,
  Wolfgang

[Cheatah]

Hi,

WHERE zahlenfeld=? AND ip=?
Hast du daran gedacht, deine Nichtnumerischen Datenbankfelder zu quoten?

das erübrigt sich dadurch, dass ich mit Bindvariablen arbeite... ;-)

Ich hab mir fuer solche Abfragen eine Routine gebaut, die so aussieht:

Danke dafür. Sieht allerdings etwas proprietär aus (kannst Du es generisch gestalten? Dann wär's evtl. was für CPAN), und Typen wie FLOAT und DOUBLE hast Du auch vergessen. Aber die Idee gefällt mir - auch wenn ich trotzdem bei Bindvariablen bleibe *g*

sub MakeRequest {
  my $table = shift;
  my $request = shift;
  my $like = shift;

my ($table, $request, $like) = (shift||'', shift||'', shift||'');

my %hash;
  my $i;

my (%hash, $i, $query, ...);

Ist Geschmackssache, aber ich find's so übersichtlicher :-)

if (not $table) {
    return;
  }

return unless $table ;-)

($keyname, $value) = split(/ = /,$input[$i],2);

Hier gehst Du davon aus, dass 'WHERE x = ...' mit Leerzeichen geschrieben wird, sehe ich das richtig? Was ist mit BETWEEN?

if ($like) {

Hm, irgendwie verstehe ich das ganze glaube ich noch nicht. Erlaubst Du hier nur _entweder_ LIKE-Abfragen oder _keine_ LIKEs?

$query = "SELECT * FROM $table\nWHERE $query";

Oh. Selektive SELECTs, Joins usw. erlaubst Du nicht? Gut, dass MySQL keine Subselects kann... ;-)

Cheatah

[xwolf]

Hi,

Danke dafür. Sieht allerdings etwas proprietär aus (kannst Du es generisch gestalten? Dann wär's evtl. was für CPAN),

Muss noch verbessert werden dazu...

und Typen wie FLOAT und DOUBLE hast Du auch vergessen. Aber die Idee gefällt mir - auch wenn ich trotzdem bei Bindvariablen bleibe *g*

sub MakeRequest {
  my $table = shift;
  my $request = shift;
  my $like = shift;

my ($table, $request, $like) = (shift||'', shift||'', shift||'');

my %hash;
  my $i;

my (%hash, $i, $query, ...);

Ist Geschmackssache, aber ich find's so übersichtlicher :-)

Hihi...genau das wollte ich gerade als Begruendung fuer meinen
Code sagen!

Hier gehst Du davon aus, dass 'WHERE x = ...' mit Leerzeichen geschrieben wird, sehe ich das richtig? Was ist mit BETWEEN?

BETWEEN....ups! Vergessen :)

if ($like) {

Hm, irgendwie verstehe ich das ganze glaube ich noch nicht. Erlaubst Du hier nur _entweder_ LIKE-Abfragen oder _keine_ LIKEs?

Genau.

$query = "SELECT * FROM $table\nWHERE $query";

Oh. Selektive SELECTs, Joins usw. erlaubst Du nicht? Gut, dass MySQL keine Subselects kann... ;-)

Das ist ja nur fuer einfache Abfragen....

Fuer komplexere hab ich ne andere Sub.
Ich hab auch ein paar Zeilen weggelassen als ich es hier postete :)

Ciao,
  Wolfgang

[Cheatah]

Hi,

Ist Geschmackssache, aber ich find's so übersichtlicher :-)
Hihi...genau das wollte ich gerade als Begruendung fuer meinen
Code sagen!

*g* über Geschmack darf man ja nicht streiten :-)

Hier gehst Du davon aus, dass 'WHERE x = ...' mit Leerzeichen geschrieben wird, sehe ich das richtig? Was ist mit BETWEEN?
BETWEEN....ups! Vergessen :)

Tja, SQL ist recht komplex. Wenn Du's schaffst, die Routine zu verallgemeinern (und sei es erst mal nur auf MySQL-Basis, ohne Subselects), so dass sie mit jedem gültigen Statement klarkommt, hast Du schon verdammt viel geschafft.

Mit Bindvariablen hättest Du's allerdings deutlich leichter ;-)))

Oh. Selektive SELECTs, Joins usw. erlaubst Du nicht? Gut, dass MySQL keine Subselects kann... ;-)
Das ist ja nur fuer einfache Abfragen....
Fuer komplexere hab ich ne andere Sub.

Vereinen, vereinen, vereinen! :-)

Ich hab auch ein paar Zeilen weggelassen als ich es hier postete :)

*mit finger auf xwolf zeig* AHA!!!1 Du verheimlichst uns Code und machst Geheimsachen! Pösewicht, Du! :-)

Cheatah

[xwolf]

Hi,

Ich hab auch ein paar Zeilen weggelassen als ich es hier postete :)

*mit finger auf xwolf zeig* AHA!!!1 Du verheimlichst uns Code und machst Geheimsachen! Pösewicht, Du! :-)

Garnicht so unecht :)

Ich arbeite zur Zeit an einer Metasprache, mit der sich, aehnlich wie bei PHP,
komplexe Layouts aus HTML, Text oder anderem zusammen mit SQL-Datenbankzugriffen automatisch umwandeln und bearbeiten lassen.
Mit dem Unterschied jedoch, dass vom Funktionsumfang die Datenbankkonnektivitaet von PHP gegenueber dieser Sprache ziemlich alt (*) aussieht und das ganze Ding *nicht nur Browserbasiert* ist, sondern als Perlmodul und als Windows-DLL herkommt.

Das beste ist aber: Die Templates, die auf Unix, Linux und sogar auf Windows durch die Sprache bearbeitet wurden, sehen danach gleich aus - Egal ob man als DB dann mySQL, mSQL, Interbase oder was anderes nutzt....

Moeglicherweise werden wir die Sprache und die Module dazu bald
veroeffentlichen ....

Nachdem ich es als Content-Management-System fuer xwolf eingebaut hab.

Ciao,
  Wolfgang

(* Und das ist nicht uebertrieben. Ich kenne PHP auch etwas mehr als wie ich Jan verrate)

[Christian Kruse]

Hoi,

[... eigene Meta-Sprache ...]

Code-Beispiele! Code-Beispiele! Code-Beispiele! ;-)

Gruesse,
 CK

[xwolf]

Hoi,

[... eigene Meta-Sprache ...]

Code-Beispiele! Code-Beispiele! Code-Beispiele! ;-)

Ok, ein bischen zum verwirren:

Hauptdatei:

<h2>Einstufungstest in #sprache.Name#</h2>
                <p>Da Sie noch keine Vorbildung in #sprache.Name# durch Scheine
haben, müssen Sie an einem <B>verbindlichen Einstufungstest</B> teilnehmen.
<br><br><br></p>
                <form  method="post" action="#CONST_formaction##CONST_actionfile
#">#CONST_formhidden#

#SQL_6etplatzcheck.#sprache.Id##

#IF_#ISDEF_6etplatzcheck#.==.1#
  #_6withet#
#ELSE#
  #_6withoutet#
#ENDIF#
...

=================
Datei 6etplatzcheck.sql :

SELECT MAX("Date") FROM DIC_ETPLATZ WHERE "RefIdStudent" IS NULL AND "RefIdSprac
he" = :Lang AND "Key" IS NULL AND "Date" >= 'Now' group by "Date" HAVING "RefIdS
emester" = #CONST_SemesterId#

=================
6withet.html:
..
                <table>
                <tr>
                        <td>
                                <P>Bitte wählen Sie einen Termin aus der Li
ste aus:</P>
                                <select name="DIC_ETPLATZ.Id" size="10">
                                        #__incetplatz.#sprache.Id##
                                </select>
                        </td>
                </tr>
...

Datei incetplatz.sql:
SELECT MAX("Date"), COUNT("Date") FROM DIC_ETPLATZ WHERE "RefIdStudent" IS NULL
AND "RefIdSprache" = :Lang AND "Key"
IS NULL AND "Date" >= 'Now' group by "Date" HAVING "RefIdSemester" = #CONST_Seme
sterId# ORDER BY 1

=================
Datei incetplatz.html
<option value="#.MAX#">#.MAX# (freie Plätze: #.COUNT#)</option>

=================

Alles klar ? :))

Die Sprache ist da schon ziemlich mächtig..
Und da wurden nur die Befhle fuer IF und dem von einewr DB abhängigen INCLUDE verwendet ...

Ciao,
  Wolfgang

[Christian Kruse]

Hoi,

[... metasprache ...]

Alles klar ? :))

Ne ;-) Woher kommen z. B. #sprache.Name#? Oder die ganzen Konstanten?

Die Sprache ist da schon ziemlich mächtig..

Ja ;-)

Gruesse,
 CK

[Cheatah]

Hi,

Ich arbeite zur Zeit an einer Metasprache, [...]

klingt geil! Halt uns mal bitte auf dem laufenden :-)

Cheatah

[Michael Schröpl]

Hi Cheatah,

CREATE INDEX indexname ON tabellenname (zahlenfeld, ip (15), page (250),
nochwasanderes)

Welchen Tabellentyp verwendest Du?
Hat dessen Auswahl (und der dabei implizit angezogene Treiber) einen Einfluß auf das Phänomen?

"ip" war als TINYTEXT

Was hältst Du davon, die IP zu konvertieren und als unsigned 32 bit integer zu speichern?
Das müßte den Indexzugriff beschleunigen und zudem Platz sparen (vor allem im Index - in der Tabelle ist der Unterschied eher vernachlässigbar).

Nachdem der Index jedoch angelegt war, brachte ein Billig-Select der Art
SELECT page, count(*)
FROM tabelle
WHERE zahlenfeld=? AND ip=?
GROUP BY page
kein Ergebnis mehr

Grübelgrübel ...

Ein EXPLAIN auf das Statement verriet mir, dass genau dieser Index verwendet
werden sollte.

... das wäre meine nächste Frage gewesen ...

Auch das Entfernen der Spalte "nochwasanderes" im Index änderte dies
nicht.
Nun ja, ich sollte noch erwähnen, dass der Inhalt der Spalte "ip" meist
auf "127.0.0.1" lautete und damit die Länge 15 nicht erreichte.

Wenn ich "meist" wörtlich nehmen soll, dann ist der Index so nicht sehr projektiv. (Aber wahrscheinlich wird er das bei richtigen Produktionsdaten sein.)
In jedem Fall ist Deine Anordnung der beiden Felder im Index m. E. so richtig herum ...

Oder ist das ein (mittlerweile gefixter) Bug?

Es könnte sein, daß "3.23" dafür eine zu ungenaue Angabe ist.
(Ich verwende 3.23.38, aktuell ist wohl 3.23.47.)

Viele Grüße
      Michael
(der sich gerade mit etwas vage Ähnlichem herumschlägt: Ein CREATE FULLTEXT INDEX auf etwa 100 MB Textdaten erzeugt nach knapp 2 Stunden den anscheinend richtigen Indexbaum [dessen Größe mir bekannt ist], aber statt diesen und die temporäre Version der Tabelle dann auf den endgültigen Dateinamen umzubenennen, beschließt der mySQL-daemon, eine Endlosschleife mit 100% CPU-Auslastung lustiger zu finden ...)

[Cheatah]

Hi Michael,

Welchen Tabellentyp verwendest Du?

den, der bei einem CREATE TABLE entsteht... bzw. wen PHPMyAdmin es anders macht, dann eben den.

Sorry, mir war nicht bewusst, dass MySQL unterschiedliche Tabellentypen kennt. Wie finde ich das ggf. heraus?

Was hältst Du davon, die IP zu konvertieren und als unsigned 32 bit integer zu speichern?

Abstand. Ich müsste ständig hin und her konvertieren, obwohl ich grundsätzlich nur den IP-String brauche. Mit VARCHAR statt *TEXT funktioniert der Index ja auch, es gibt also keinen akuten Handlungsbedarf.

Das müßte den Indexzugriff beschleunigen und zudem Platz sparen (vor allem im Index - in der Tabelle ist der Unterschied eher vernachlässigbar).

Ja, das glaube ich Dir; nur ist Geschwindigkeit bei MySQL ja nicht gerade das primäre Problem ;-) und Speicherplatz habe ich prinzipiell genug (für meine Zwecke zumindest). Die Idee halte ich aber im Hinterkopf, falls ich das ganze ausweiten möchte, danke dafür.

Ein EXPLAIN auf das Statement verriet mir, [...]
... das wäre meine nächste Frage gewesen ...

Sorry, dass ich sie zu früh beantwortet habe... ich schalte meine Vorahnungen mal aus *klick* ;-)

Nun ja, ich sollte noch erwähnen, dass der Inhalt der Spalte "ip" meist
auf "127.0.0.1" lautete und damit die Länge 15 nicht erreichte.
Wenn ich "meist" wörtlich nehmen soll, dann ist der Index so nicht sehr projektiv. (Aber wahrscheinlich wird er das bei richtigen Produktionsdaten sein.)

Ja, genau - in der Entwicklungsphase sind die Daten halt nicht besonders repräsentativ...

In jedem Fall ist Deine Anordnung der beiden Felder im Index m. E. so richtig herum ...

Danke :-)

Oder ist das ein (mittlerweile gefixter) Bug?
Es könnte sein, daß "3.23" dafür eine zu ungenaue Angabe ist.
(Ich verwende 3.23.38, aktuell ist wohl 3.23.47.)

Ich schau mal nach, was es genau ist.

(der sich gerade mit etwas vage Ähnlichem herumschlägt: Ein CREATE FULLTEXT INDEX auf etwa 100 MB Textdaten erzeugt nach knapp 2 Stunden den anscheinend richtigen Indexbaum [dessen Größe mir bekannt ist], aber statt diesen und die temporäre Version der Tabelle dann auf den endgültigen Dateinamen umzubenennen, beschließt der mySQL-daemon, eine Endlosschleife mit 100% CPU-Auslastung lustiger zu finden ...)

Cool! MySQL ist witzig. Warum benutze ich eigentlich nicht 'ne _richtige_ Datenbank? :-)

Cheatah

[Michael Schröpl]

Hi Cheatah,

Sorry, mir war nicht bewusst, dass MySQL unterschiedliche Tabellentypen
kennt. Wie finde ich das ggf. heraus?

http://www.mysql.com/doc/T/a/Table_types.html

Sehr schöne Lektüre (InnoDB ist relativ nah dran an Oracle).
Nimm Dir reichlich Zeit dafür, es ist einiges an Stoff.

Cool! MySQL ist witzig. Warum benutze ich eigentlich nicht 'ne
_richtige_ Datenbank? :-)

Diese Frage stellt sich mir 'glücklicherweise' nicht:
Die Daten, die ich verarbeiten will, sind in einer mySQL-Datenbank eines
Fremdprodukts drin, von dem ich einen Prozeß wegwerfe und neu schreibe.

Viele Grüße
      Michael (bekennender Oracle7-Fan)