n:m - Verknüpfung -> Woher kommen diese Anomalien

**hOk** · 31.08.2002, 17:41

*leises huhu*
...hat wirklich keiner eine Idee?

Oder einen anderen Ansatz, den ich
probieren könnte?

viele nette Grüße, Holger

**CHnuschti** · 31.08.2002, 19:39

Ich nehme an, du willst letztendlich die urls aus seitentabelle aufgelistet haben, oder?
Willst du die Treffer, die eines oder mehrere Wörter finden, oder willst du nur die Treffer, bei denen ALLE gesuchten Wörter gefunden werden ("je url")?
mfg

**hOk** · 01.09.2002, 03:44

Original geschrieben von CHnuschti Ich nehme an, du willst letztendlich die urls aus seitentabelle aufgelistet haben, oder?

Ja, ganz genau

Willst du die Treffer, die eines oder mehrere Wörter finden, oder willst du nur die Treffer, bei denen ALLE gesuchten Wörter gefunden werden ("je url")?

Zweiteres, daher dachte ich, ich müsste mit HAVING und GROUP BY filtern...

netten Gruß, Holger

**CHnuschti** · 01.09.2002, 18:37

Also ich finde deine Fragestellung interessant, d.h. besser gesagt, die Frage ob man diese Abfrage in ein einziges Statement gepackt kriegt

Das ganze in mehreren Abfragen hinzukriegen ist wahrsch. nicht so schwierig.

M.E. ist der Suchvorgang nach deinem obigen WHERE abgeschlossen, das Group & having ist nur noch die Nachselektion, gewissermassen und diese baut auf dem Gefundenen auf, das "bis und mit" WHERE gefunden wurde, das HAVING ist also kein Kriterium für die eigentliche URL-Suche.

Ich weiss zwar atm auch nicht, wie das Statement richtig ausschauen soll

. Jedenfalls lässt sich folgendes feststellen, warum es nicht funktioniert: Du kannst mit dem Statement in Theorie beliebig viele urls finden, die auf besagte 3 Wörter zutreffen. Dieses Count zählt alle gefundenen URLs und steht in keinem Zusammenhang mit deinen 3 Wörter, d.h. es müsste ein Zufall sein, wenn du damit gerade 3 URLs findest. M.E findet dein Statement alle Datensätze, die EINES der gesuchten Wörter enthält.

Vllt. fällt mir noch was ein.

P.S beinhalten die Felder wort_id und seiten_id jeweils nur einen EINZIGEN Wert?
mfg

**CHnuschti** · 01.09.2002, 22:17

PHP-Code:


$hosti = "localhost"; $logini = "bli"; $passi = "bla"; $query = "blu";



$str_search = '"wort1","wort2","wort3"';

$int_search_words = 3;



$db = mysql_connect($hosti, $logini, $passi);



$sql1="select wort_id from wort where wort in ($str_search)"; 

// Voraussetzung fürs Funktionieren: jedes Wort darf in der Tabelle nur 1x vorkommen



$res = mysql_db_query($query, $sql1);

$num = mysql_num_rows($res);



if($num==$int_search_words) // Damit ist bestätigt, dass die wort-Tabelle alle Suchausdrücke beinhaltet

{



// Liste der wort_id der gesuchten Wörter

for ($i=0; $i<$num; $i)

$temp[]=mysql_result($res, $i, "wort_id"); // mit fetch_??? kann man den array vllt. direkt erhalten



// Syntax neue query:

// select s.url, 

// a1.seite_id, a2.seite_id, a3.seite_id from relation a1, relation a2, relation a3, seite s

// where a1.wort_id=$temp[0] and a2.wort_id=$temp[1] and a3.wort_id=$temp[2] and 

// a1.seite_id=a2.seite_id and a1.seite_id=a3.seite_id

// and a1.seite_id=s.seite_id

// order by irgendwas



// Das musst du die query noch mit php-schleifen ausarbeiten, wenn es variabel in funktion der anzahl gesuchten 

// Ausdrücke sein soll

// Idee: du machst für jedes gesuchte Wort eine "Kopie" von relation auf, suchst darin das wort und überprüfst, ob die 

// zugehörige seite_id auch in allen "Kopien" die gleiche ist



// hier dann die Ausgabe



}



else 

echo "keine Beiträge gefunden, die alle gesuchten Wörter $str_search enthalten";



mysql_close($db);

Wahrscheinlich kann man mit diesen Equi-joins auch noch die wort_id suche auch noch gleich in die Query packen.
Jedenfalls würde es mich interessieren, falls das hier jemand schafft.

mfg

**hOk** · 01.09.2002, 23:09

erstmal vielen Dank für die inspirierenden Ansätze.

Das muss doch auch mit einer Query zu schaffen sein?

Ich erläutere nochmal meinen Ansatz, der ja funktioniert,
davon abgesehen das manchmal bei der Suche, nach
Seiten in denen an die 10 Wörter vorkommen sollen,
schwachsinnige Ergebnisse auftauchen.

Ich versuch mal, meinen Ansatz zu erläutern...

Code:

# String fuer die WHERE IN Bedingung mit den Wörtern
# die in der Seite auftauchen sollen
$str_search = '"wort1","wort2","wort3"';
# Anzahl der Wörter die in der Seite auftauchen sollen
$int_search_words = 3;
$result = mysql_query('
SELECT 
# Anzahl des Auftretens einer Seite in der Ergebnissmenge
# -> In der Ergebnissmenge kann eine Seite öfters auftauchen,
# da eine Seite mehrere der gesuchten Wörter enthalten kann.
# Somit: Wenn anz == $int_search_words -> Seite mit allen 
# Suchwörtern. 
# Hier passiert es manchmal und für mich komischerweise, 
# dass anz größer ist als $int_search_words
COUNT(s.s_url) as anz,
s.s_url as url, 
# Ergebnissmenge aller Wörter
FROM wort w 
# Ergebnissmenge aller Wortseitenkombinationen.
# -> Seiten tauchen öfters auf, da eine Seite viele Wörter
# enthält
LEFT JOIN rel_seite_wort r 
USING(w_id) 
# Ergebnissmenge aller Wortseitenkombinationen ergänzt mit 
# den entsprechenden Seiteninformationen
LEFT JOIN seite s
USING(s_id) 
# Filtere die jetzige Ergebnissmenge auf Zeilen die eines der
# gesuchten Wörter enthalten
WHERE w.w_wort IN ('.$str_search.') 
# Gruppiere gleiche Seiten,
# dabei müsste jetzt der Wert für anz entstehen,
# je nach dem wie oft eine Seite in der Ergebnissmenge
# ist. 
GROUP BY s.s_id 
# Gib nur die Seiten aus bei denen $int_search_words == anz
# ist. -> Seiten die alle gesuchten Wörter enthalten haben
# und daher vor dem GROUP BY mehrfach in der Ergebniss-
# menge vorhanden waren, da sie in der WHERE IN Bedingung
# öfters gefunden wurden.
HAVING anz = '.$int_search_words.' 
);

...dass funktioniert ja fast immer perfekt, irgendwo muss da
ein kleiner Denkfehler sein.

Schön an der Abfrage ist, dass Sie wirklich sehr schnell ist,
ca. 1ms bei insgesammt 600.000 Tabellenzeilen.

...also das muss doch irgendwie gehen...;-)

Hier der genaue Tabellenaufbau

Code:

#
# Tabellenstruktur für Tabelle `rel_seite_wort`
#

CREATE TABLE rel_seite_wort (
  s_id mediumint(8) unsigned NOT NULL default '0',    # Seiten-ID
  w_id int(10) unsigned NOT NULL default '0',             # Wort-ID
  w_anzahl tinyint(3) unsigned NOT NULL default '0', # Anzahl des Auftretens eines Wortes in einer Seite
  KEY w_id (w_id),
  KEY s_id (s_id)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Tabellenstruktur für Tabelle `seite`
#

CREATE TABLE seite (
  s_id mediumint(8) unsigned NOT NULL auto_increment, # Seiten-ID
  s_url varchar(255) NOT NULL default '',         # URL
  s_titel varchar(255) default NULL,                 # Seitentitel
  s_beschreibung varchar(255) default NULL, # Seitenbeschreibung
  s_modified varchar(255) NOT NULL default '', # Letzte Aenderung
  s_status tinyint(4) NOT NULL default '0',        # Indizierungsstatus
  PRIMARY KEY  (s_id),
  KEY url (s_url)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Tabellenstruktur für Tabelle `wort`
#

CREATE TABLE wort (
  w_id int(10) unsigned NOT NULL auto_increment, # Wort-ID
  w_wort varchar(255) NOT NULL default '',             # Wort
  PRIMARY KEY  (w_id),
  KEY w_wort (w_wort)
) TY

P.S beinhalten die Felder wort_id und seiten_id jeweils nur einen EINZIGEN Wert?

Ja, in der Seiten- und in der Worttabelle sind die Spalten unique,
in der rel_seite_worttabelle sind sie nur in ihrer Kombination
unique.

viele nette Grüße, Holger

**CHnuschti** · 04.09.2002, 17:14

Hallo.
Ich hab nun mal interessenshalber/übungshalber die Thematik mal angeschaut und ein analoges indiziertes Find-DB-System gemacht. 70000 Wörter/500000 Relationen/10000 Seiten (jede Seite hat 50 "Random"-Wörter).

Nun muss ich zurückbuchstabieren, deine a) Query funktioniert bei mir perfekt, meine Aussage betr. COUNT stimmt nicht. a) ergibt auch *immer* die identischen Resultate wie wenn ich mit meiner b) Query-Lösung suche. "Anzahl" wird bei mir, soweit getestet, nie grösser als die Anzahl Suchbegriffe.

Aber deine Query a) ist um Längen schneller als b). Z.B. werden 50 Suchbegriffe mit a) in 2s, mit b) in 2min!! gefunden.
a) scheint also optimal zu sein! Zudem hat sie ja auch noch die weiteren Treffer, wo nicht alle Suchbegriffe gefunden werden (wenn man das having weglässt). Ist für mich erstaunlich, denn b) sucht eigentlich exakt, während a) alles mögliche herankarrt und erst am Schluss filtert.
Wenn a) noch so modifiziert werden kann, dass auch "LIKE"-Wörter gefunden werden, dann ist sie perfekt.

BTW habe ich nun was punkto Left-join dazugelernt

Deinen Fehler kann ich mir nur dadurch erklären, dass du doppelte Einträge in den Relationen und/oder in den Wörter hast.
Das A und O ist bei diesem System ja wohl das richtige Nachführen der Tables wort&relation.

Ich nehm an, du hast eine Routine, wo du die Seiten-Texte sezierst und in die Tables wort & relation einträgst. Könntest du diese mal posten? V.A. das "Säubern" des Textes würde mich interessieren.

mfg

**hOk** · 04.09.2002, 23:02

Original geschrieben von CHnuschti

Das A und O ist bei diesem System ja wohl das richtige Nachführen der Tables wort&relation.

...erklär mir mal bitte was du damit meinst, bin nämlich
auch über die Performanz überrascht und verwundert.

Das LIKE kann glaub ich Probleme machen, den wenn
in einer Seite die Wörter: "auto" und "autobahn"
stehen. Und nach "auto" gesucht wird, ist anz = 2
und in der HAVING-Bedingung wird anz auf Gleichheit
mit 1 geprüft, was meinst du?

Ich denke auch das der Fehler irgendwie mit Falsch
einträgen in der Verbindungstabelle zusammenhängt.

Hast du, oder ein anderer eine Idee für eine Abfrage
auf die rel_seite_tabelle, die nur Zeilen ausgibt, die
mehrfach vorhanden sind???
(Genau das darf nämlich nicht sein, und ich kann nicht
500.000 Datensätze vergleichen, keine Zeit...;-))

Ach ja, für Suchmaschinen soll sich ja auch die
MATCH AGAINST Abfrage eignen, doch da würden mir
die Tabellen zu groß, wenn ich die ganze Seite, in
einem Feld speichere.

Hier die Wörterfilterung die ich verwende:

Code:

// entferne alle HTML-Tags
//$str_seite = strip_tags($str_seite);
$str_seite = preg_replace('=<.*>=Us', ' ', $str_seite);

// ersetze alle [\\\\,\.\:\)\(\{\}\=\[\]\/\+\-\"\'] durch einfache Leerzeichen
$str_seite = preg_replace('§[\\\\,\.\:\)\(\{\}\=\[\]\/\+\"\']§', ' ', $str_seite);

// entferne alle &quot; &lt; &gt; nbsp;
$str_seite = str_replace('&quot;', ' ', $str_seite);
$str_seite = str_replace('&lt;', ' ', $str_seite);
$str_seite = str_replace('&gt;', ' ', $str_seite);
$str_seite = str_replace('&nbsp;', ' ', $str_seite);

// entferne alle Semikolon die nicht zu einem HTML_Sonderzeichen gehoeren
$str_seite = preg_replace('=(?<!uml|szlig);=', ' ', $str_seite);

// entferne alle & die nicht zu einem HTML_Sonderzeichen gehoeren
$str_seite = preg_replace('=&(?!.uml|szlig)=', ' ', $str_seite);

// entferne alle uerberfluessigen Whitespaces
$str_seite = preg_replace('=\s{2,}=', ' ', $str_seite);

// wandle alles in kleine Buchstaben
$str_seite = strtolower($str_seite);

// baue ein Array aus den vorhanden Woertern
$arr_seite = explode(' ', $str_seite);

// durchlaufe das Array und...
foreach ($arr_seite as $key => $val) {
	// ...entferne alle Elemente die nicht mit [a-z_] 
	// oder einem Umlaut anfangen
	if (!preg_match('=^[a-z_&]=', $val)) {
		unset($arr_seite[$key]);
		continue;
	}
	// ...entferne alle Element die kuerzer als drei Zeichen lang sind
	if (strlen($val) < $conf['shortest_word']) unset($arr_seite[$key]);
}

// Zaehle die Werte des Arrays und verwende Sie als index
// der neue Wert ist dann die Anzahl des vorkommens
$arr_seite = array_count_values($arr_seite);

netten Gruß, Holger

**CHnuschti** · 05.09.2002, 00:15

Hallo.
Vielen Dank für den "Reinigungs"-Code. Den übernehme ich doch gleich

Dachte ich mir doch, dass es eine längere Geschichte ist, den Text vom "Müll" zu befreien und zu zerlegen

Wg. den doppelten Einträgen würde ich ein kurzes Script schreiben und array_unique() anwenden, per Definition sollten dann alle doppelten Einträge getilgt werden (jeweils einer bleibt). Dann alle DB-Datensätze löschen und mit Schleife & Insert neu in die DB reinschreiben (NICHT! Update). Bei mir dauerte es für 500'000 Einträge nur 5-10min. Ob der Array soviele Datensätze packt ist aber eine andere Frage

, lässt sich aber einfach überprüfen.
EDIT: mit SELECT DISTINCT * FROM auslesen gehts noch kürzer
EDIT2: mit INSERT INTO ... SELECT ... gehts sogar "on the fly", allerdings in eine 2 Tabelle, die du dann wieder umbenennen kannst.
http://www.mysql.com/documentation/m...#INSERT_SELECT

Beim "normalen" Eintragen in Relation & Wort kannst du ja noch eine Prüfung einbauen, die prüft, ob der Eintrag tatsächlich unique ist.

Wg. dem "LIKE", m.E. sollte es keine Probleme geben. Du änderst einfach die Query beim Where ab:
WHERE wort like 'string' OR wort like 'string2' OR wort like 'string3' usw.
Dann gibt es zwar mehr Treffer (z.B. finde auto automat und automobil), aber das ist ja egal, weil es bleibt ja alles beim eigentlichen Sinn der Suche.
Am Schluss der Query machst du einfach ORDER BY anzahl DESC (ohne HAVING=...), damit hast du dann die besten Trefferquoten; mit dem having > kannst du immer noch Einschränkungen machen.

mfg

**hOk** · 05.09.2002, 09:08

Guten Morgen, danke für die Antwort,
dass muss doch auch wieder mit einer
Query gehen...;-)

Ich werde mir noch mal ein paar gedanken
machen, wie man die Dupletten finden
könnte.

Wenn ich das richtig sehe, funtioniert die,
LIKE-Abfrage, aber nur wenn man nach
Seiten sucht in denen ein Wort vorhanden
ist. Sollen mehrere Wörter vorhanden sein
ist man ja auf die HAVING-Bedingung an-
gewiesen, oder?

Ich mache mal einen Versuch wie man
es Anstellen könnte Dupletten zu finden:
SELECT r1.*, COUNT(r1.s_id) as anz
FROM rel_seite_wort r1, rel_seite_wort r2
WHERE r1.s_id = r2.s_id
AND r1.w_id = r2.w_id
GROUP BY s_id
HAVING anz > 1

...damed, da rechnet sich MySQL doof,
ich mache mal einen neuen Thread auf,
das muss doch gehen...;-)

netten Gruß, Holger

**hOk** · 06.09.2002, 12:27

Hi,
LEFT JOIN macht doch nur Sinn, wenn man in
der rechten Tabelle eventuell keinen entsprechenden
Eintrag hat, habe es jetzt mal mit einem INNER JOIN
probiert und es funktioniert ebenso gut, oder verstehe
ich da was falsch, und der LEFT JOIN wäre vorzuziehen?
mfg, Holger

**CHnuschti** · 06.09.2002, 21:34

Der Vollständigkeits halber:

"Meine" Idee der Query wie ein paar Post oben dargestellt ist auch in einer einzigen Query machbar (Bsp. 4 Suchbegr.):

SELECT t.text_id FROM text t, relation r0, woerter2 w0, relation r1, woerter2 w1, relation r2, woerter2 w2, relation r3, woerter2 w3
WHERE w0.wort = 'abgeordnetenbänke' AND w1.wort = 'ableseperiode' AND w2.wort = 'abt' AND w3.wort = 'abtastrate'
AND r0.wort_id=w0.wort_id AND r1.wort_id=w1.wort_id AND r2.wort_id=w2.wort_id AND r3.wort_id=w3.wort_id
AND r0.text_id=r1.text_id AND r0.text_id=r2.text_id AND r0.text_id=r3.text_id
AND t.text_id=r0.text_id
ORDER BY t.text_id

Ist aber wie gesagt ca. 20x langsamer als die andere hier dargestellte. Zudem kann damit nur "exakt" gefunden werden, d.h. würde man in der 3.Zeile OR setzen anst. AND funktioniert sie nicht mehr. Auf den ersten Blick siehts so aus, als würden damit auch Treffer mit weniger Suchbegriffen gefunden.

****
Deine o.g. Query a) lässt sich jedenfalls einfach umbauen, damit auch weniger Suchbegriffe und eine grössere Trefferzahl erzielt werden kann:
indem man das WHERE wort IN ('...) ersetzt mit
WHERE wort LIKE 'str1' OR wort LIKE 'str2' ...
Mit 'str1%' bzw. '%str1%' lässt sich die Suche zudem noch ausdehnen. Läuft bei mir prima.

EDIT.
Betr. dem LEFT/INNER JOIN bin ich auch deiner Meinung. LEFT JOIN ist (bei meinem heutigen Wissenstand

)nur sinnvoll, wenn in der gelinkten Table "Leerstellen" gewärtigt werden müssen.

mfg

**hOk** · 07.09.2002, 14:32

Cool, danke für die Infos,
was ist mit dem folgendem Problemfall
bei einer LIKE-Abfrage,
wie siehst du die Geschichte?

...wenn
in einer Seite die Wörter: "auto" und "autobahn"
stehen. Und nach "auto" gesucht wird, ist anz = 2
und in der HAVING-Bedingung wird anz auf Gleichheit
mit 1 geprüft...

mfg, hOk

n:m - Verknüpfung -> Woher kommen diese Anomalien