Suchabfrage verbessern

[r. geiseler]

Hallo, an alle Profis der Joinabfragen.

ich benötige Hilfestellung bei dem Aufbau einer Suchabfrage, momentan benutze ich eine rigth join Abfrage, diese sucht mir alle Firmendaten aus meiner Haupttabelle, welche das Suchkriterium erfüllen. Derzeit ist das Suchkriterium allerdings eingeschränkt, nähmlich auf genau den vorhanden Begriff in einer Kategorie-Tabelle (Fulltext). Die Kategorien haben, genau wie die Firmentabelle eine ID mit Int-Typ sowie belegtem Index (Primär). Eine dritte Tabelle bildet dann die Verbindung (Foreign-Key). Nun möchte ich die Suche erweitern, undzwar soll die Möglichkeit bestehen auch Firmennamen im selben Suchfenster einzugeben, sowie in einem zweiten Suchfenster Orte oder PLZ gleich mitzufiltern.

Explain SELECT *
FROM company AS comp
RIGHT JOIN company_industry AS comp_ind ON comp.id = comp_ind.company_id
RIGHT JOIN industry AS ind ON comp_ind.industry_id = ind.id
WHERE
MATCH (ind.d_kat) AGAINST ('$Abbeizarbeiten')

Kann jemand helfen die Abfrage auszubauen oder anders zu gestalten?

CREATE TABLE `company` (
`ID` int(10) NOT NULL,
`COSTUMER_ID` varchar(10) NOT NULL,
`PASSWORD` text NOT NULL,
`SALUTATION` text NOT NULL,
`TITLE` text NOT NULL,
`FIRSTNAME` varchar(80) NOT NULL,
`COMPANYNAME` varchar(255) NOT NULL,
`STREET` varchar(255) NOT NULL,
`ZIPCODE` varchar(10) NOT NULL,
`LOCATION` varchar(50) NOT NULL,
`PHONEPREFIX` varchar(20) NOT NULL,
`PHONENUMBER` varchar(20) NOT NULL,
`FAXPREFIX` varchar(20) NOT NULL,
`FAXNUMBER` varchar(20) NOT NULL,
`MOBILEPREFIX` varchar(20) NOT NULL,
`MOBILNUMBER` varchar(20) NOT NULL,
`EMAIL` text NOT NULL,
`HOMEPAGE` text NOT NULL,
`STATE` text NOT NULL,
`COUNTRY` text NOT NULL,

PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

CREATE TABLE `company_industry` (
`COMPANY_ID` int(10) NOT NULL default '0',
`INDUSTRY_ID` int(10) NOT NULL default '0',
PRIMARY KEY (`COMPANY_ID`,`INDUSTRY_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

CREATE TABLE `industry` (
`id` int(10) NOT NULL default '0',
`d_kat` varchar(50) NOT NULL default '',

PRIMARY KEY (`id`),
FULLTEXT KEY `FULLTEXT_INDEX` (`d_kat`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ca. 2.000.000 Datensätze, PHPMyAdmin, SQL-DB, V-Server bei Strato

mfg
R. Geiseler

[dibo33]

Brauchst du doch dann nur noch mittels AND verknüpfen, wobei du dann auch entspr. NULL-Werte mit berücksichtigen musst

WHERE
MATCH (ind.d_kat) AGAINST ('$Abbeizarbeiten')
AND (comp.ZIPCODE = '12345' OR comp.ZIPCODE IS NULL)

oder

WHERE
MATCH (ind.d_kat) AGAINST ('$Abbeizarbeiten')
AND COALESCE(comp.ZIPCODE, '12345') = '12345'

Edit: oder lege die Bedingung im ON fest

FROM company AS comp
RIGHT JOIN company_industry AS comp_ind 
  ON comp.id = comp_ind.company_id AND comp.ZIPCODE = '12345'
RIGHT JOIN industry AS ind ON comp_ind.industry_id = ind.id

[r. geiseler]

Suchabfrage verbessern

Hallo Dibo33,

besten Dank erst mal. Hatte garnicht mitbekommen dass ich eine Anwort bekommen habe, sorry dass ich mich erst so spät melde.

Ja mitlerweile ist einiges passiert. Verwende jetzt folgende Abfrage:

SELECT
company.id
, company.Companyname
, company.STREET
, company.ZIPCODE
, company.Location
FROM company
WHERE MATCH (company.Companyname) AGAINST ('Afro')
AND company.id IN
(
SELECT company.id
FROM company
WHERE MATCH (company.Companyname) AGAINST ('Afro')
UNION
SELECT company.id
FROM company
INNER JOIN company_industry ON company.id =
company_industry.company_id
INNER JOIN industry ON company_industry.industry_id = industry.id
WHERE MATCH (industry.d_kat) AGAINST ('Afro'))

Da mein Suchfeld nicht statisch ist, muß ich zwei Abfragen durchführen, und zwar einmal wie bereits geschrieben mit Join über die drei Tab und dann noch in der Hauptatbelle im Namen, man weiß ja nicht was der Suchende eingibt.

Die erste Where Klausel funktioniert so wie ichs hab leider nicht korrekt, es werden jetzt nur Einträge gefunden, welche den Suchbegriff im Namen und gleichzeitig in der Kategorie enthalten, die welche ihn im Namen oder in der Kat enthalten werden nicht ausgegeben.

Kannst du helfen?

Gruß R. Geiseler

[dibo33]

Bitte nutze die CODE-Blöcke zur besseren Übersicht [ code ][ /code ] (jeweils ohne Leerzeichen)

es werden jetzt nur Einträge gefunden, welche den Suchbegriff im Namen und gleichzeitig in der Kategorie enthalten, die welche ihn im Namen oder in der Kat enthalten werden nicht ausgegeben.

Falsch, es werden die gefundenen aus der Kategorie wieder aussortiert, dies geschieht durch das erste MATCH in der Hauptabfrage, entferne es.

[Novi]

Bitte nutze die CODE-Blöcke zur besseren Übersicht [ code ][ /code ] (jeweils ohne Leerzeichen)

Oder am besten gleich eine Formatierung für Sql:

[highlight=sql][/highlight]

[r. geiseler]

Hallo dibo33,

habe ich versucht, dann dauert die Abfrage leider zu lange. Vieleicht hab ichs nicht richtig gemacht, kannst Du mir mal ein Beispiel geben.

habe ca. 1,5 millionen Datensätze, Indizies funktionieren alle gut.

Hatte eben versucht:

[/SELECT...FROM company
WHERE MATCH (company.Companyname) AGAINST ('Afro' IN BOOLEAN MODE)
and company.id IN
(SELECT company.id...]

gleiches Ergebnis, bist du sicher dass die Einträge gelöscht werden? Mit "IN BOOLEAN MODE" wär dies doch eigentlich ausgeschlossen.

mfg
r. Geiseler

[dibo33]

Da hast du einiges falsch verstanden.

Für die Formatierung des Codes schau mal hier
http://entwickler-forum.de/showthread.php?t=42101


Schauen wir jetzt mal deine Abfrage

[highlight=sql]
SELECT
company.id
, company.Companyname
, company.STREET
, company.ZIPCODE
, company.Location
FROM company
WHERE MATCH (company.Companyname) AGAINST ('Afro')
AND company.id IN
(
SELECT company.id
FROM company
WHERE MATCH (company.Companyname) AGAINST ('Afro')
UNION
SELECT company.id
FROM company
INNER JOIN company_industry ON company.id =
company_industry.company_id
INNER JOIN industry ON company_industry.industry_id = industry.id
WHERE MATCH (industry.d_kat) AGAINST ('Afro')
)
[/highlight]

Wenn du jetzt die innere Abfrage ausführst
[highlight=sql]
SELECT company.id
FROM company
WHERE MATCH (company.Companyname) AGAINST ('Afro')
UNION
SELECT company.id
FROM company
INNER JOIN company_industry ON company.id =
company_industry.company_id
INNER JOIN industry ON company_industry.industry_id = industry.id
WHERE MATCH (industry.d_kat) AGAINST ('Afro')
[/highlight]

Dann erhälst du ein Set von Ids
Diese filterst du wiederum in deiner oberen Abfrage
[highlight=sql]
WHERE MATCH (company.Companyname) AGAINST ('Afro')
AND company.id IN ( Result aus innerer Abfrage )
[/highlight]
Deine gefundenen Ids werden dann also nochmals mit MATCH (company.Companyname) AGAINST ('Afro') verglichen und alle gefundenen Ids welche dieser Bedingung nicht genügen wieder entfernt.
Also entferne das MATCH und frage nur ab
[highlight=sql]
WHERE company.id IN ( Result aus innerer Abfrage )
[/highlight]

Dein
[highlight=sql]
WHERE MATCH (company.Companyname) AGAINST ('Afro')
[/highlight]
hast du ja bereits in der inneren Abfrage ist also in der äusseren überflüssig.

[r. geiseler]

Hallo,

gut habe ich versucht, die Abfrage hängt sich aber auf, nach 15sec. habe ich abgebrochen. Explain zeigt, dass der Index auf Company jetzt nicht genutzt wird. (using filesort, using where)

Die Abfrage wie von dir vorgeschlagen, hoffe war kein Fehler drin, wär mir jetzt peinlich.

SELECT
company.id
, company.Companyname
, company.STREET
, company.ZIPCODE
, company.Location
FROM company
WHERE company.id IN
(SELECT company.id
FROM company
WHERE MATCH (company.Companyname) AGAINST ('abbeizarbeiten')
UNION
SELECT company.id
FROM company
INNER JOIN company_industry ON company.id = 
company_industry.company_id
INNER JOIN industry ON company_industry.industry_id = industry.id
WHERE MATCH (industry.d_kat) AGAINST ('abbeizarbeiten'))
ORDER BY company.Companyname

gruß r.geiseler

[dibo33]

Wenn ich das jetzt richtig interpretiere, versuche mal folgendes

[highlight=sql]
SELECT
company.id
, company.Companyname
, company.STREET
, company.ZIPCODE
, company.Location
FROM company
INNER JOIN company_industry
ON company.id = company_industry.company_id
INNER JOIN industry
ON company_industry.industry_id = industry.id
WHERE MATCH (company.Companyname) AGAINST ('abbeizarbeiten')
OR MATCH (industry.d_kat) AGAINST ('abbeizarbeiten')
ORDER BY company.Companyname
[/highlight]

[r. geiseler]

Danke erstmal, dass du dir solche Mühe machst.

Das war meine erste Abfrage, mit "or" leider keine Chance. 45sec. dauert die Abfrage. Der Index auf Company wird nicht genutzt, Using Filesort.

In der Folge hatte ich mehrere unterschiedliche Abfragen versucht, die einzige welche wirklich funktioniert ist mit Union. Da kam ich dann aber an einen Punkt wo ich am ende order by benötigte, wegen der Sortierung aufsteigend und da war es dann mit der schönen schnellen Abfrage wieder vorbei.

Naja nun bekam ich den jetzigen Vorschlag, also dass die eigentliche Abfrage als innere ausgeführt wird und anschließend das Ergebnis nochmals abgefragt wird und durch order by sortiert wird. Alle Indizes gehen ja auch sauber und die Abfrage ist nochmal schneller als die letzte, aber jetzt hab ich das bekannte Problem das nicht alle rausgeholt werden.

[dibo33]

Ich glaube nicht, das du es viel schneller bekommst, das filesort bremst aus.

Mir fällt allerhöchstens noch das Konstrukt ein
[highlight=sql]
SELECT
company.id
, company.Companyname
, company.STREET
, company.ZIPCODE
, company.Location
FROM (
SELECT
company.id
, company.Companyname
, company.STREET
, company.ZIPCODE
, company.Location
FROM company
WHERE MATCH (company.Companyname) AGAINST ('abbeizarbeiten')
UNION
SELECT
company.id
, company.Companyname
, company.STREET
, company.ZIPCODE
, company.Location
FROM company
INNER JOIN company_industry
ON company.id = company_industry.company_id
INNER JOIN industry
ON company_industry.industry_id = industry.id
WHERE MATCH (industry.d_kat) AGAINST ('abbeizarbeiten')
) company
ORDER BY company.Companyname
[/highlight]

[r. geiseler]

Leider wird so jetzt der Index von Company_industry nicht genutz, aber irgendwie könnte dass der richtige Weg sein.

Im Grunde wird auch Angezeigt, dass 112 Ergebnisse entstanden sind, also mit Explain meine ich. Letztendlich wird ja dann mit diesem Ergebnis die sortierung durchgeführt. Hmm naja mein Kopf is dicht, erstmal ne nacht drüber schlafen, bin jetzt seit zwei monaten verzweifelt auf der Suche nach der Lösung, kommt es jetzt auf ein paar Tage nicht mehr an.

Naja reicht für heute, besten Dank erstmal, vieleicht hörn wir uns morgen ja noch mal, falls du zeit hast.

gruß

R.geiseler

[dibo33]

Dann musst du weiter forschen
http://dev.mysql.com/doc/refman/5.1/de/explain.html

Wenn das Problem auftritt, dass Indizes nicht benutzt werden, obwohl Sie annehmen, dass dies eigentlich der Fall sein sollte, dann führen Sie ANALYZE TABLE aus, um die Tabellenstatistiken wie etwa die Kardinalität der Schlüssel, die sich auf die durch den Optimierer vorgenommene Auswahl auswirken kann, zu aktualisieren. Siehe auch Abschnitt 13.5.2.1, „ANALYZE TABLE“.

Wenn das Schlüsselwort EXTENDED verwendet wird, erzeugt EXPLAIN zusätzliche Informationen, die durch Absetzen einer SHOW WARNINGS-Anweisung nach der EXPLAIN-Anweisung angezeigt werden können. Diese Informationen geben Hinweise zum Optimierungsprozess, z. B. wie der Optimierer Tabellen- und Spaltennamen in der SELECT-Anweisung qualifiziert oder wie die SELECT-Anweisung nach der Anwendung der Umformulierungs- und Optimierungsregeln aussieht.

Auch interessant
http://newsgroups.derkeiler.com/pdf/...1/msg00119.pdf

[r. geiseler]

Hallo Dibo,

besten Dank für deine Unterstützung. Ja diese ganzen Dinge habe ich schon zu genüge gelesen, bin leider nicht der Profi, verstehe aber die Problematik mit den halbwegs. Kann dies nur häufig nicht umsetzen. Wollte dir mal die folgende Abfrage zeigen, diese funktioniert super, alle Indizes werden genutz. Wenn aber am Ende OrderBy stehen würde bricht die Abfrage zusammen.

Vieleicht hast du ne Idee.

Explain SELECT comp.id
, comp.Companyname
, comp.STREET
, comp.ZIPCODE
, comp.Location
FROM company AS comp
INNER JOIN company_industry AS comp_ind ON comp.id = comp_ind.company_id
INNER JOIN industry AS ind ON comp_ind.industry_id = ind.id
WHERE MATCH (comp.location) AGAINST ('Hamburg') and
(MATCH (comp.Companyname) AGAINST ('Afro'))
UNION
SELECT comp.id
, comp.Companyname
, comp.STREET
, comp.ZIPCODE
, comp.Location
FROM company AS comp
INNER JOIN company_industry AS comp_ind ON comp.id = comp_ind.company_id
INNER JOIN industry AS ind ON comp_ind.industry_id = ind.id
WHERE MATCH (comp.location) AGAINST ('Hamburg') and
(MATCH (ind.d_kat) AGAINST ('Afro'))

Gruß Ronny

[Falk Prüfer]

Hallo,

warum verwendest du für diese Abfrage ein UNION? Ein UNION beinhaltet immer auch ein DISTINCT und wenn dann noch sortiert werden muß, kann dafür kaum ein Index verwendet werden, da es zwei getrennte Datenmengen sind.
Da du ja schon eine Volltextsuche verwendest, wäre doch sowas günstiger!?

[highlight=sql]
SELECT comp.id
, comp.Companyname
, comp.STREET
, comp.ZIPCODE
, comp.Location
FROM company AS comp
INNER JOIN company_industry AS comp_ind ON comp.id = comp_ind.company_id
INNER JOIN industry AS ind ON comp_ind.industry_id = ind.id
WHERE MATCH (comp.location) AGAINST ('Hamburg') and
(MATCH (comp.Companyname, ind.d_kat) AGAINST ('Afro'))
[/highlight]

Gruß Falk