Syntax Query über viele Tabellen

t_soul · 08.12.2005

Hallo,

als mySQL-newbie stehe ich auf dem Schlauch:
Gegeben sind mehrere Tabellen, in denen ich einen Begriff suche:

Haupttabelle ist A, Untertabellen sind B, C, ... M.
Schlüssel (die ID der Haupttabelle) ist jeweils inv.
Von der Haupttabelle ausgehend m:n-Beziehungen.

Nun suche ich alle DS in A, welche
- in A in verschiedenen Feldern einen Suchbegriff enthalten können (LIKE 'sonstwas'),
oder
- in einem Feld von B 'sonstwas' stehen haben
oder
- in einem Feld von C 'sonstwas' stehen haben
oder
- in einem Feld von D 'sonstwas' stehen haben
oder
- in einem Feld von E 'sonstwas' stehen haben
oder ...
...gähn

Wie bitte ist die allg. Syntax füe diese Abfrage?

Die Datensätze aus A sollen natürlich nur eimal erscheinen.
Der Suchbegriff kann irgendwo stehen, d.h. er muß nicht selbst in A vorkommen.

Folgende Syntax zumindest (bitte nicht schlagen!) zwingt den
Server in die Knie:

"SELECT Ob.inv, Ob.Bereich, Ob.Sammlung, Ob.Objekt, Ob.Titel, Ob.Bild1
FROM (mOBJEKT Ob, mAltNamen aN, mMaterial Ma, mVerwendung Vw, mHersteller Hst)
WHERE (Ob.inv like '%sonstwas%' OR
Ob.Bereich like '%sonstwas%' OR
Ob.Sammlung like '%sonstwas%' OR
Ob.Objekt like '%sonstwas%' OR
Ob.Titel like '%sonstwas%' OR
Ob.Internet like '%sonstwas%' OR
aN.AltNamen like '%sonstwas%' AND aN.inv = Ob.inv OR
Ma.Material like '%sonstwas%' AND Ma.inv = Ob.inv OR
Vw.Verwendung like '%sonstwas%' AND Vw.inv = Ob.inv OR
Hst.Hersteller like '%sonstwas%' AND Hst.inv = Ob.inv )
GROUP BY 'Ob.inv'
ORDER BY 'Ob.inv'
ASC LIMIT 25;"

Es können noch mehr Tabellen hinzukommen...

Ich vermute, daß ich dies mit JOIN o.ä. lösen muß, allein der
Ansatz fehlt mir...
Oder muß ich die inv in einer neuen Tabelle zwischenspeichern?

Herzlichen Dank für DEN weiterhelfenden Tip!

Till

msslovi0 · 08.12.2005

Joins machst du ja schon, indem du die Tabellen verknüpfst. Wir sollten vielleicht über die Art der Joins diskutieren, aber ich weiß ja nicht, was du zurück bekommen willst. Deine Abfrage würde aber zur Zeit nur was finden, wenn es in allen verknüpften Tabellen (Altnamen, Material, Verwendung, Hersteller) einen entsprechenden Eintrag für Objekt gibt!

BTW: Wenn inv dein Primärschlüssel ist, kann es dann sein, dass du einfach falsch klammerst?

Code:

SELECT DISTINCT
  Ob.inv, 
  Ob.Bereich, 
  Ob.Sammlung, 
  Ob.Objekt, 
  Ob.Titel, 
  Ob.Bild1
FROM 
  mOBJEKT Ob, 
  mAltNamen aN, 
  mMaterial Ma, 
  mVerwendung Vw, 
  mHersteller Hst
WHERE
  Ob.inv like '%sonstwas%' ||
  Ob.Bereich like '%sonstwas%' ||
  Ob.Sammlung like '%sonstwas%' ||
  Ob.Objekt like '%sonstwas%' ||
  Ob.Titel like '%sonstwas%' ||
  Ob.Internet like '%sonstwas%' ||
  (aN.inv = Ob.inv && aN.AltNamen like '%sonstwas%') ||
  (Ma.inv = Ob.inv && Ma.Material like '%sonstwas%') ||
  (Vw.inv = Ob.inv && Vw.Verwendung like '%sonstwas%') ||
  (Hst.inv = Ob.inv && Hst.Hersteller like '%sonstwas%')
ORDER BY 
  Ob.inv ASC LIMIT 25;

Matt

t_soul · 08.12.2005

Hallo Matt,

Danke Dir.
Ich probierte es mit Deinem Code, aber leider dreht es da auch ewig. Ich vermute, er hat ein Problem mit der Tabellenanzahl. (Übrigens versuchte ich es mit mySQL v3.23 sowie v4.0, ich benutze MAMP zum lokalen Programmieren. Aber auch den Kundenserver zwinge ich damit in die Knie, der hat v4.1.14)

Liegt es ggf. an den Spalteneigenschaften?:
z.B. AltNamen:

CREATE TABLE `mAltNamen` (
`ID` int(11) NOT NULL auto_increment,
`inv` varchar(9) collate latin1_german2_ci NOT NULL default '',
`AltNamen` varchar(50) collate latin1_german2_ci NOT NULL default '',
`c` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`,`inv`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci COMMENT='Tabelle mit den alternativen Objektbezeichnungen' AUTO_INCREMENT=291 ;

(Bei meiner lokalen Installation unter MAMP fehlt "ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci".)

Wenn es das nicht ist:
Wäre es eine schnell laufende Möglichkeit, alle Tabellen mit einzelnen Querys abzuklappern, die gefundenen inv in eine temporäre Tabelle zu legen und dann die Haupttabelle mOBJEKT und diese temp. Tabelle abzufragen?
Ich habe aber keine Ahnung, wie man sowas schreibt.

Till

msslovi0 · 08.12.2005

So auf den ersten Blick: Dein Primärschlüssel sieht komisch aus. Wenn du einen EXPLAIN SELECT machst, wirst du auch sehen, das er nicht in der Lage ist, einen Schlüssel zu verwenden. ID ist doch schon eindeutig. Und warum nutzt du nicht gleich ID als Primary- und Foreignkey?

Ansonsten, wenn ich mir auf der Basis eine Testdatenbank anlege, funktioniert das wunderbar. OK, der Testdatenbestand bei mir ist jetzt auch nicht so groß... Zu viele Tabellen ist eher unwahrscheinlich. Ich habe hier Abfragen, die über weit mehr Tabellen laufen.

Ansonsten wäre es mal gut zu wissen, wie die anderen Tabellen aussehen.

Matt

t_soul · 09.12.2005

Hallo Matt,

die Daten werden aus einer XML-Tabelle importiert. Die inv (Inventarnummer) ist dort die ID. Die Daten werden in unbestimmten Zeiträumen durch Importe aktualisiert, also teilweise überschrieben.

Danke für den Tip mit EXPLAIN SELECT! Damit kam zumindest ein leeres Ergebnis. Dies warf er aus:

PHP:

table type  possible_key  key   key_len  ref   rows  Extra
 Hst   ALL   NULL          NULL  NULL     NULL  50    Using temporary
 Ob    ALL   inv           NULL  NULL     NULL  60      
 Vw    ALL   NULL          NULL  NULL     NULL  90    Distinct
 Ma    ALL   NULL          NULL  NULL     NULL  110   Distinct
 aN    ALL   NULL          NULL  NULL     NULL  290   Using where; Distinct

(php-Code nur der Anzeige wegen

Es schein also wirklich an den Tabellen zu liegen und nicht an der Abfrage. Nur was muß normalerweise dort stehen bzw. wie erreiche ich es? Ich bekam es nicht hin, die Keys an der Tabelle zu ändern, muß diese also wohl neu anlegen (phpMyAdmin). Jedenfalls bohrte ich tagelang an der falschen Stelle... Kann denn mySQL nicht eine nette Fehlermeldung bringen: falscher Key, Index fehlt oder sonstwas?...

Einen Tipp mit einer temporären Tabelle fand ich übrigens unter http://www.heddesheimer.de/coaching/mysql_group.html
Aber erstmal muß ich hier aufräumen...

Danke nochmal! (und gute N8 erstmal) - Till

msslovi0 · 09.12.2005

Nur kurz, bevor die Arbeit ruft: Doch, es geht, mit PMA die Keys zu ändern: Klick links auf den Namen der Tabelle, dann siehst du rechts oben deine Felddefinitionen und darunter ganz links die angelegten Indizes, auf das Editiericon hinter deinem aktuellen PRIMARY klicken, für das zweite Feld "-- Ignorieren --" wählen und speichern.

Jetzt noch für inv einen normalen Index anlegen. Das geht am einfachsten, indem du oben in den Felddefinitionen das entsprechende Icon anklickst.

Matt

t_soul · 10.12.2005

Hallo Matt,
jetzt erst sah ich Deine Antwort. Danke!
Dies probierte ich schon, bekam aber eine Fehlermeldung beim Speichern:
ALTER TABLE `mOBJEKT` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `inv` )
->
MySQL said:
#1075 - Incorrect table definition; There can only be one auto column and it must be defined as a key

Wenn ich nur schreibe
ALTER TABLE `mOBJEKT` DROP PRIMARY KEY `ID`
kommt
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID`' at line 1

Ich bin erstmal mit anderen Sachen beschäftigt. Dann werde ich die Tabellen neu anlegen (ohne ID), inv nur indiziert.
Ich las auch schon unter http://dev.mysql.com/doc/refman/4.0/de/explain.html
Besonders der untere Teil scheint mir interessant...

Danke und Gruss - Till

t_soul · 15.12.2005

Also irgendwie klemmt die Säge noch heftig.
Folgende Tbl-Def:
CREATE TABLE `mOBJEKT` (
`ID` int(11) NOT NULL auto_increment,
`inv` varchar(9) NOT NULL default '',
`Bereich` varchar(60) NOT NULL default '',
`Sammlung` varchar(60) NOT NULL default '',
`Objekt` varchar(60) NOT NULL default '',
`Titel` varchar(100) NOT NULL default '',
`InternetOrig` text NOT NULL,
`Internet` text NOT NULL,
`Bild1` varchar(100) NOT NULL default '',
`c` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `ID` (`ID`),
KEY `inv` (`inv`),
KEY `Bereich` (`Bereich`),
KEY `Sammlung` (`Sammlung`),
KEY `Objekt` (`Objekt`),
KEY `Titel` (`Titel`),
FULLTEXT KEY `Internet` (`Internet`)
) TYPE=MyISAM AUTO_INCREMENT=61 ;

Ich legte also für jedes zu durchsuchende Feld einen index an. Damit bringt er bei zwei Tabellen ein Ergebnis, es dauert allerdings einige Sekunden.

Die Abfrage lautet:

Code:

SELECT DISTINCT
  Ob.inv, 
  Ob.Bereich, 
  Ob.Sammlung, 
  Ob.Objekt, 
  Ob.Titel, 
  Ob.Bild1
FROM 
  mOBJEKT Ob, 
  mAltNamen aN
WHERE (
  Ob.inv LIKE  '%geheim%'
  OR Ob.Bereich LIKE  '%geheim%'
  OR Ob.Sammlung LIKE  '%geheim%'
  OR Ob.Objekt LIKE  '%geheim%'
  OR Ob.Titel LIKE  '%geheim%'
  OR Ob.Internet LIKE  '%geheim%'
  OR (aN.AltNamen LIKE  '%geheim%' AND aN.inv = Ob.inv)
)
ORDER BY 
  Ob.inv ASC 
  LIMIT 25;

EXPLAIN bringt dieses Ergebnis:

Code:

table type possible_keys key   key_len ref   rows  Extra
aN    ALL  inv           NULL  NULL    NULL  290   Using temporary
Ob    ALL  inv           NULL  NULL    NULL  60    Using where

Wie schafft man es nun, daß unter key die Schlüsselfelder erscheinen? Ich bekam es mit viel Probieren nicht hin, sicher übersah ich etwas.

Danke! - Till

msslovi0 · 16.12.2005

Da hatte ich wohl letzte Woche nen Blackout und auch falsch geklammert...

Code:

SELECT DISTINCT
  Ob.inv, 
  Ob.Bereich, 
  Ob.Sammlung, 
  Ob.Objekt, 
  Ob.Titel, 
  Ob.Bild1
FROM 
  mOBJEKT Ob, 
  mAltNamen aN
WHERE 
  aN.inv = Ob.inv && (
  Ob.inv LIKE  '%3yxhape9s99k3l1pdpk%'
  OR Ob.Bereich LIKE  '%3yxhape9s99k3l1pdpk%'
  OR Ob.Sammlung LIKE  '%3yxhape9s99k3l1pdpk%'
  OR Ob.Objekt LIKE  '%3yxhape9s99k3l1pdpk%'
  OR Ob.Titel LIKE  '%3yxhape9s99k3l1pdpk%'
  OR Ob.Internet LIKE  '%3yxhape9s99k3l1pdpk%'
  OR aN.AltNamen LIKE  '%3yxhape9s99k3l1pdpk%' )
ORDER BY 
  Ob.inv ASC 
  LIMIT 25

Benötigt hier (mit 100 Testdatensätzen) 0.0028 Sekunden. Der Index auf ID in mOBJEKT kann BTW weg, das ist ja schon durch PRIMARY gegeben.

Matt

t_soul · 16.12.2005

Hallo Matt,

Danke. So falsch schien Deine Klammerung aber nicht zu sein, denn ich ließ ja bei letztem Versuch die anderen Tabellen aus Performancegründen weg. Sonst dauerte das ewig bzw. der Server schmierte ab.
D.h. also, die Haupttabelle mObjekt bekommt 6 Abfragen und außerdem wird in mind. 4 weiteren Tabellen gesucht (siehe Beiträge 1 und 2).

Mit Deiner obigen Abfrage komme ich übrigens auf 0.07 Sek.
Bringt es was, wenn ich statt der inv die eindeutige ID als Key verwende? D.h. verursachen PRIMARY KEY und KEY verschiedene Geschwindigkeiten?
Ich weiß eben noch nicht, ob die Fehlerursache an der Abfrage oder an den Tabellen liegt.
Ich glaube, ich sollte doch mal ein mySQL-Handbuch von A bis Z lesen. Nur fordert man von mir schnellstens Ergebnisse... :-/ Ich komme aus der FileMaker-Ecke, da läuft die Indizierung bequemer. Dafür kann man bei FM von solchen Abfragen nur träumen.

Till

msslovi0 · 16.12.2005

Es sollte vor allem was bringen, weil du nicht mehr ein Varchar-Feld als Key - Foreign Key benutzt, sondern ein Integer-Feld.

Aber wenn du meine obige Abfrage auf dein Ursprungsbeispiel umbaust, sollte das jetzt auch deutlich schneller ausgeführt werden.

Matt

t_soul · 22.12.2005

Hallo Matt,

nach langer Pause nun endlich eine Meldung:
Ich habe alles soweit umbauen können, dass eine ID (Integer) als Key genommen wird. (Die inv konnte ich dazu nicht nehmen: "01428-1".)
Damit läuft es - zumindest bei wenigen Datensätzen erstmal - flott

Hab vielen Dank nochmal für Deine Hilfe.
Frohe Weihnachten und alles Gute für 2006!

Till

Syntax Query über viele Tabellen

t_soul

Mitglied

msslovi0

Aktives Mitglied

t_soul

Mitglied

msslovi0

Aktives Mitglied

t_soul

Mitglied

msslovi0

Aktives Mitglied

t_soul

Mitglied

t_soul

Mitglied

msslovi0

Aktives Mitglied

t_soul

Mitglied

msslovi0

Aktives Mitglied

t_soul

Mitglied