SQL 2 tables, InnoDB und Fulltext, wie muss query aussehen?

[KingLz]

@dreivier
Kannst auch USING nehmen. (vergessen dass es das auch gibt)
Du kannst vermutlich sogar einfach NATURAL JOIN nehmen ohne Spaltenangabe und er wird das korrekt joinen.
Genauso gehe ich davon aus, dass es sich hier immer um INNER JOINS handelt, also nur Einträge gejoint werden wo beide Seite existieren und nicht mit NULL Zeilen fehlende Partner aufgefüllt.

Der Join ist hier auch nicht der kritische Punkt, sondern die Auswahl der Zeilen.

 

[dreivier]

@KingLz
Ich werde es mal ohne Volltext ausprobieren, ich suche ja quasi nur nach Stadtnamen oder Strassennamen oder Bezirke. Viel Text ist ja da nicht wo sich die Suche durch z.B. 5.000-10.000 Zeichen wühlen muss. Das sind wenn es hochkommt maximal 200 Zeichen je Spalte, und die längsten Texte kommen bei der Beschreibung des Bezirkes bzw. Stadtteiles vor, doch wer sucht schon nach Berlin schöner Park mit 2 großen Laternen?
Mir ging es immer um die Schnelligkeit, weil ich immer gelesen habe das Volltext durch die Indexe schneller ist als die normale LIKE suche.

EDIT
Keine Chance, ich bekomme das nicht hin. ich möchte hier jetzt kein CODE posten weil es nichts bringt, das Problem ist nicht einfach zu lösen, ich werde jemanden suchen der mir das günstig macht denn es bringt mir nichts wenn ich jetzt Tage oder sogar Woche damit verbringe eine popelige Suche zu programmieren und nicht voran komme. Und dann imme rnoch nicht weiß ob das so auch richtig ist.

Ich danke euch trotzdem für die Hilfe.
Von mir aus kann das hier zu.

 

[Lar337]

Ich suche hier immer noch die Fulltext-Suche (LIKE %*% ist keine).

In wiefern würde das hier denn nicht die gewünschten Eigenschaften aufweisen?

...
WHERE Stadt.Name + ' ' + Stadtteil.Name
LIKE '%Berlin Kreuzberg%';

Sowohl die Suchbegriffe "Berlin", "Kreuzberg" als auch "Berlin Kreuzberg" würden das Ergebnis "Berlin Kreuzberg" treffen.

Dass dies aus Performancegründen trotzdem keine gute Lösung ist, habe ich ja gesagt.
Daher hatte ich ja nach der "trivialen Lösung" gefragt, die es angeblich geben soll.

 

[GroMag]

Sowohl die Suchbegriffe "Berlin", "Kreuzberg" als auch "Berlin Kreuzberg" würden das Ergebnis "Berlin Kreuzberg" treffen.

Und was ist mit "Berlin, Kreuzberg"? Oder "Kreuzberg Berlin"?
Fulltext search in MySql/MariaDB benötigt einen Fulltext Index und "match () against ()".

Dein Beispiel aus Post #5 ist übrigens auch sehr schlecht, weil es auf das (temporäre) Feld in der Where-Bedingung keinen Index nutzen kann.
Wenn dann

WHERE stadtteil.name = 'Kreuzberg'
    AND stadt.name = 'Berlin';

[edit]
Welche Methode mal wählt ist immer fallabhängig. Wenn man in der linken Tabelle schon auf wenige selektieren kann (sprich die where schliesst da schon viel aus) ist ein Join das Richtige. (Ein Join auf eine Tabelle mit Millionen Einträgen, nur um in der rechten Tabelle dann 10 zu selektieren ist perf.tech ein richtiger Spaß)
Wenn man aber eher in der rechten Tabelle selektiert (und man sicher stellen kann, dass die rechte Tabelle nicht zu viele results hat) ist z.B. eher ein IN (subquery) sinnvoll. Der Knackpunkt hier ist, dass mySql keine table-übergreifenden Indexe nutzen kann, also versucht man zuerst die Tabelle zu wählen die am meisten vom Index profitiert und/oder am wenigsten Treffer liefert. Hier kann aber auch die Reihenfolge vom Join entscheidend sein, also statt IN einfach rechts und links vertauschen, kann auch schon viel bringen.

EDIT: Ich überlege gerade, ob das am Fulltext Index liegen könnte?

Funktioniert halt nicht mit LIKE.

Mysql ist bei richtigem fulltext search eh etwas suboptimal. Wenn man das viel machen muss, hilft eine search engine. Aber das ist hier dann wohl doch eher die Kanone^^

Dass dies aus Performancegründen trotzdem keine gute Lösung ist, habe ich ja gesagt.

Ja, vor allem wegen dem leading %.

name, stadtteil, stadtteilinfo haben fulltext index

FT sparsam einsetzen. Nur dort, wo es wirklich sinnvoll ist. Und hier im Thread sehe ich keinen Nutzen.
FT ist dann sinnvoll, wenn in Dokumenten/Texten gesucht werden muss. Hier würde ich mich eher auf klassische B-Tree-Indexe verlassen und mit "= 'x'" oder "LIKE 'x%'" suchen. MySql kann auch keine FT-Indexe verknüpfen!

 

[dreivier]

@Lar337
Ich habe das gestern Abend noch paar mal getestet, da kommt leider nichts an, es gibt keine Fehlermeldung sondern das Ergebnis ist NULL also ,0,. Es wird nichts in keiner der beiden Tabellen was gefunde, und ich habe das so übernommen von dir und auch hin und her verändert ausprobiert.

Edit: Ja jetzt weiß ich auch warum wegen dem LIKE, was laut @GroMag ja nicht funktioniert in Fulltext..

 

[GroMag]

Ich habe das gestern Abend noch paar mal getestet, da kommt leider nichts an, es gibt keine Fehlermeldung sondern das Ergebnis ist NULL also ,0,.

Wie genau lautet das Query?

 

[KingLz]

@dreivier

Ich wollte grad dir nen sql fiddle zusammenbasteln, da habe ich gesehen, dass das Tabellenschema sehr komisch ist.

Aktuell besitzt jede Stadt einen Stadtteil und nur einen. D.h. du musst jeden Stadtnamen mehrmals in die Tabelle einfügen, soll das so? Eigentlich muss das doch genau anders herum, oder? Jeder Stadtteil gehört eindeutig zu einer Stadt.

Ich bau dir mal was zusammen....

 

[dreivier]

D.h. du musst jeden Stadtnamen mehrmals in die Tabelle einfügen, soll das so?

Wo siehst du das? muss eben nicht darum hab ich ja die Stadtnamen ja in einer seperaten Tabelle ausgelagert.

staedte und stadtteile haben eine identische spalte [idt] darin sind Nummern, anhand dieser wird das passende mit JOIN USING(idt) verbunden.

Also so:
Tabelle1
Berlin [idt = 1]
Bonn [idt = 2]
Koeln [idt =3]
...
München [idt = 328]
...
Tabelle2
Kreuzberg [idt=1]
...
...

In der query wird dann mit JOIN stadtteile USING(idt) verbunden...suche ich also nach München, kommt als Ausgabe München und alle Stadtteile die als idt die Nummer 328 haben.

 

[GroMag]

weil

Tabelle ,,staedte,, Spalten: id int(11) //primary key unique name varchar(50) idt int(11) // Tabelle ,,stadtteile,, Spalten: idt int(11) //primary key unique stadtteil varchar(100) stadtteilinfo text(2000)

"idt" in staedte ist falsch.
Wenn, dann muss es eine Fremdschlüsselspalte StadtID in Stadtteile geben
(ist mir vorher gar nicht aufgefallen^^)

Das ist eine 1:n-Beziehung
also eine Stadt kann n Stadtteile enthalten

SELECT *
FROM staedte s
LEFT JOIN stadtteile st ON s.ID = st.sID
WHERE s.Name = 'BERLIN'
    AND st.stadtteil = 'Kreuzberg'

sID ist der Fremdschlüssel, also die PrimärID aus staedte

edit:

SELECT *
FROM stadtteile st
RIGHT JOIN staedte st ON st.sID = s.sID
WHERE s.Name = 'BERLIN'
    AND st.stadtteil = 'Kreuzberg'

So rum ist es vllt besser.


edit2:
Rein von der Aufgabenstellung aus Post #1, willst du also einfach nur alle Stadtteile aus einer bestimmten Stadt?
Wenn ja:

SELECT *
FROM stadtteile st
WHERE st.sID IN (
  SELECT ID
  FROM staedte
  WHERE Name = 'Berlin'
)

M.M.n. hier die beste Variante, weil das Subselect eigentlich nie mehr als einen Treffer liefern sollte. Kommt halt auf die Aufgabenstellung an. Willst du z.B nur ein Dropdown mit Stadtteilen befüllen nach der Wahl der Stadt.

 

[dreivier]

@GroMag
Ja ich hab es falsch hier hingeschrieben...ID gibt es nicht, idt ist in staedte primary key unique...

Hab das jetzt glaub ich, ich muss aber noch testen...

$suchen = "Kreuzberg Berlin";

$query = mysqli_query($_connect, "SELECT `name` FROM `staedte` JOIN `stadtteile` USING (idt) WHERE MATCH(`name`) AGAINST('".$suchen."') OR MATCH(`stadtteile`, `stadtteilinfo`) AGAINST('".$suchen."')");

 

[Lar337]

@Lar337
Ich habe das gestern Abend noch paar mal getestet, da kommt leider nichts an, es gibt keine Fehlermeldung sondern das Ergebnis ist NULL also ,0,. Es wird nichts in keiner der beiden Tabellen was gefunde, und ich habe das so übernommen von dir und auch hin und her verändert ausprobiert.

Edit: Ja jetzt weiß ich auch warum wegen dem LIKE, was laut @GroMag ja nicht funktioniert in Fulltext..

Doch, das funktioniert sehr wohl, eben mit der von @GroMag genannten Einschränkung, dass es keine perfekte Volltextsuche ist, sondern nur die 3 von dir genannten Suchmöglichkeiten "Berlin", "Kreuzberg" und "Berlin Kreuzberg" matcht.

Übrigens habe ich die "Lösung" mit dem Satz "Das hier ist zumindest keine gute Lösung:" überschrieben, weil es eben KEINE gute Lösung ist
Eben schon alleine aus dem Grund, dass jeglicher Index versagt und die Performance daher bei sehr großen Tabellen schlecht ist.
Die Performance könnte man vielleicht sogar retten, indem man die Bedingungen der WHERE-Clause etwas erweitert, um einen Indexscan zu ermöglichen.
Beispielsweise mittels

SELECT
    *
FROM Staedte
INNER JOIN Stadtteile ON Stadtteile.idt = Staedte.idt -- so sind die Daten in dem Beispiel glaube ich
WHERE
Staedte.Name in ('Berlin', 'Kreuzberg') -- Das dürfte völlig ausreichen, um die Performance zu retten
AND Stadte.Name + ' ' + Stadtteile.Name like '%Berlin Kreuzberg%';

Aber das sollte wie gesagt eigentlich kein Lösungsvorschlag sein, sondern nur zeigen, dass die offentliche Lösung eigentlich keine ist und ich daher noch immer auf die angekündigte triviale Lösung von @abcddcba warte.

Für eine korrekte Lösung müsste man vermutlich auf DBMS-Spezifische Funktionen zurückgreifen und kommt alleine mit SQL nicht zum perfekten Ergebnis. Dafür kenne ich mich aber mit INNODB zu wenig aus.

 

[KingLz]

http://sqlfiddle.com/#!9/446622

So hier mal was mit dem man arbeiten kann.
Die 3 Queries sollten prinzipiell alle 3 Möglichkeiten abdecken. Jetzt muss man sie nur noch vernünftig zusammensetzen, denk ich mal.
Fiddle lässt mich leider keine with Clauses verwenden, so dass das etwas nervig ist.
Aber prinzipiell sollte es irgendwie gehen mit count(Stadt + Stadtteil) == 0, dann nehm 2. query, wenn 2. query count ==0 dann no 3.

Edit: Link war falsch mit Session, mom
Edit: Irgendwie will das Teilen nicht so ganz, geht das Schema immerhin?

SET @userinput = "Hamburg,Legoland";

select *
from staedte
natural join stadtteile;


select *
from staedte
natural join stadtteile
where ((SELECT FIND_IN_SET(staedte.stadt_name, @userinput)) > 0 and
       (SELECT FIND_IN_SET(stadtteile.stadtteil_name, @userinput)) > 0);

select *
from staedte
natural join stadtteile
where (SELECT FIND_IN_SET(staedte.stadt_name, @userinput)) > 0;

select *
from staedte
natural join stadtteile
where (SELECT FIND_IN_SET(stadtteile.stadtteil_name, @userinput)) > 0;

 

[GroMag]

@GroMag
Ja ich hab es falsch hier hingeschrieben...ID gibt es nicht, idt ist in staedte primary key unique...

Hab das jetzt glaub ich, ich muss aber noch testen...

$suchen = "Kreuzberg Berlin";

$query = mysqli_query($_connect, "SELECT `name` FROM `staedte` JOIN `stadtteile` USING (idt) WHERE MATCH(`name`) AGAINST('".$suchen."') OR MATCH(`stadtteile`, `stadtteilinfo`) AGAINST('".$suchen."')");

Was mir sofort auffällt, sind code quality Geschichten.
1. PDO statt mysqli
2. vllt nur aus Kurzfassungsgründen, aber SQL-Variablen immer escapen (mysqli_real_escape() oder besser PDO Prepared Statements)
3. $sql = "Select foobar";
mysql_query($sql)
kann man besser lesen und debuggen.
und 4.
ich weiß nicht woher du das using hast, aber das ist hier fehl am Platze. Du hast hier einen 1:1 Join!

So hier mal was mit dem man arbeiten kann.

natural join ist eine cross join varianten, und eher zu vermeiden.
FIND_IN_SET ist auch der "overkill"

 

[dreivier]

@GroMag...klar escape ich, das sollte doch klar sein, nur warum soll ich jetzt alles unnötig aufblähen wenn das mit dem Problem nix zutun hat? abgesehen, hat der Rest der von dir bemängelt wird auch nichts mit dem Problem zutun..jeder programmiert anders und ein Anfänger macht eh alles falsch :-)

PDO, müsste dann viel umschreiben...kein Bock, mysqli ist schon ok, ich habe kein Amazon, auch habe ich keine Angst, wenn jemand meint spielen zu müssen soll er, ich leide nicht an Verfolgungswahn, meine Lösung für sowas lautet Backup. Was soll ich mir über sowas Gedanken machen? da kommst du dann nie wieder raus weil es ist nie sicher. Wer will und es drauf hat schafft es, sieht man bei vielen großen Seiten, ich bin winzig, niemand interessiert sich darum, und gegen die Spielkinder reicht auch ein mysqli_real_escape_string.

 

[KingLz]

natural join ist eine cross join varianten, und eher zu vermeiden.
FIND_IN_SET ist auch der "overkill"

Ist voll okay, ich bin eher bei anderen SQL Arten.
Google hatte als Ergebnis nur, dass MYSQL keinen SPLIT für String bestitzt und man deswegen find_in_set nehmen sollte. Irgendeine Suche im Array muss es geben, da wir ja alle möglich Kombintationen an Städten + Stadtteilen aus der Sucha ja abdecken müssen.

Und natural join kenne ich als "inner join" auf die alle gleichen Spaltennamen. Ist das bei Mysql nicht so?

 

[Lar337]

Nur als kleine Randbemerkung. MYSQL und somit auch InnoDB scheint das Zusammenfügen von Strings mittels + nicht zu mögen.
Man muss zwingend CONCAT verwenden (habe meine Lösung nur mit MSSQL getestet, weil ich das eh grad installiert habe)

Die reine SQL-Nicht-Lösung sähe für MySQL also so aus.

SELECT
    CONCAT(Stadte.Name, ' ', Stadtteile.Stadtteil)
FROM Stadte
INNER JOIN Stadtteile ON Stadtteile.idt = Stadte.idt -- so sind die Daten in dem Beispiel glaube ich
WHERE
Stadte.Name in ('Berlin', 'Kreuzberg') -- Das dürfte völlig ausreichen, um die Performance zu retten
AND CONCAT(Stadte.Name, ' ', Stadtteile.Stadtteil) like '%Berlin%Kreuzberg%';

 

[GroMag]

Und natural join kenne ich als "inner join" auf die alle gleichen Spaltennamen. Ist das bei Mysql nicht so?

Ich geb zu ist musste es nach googlen. Ich hatte im Kopf, es wäre ein cross. Scheint aber ein inner/left join zu sein. Ich benutze eigentlich fast nur left, benötigt man was anderes, hat man schon vorher Planungsfehler gemacht oder es ist ein seeehr spezielles Query.

Google hatte als Ergebnis nur, dass MYSQL keinen SPLIT für String bestitzt

Benötigt man auch nicht, für die Aufgabe reicht ein simples ID-Join

CONCAT(Stadte.Name, ' ', Stadtteile.Stadtteil)

Bitte komme von diesem Unsinn weg! Du musst keine String-Operationen machen, wenn du nur auf IDs gehen musst. Schaue in meine letzte Lösung von Post #29.

 

[KingLz]

Benötigt man auch nicht, für die Aufgabe reicht ein simples ID-Join

Sicher? Bei den Anforderungen die ich hier vernommen habe ist das kein 0815 Query, wenn man es rein in der DB lösen möchte, da nicht einmal bekannt ist was der User eingegeben hat, ob Stadt oder Stadtteil und in welcher Kombintation:

Also ich erwarte sowas als Ausgabe...

Ich gebe ,,BERLIN,, in das Formular ein:: die Ausgabe presentiert mir Berlin, alle Stadteile und die Beschreibungen zu den jeweiligen Stadteilen.

Ich gebe ,,BERLIN KREUZBERG,, in das Formular ein:: die Ausgabe presentiert mir nur Berlin - Kreuzberg und die Beschreibung zu dem Stadtteil.

Ich gebe ,,Kreuzberg,, in das Formualr ein:: die Asugabe presentiert mir alle Städte mit dem Stadteil Kreuzberg und die Beschreibungen zu den Stadteilen.

Ich gebe BERLIN BERLIN in das Formular ein:: die Suche ignoriert das doppelte Wort und gibt wie im ersten Beispiel Berlin und alle Stadtteile aus.

Nur Berlin also nur Stadt oder nur Stadteil soll nie ausgegeben werden, wäre ja auch irgendwie blöd...die letzte Variante ist nur um Fehler abzufangen, denn wie soll ich sowas abfangen wenn jemadn im Formular rumspielt und nur Berlin oder tausendmal Bonn eingibt?

Hier wie die Tabellen aufgebaut sind:

 

[Lar337]

Bitte komme von diesem Unsinn weg! Du musst keine String-Operationen machen, wenn du nur auf IDs gehen musst. Schaue in meine letzte Lösung von Post #29.

Auf welche Ids willst du denn gehen?
Du weißt nicht, welche Stadt und welcher Stadtteil, da die Info fehlt, ob der Benutzer die Stadt oder den Stadtteil oder beides eingegeben hat.
Keine deiner Lösungen in #29 erfüllt die gegebenen Anforderungen, nämlich dass "Berlin Kreuzberg" von den Eingaben "Berlin", "Kreuzberg" und "Berlin Kreuzberg" gefunden wird.

in der optimalen Lösung würde nun auch noch Kreuzberg, Berlin einen Treffer liefern. Aber da kommt man mit reinem SQL-Standard glaube ich nicht zum Ziel. Da müsste man wirklich die Fulltextmöglichkeiten der konkreten DBMS nutzen, aber da ich kein MySQL verwende, kann ich dir die Lösung dafür nicht sagen.

Außerdem wollte ich ja auch eigentlich nur der Aussage widersprechen, die Lösung sei so trivial, dass man nur die Doku zu JOIN lesen müsste.

 

[GroMag]

Mir ging es drum, dass es grundsätzlich falsch ist, ein temporäres Feld zu erzeugen (was du mit dem Stringverknüpfen tust) und dann darin zu suchen. Das ist perf.tech so ziemlich das aller letzte, was man tun sollte.
Wenn du mal eine DB mit 2-3stl. Mio Einträgen hast, wirst du es verstehen. (das DBMS muss dann jedes(!) Value erst berechnen, das ist wie mit Order by rand() )