✔ MySQL-Suche legt MySQL lahm

[splat]

Hi,

ich habe 4 Tabellen die ich mit einer einzigen Suche abfrage. Die Tabellenstruktur sieht wie folgt aus:

Order:

id | status | sender | recipient | product | ...
----------------------------------------------------------------------------
12 | paid   | 20     | 40        | 5232    |


Product:

id   | name  | ...
----------------------------------
5232 | Stuhl |
5233 | Tisch |



Address:

id | name  | company | postcode | ...
---------------------------------------------------
40 | heinz | Firma A | 23223    |
41 | fritz | Firma B | 43232    |




Customer:

id | email                | address
-------------------------------------
20 | fritz@firma-b.com.au | 41

Wie man sieht, hat jede Bestellung einen Sender und einen Receiver. Die ID für die Adresse des Empfängers steht direkt in der Tabelle Order. Der Sender hingegen hat dort die ID für seinen Eintrag in der Tabelle Customer. Dort bekommt man dann erst die ID für dessen Addresse.

Die Suche sieht jetzt so aus:

	$qid = db_query("
	SELECT o.id, [..,..,..]
	FROM order o, product p, address ad, customer c
	WHERE (o.id LIKE '$search' OR ad.name LIKE '$search')
	AND ad.id = c.address
        AND c.id = o.sender
	AND p.id = o.product
	AND o.status IN ('paid','complete','error')
	ORDER by o.id DESC
	LIMIT 0, 100
	");

so funktioniert alles, jedoch durchsucht er nur den namen von "sender".
Wenn ich nun

AND ad.id = c.address
AND c.id = o.sender

mit

AND ad.id = c.recipient

austausche, dann durchsucht er natürlich nur den Namen des Receivers. Wie kann ich das denn am besten kombinieren?
Habe es schon so probiert:

AND (ad.id = o.recipient OR (ad.id = c.address AND c.id = o.sender))

Doch so arbeitet sich mein MySQL zu Tode, so das ich das Query manuell beenden muss Das muss wohl irgendwie anders gehen Hat irgendjemand einen Vorschlag? Oder vielleicht auch Optimierungsvorschläge?

Gruß,
Marc

 

[melmager]

Natürlich wird SQL durch die Abfrage lahmgelegt.
Ohne weiterere Angaben wird ein Cross Join gemacht dabe wird jeder Eintrag einer Tabelle mit jedem verbunden und dann wird erst gesucht.

bei 4 Tabellen und 100 einträgen pro Tabelle rechnet sich das so:

100 * 100 * 100 * 100

Also sollete man vorher schon aussotieren was nicht gebraucht wird
das geht recht elegant mit einem "left join" dort wir schon vor der where Auswertung unnötige Ergebnisse aussotiert.

das Left Join nimmt die linke Tabelle ganz aber bei der rechten seite wir nur die Einträge mit in das Ergebnis übernommen bei dem die on Verküpfung übereinstimmt
das nächste left join setzt dann auf dem Ergebnis auf


select .... from order o left join adresse ar on o.recipient = ar.id left join Customer c on o.sender = c.id left join adresse ads on c.adress = ads.id WHERE (o.id LIKE '$search' OR ar.name LIKE '$search' OR ads.name LIKE '$search')

wie du siehst wird dabei 2 mal mit der adress Table gearbeitet
obwohl du mehr Verküpfungen brauchst ist die Abfrage schneller

 

[splat]

Hi Melmager,

irgendwie hatte ich es doch im Gefühl, das ich die ganze Zeit etwas vergesse bei meinen Abfragen...
Danke für die ausführliche Erklärung!

Doch leider bekomme ich nun folgenden Fehler bei dem ersten LEFT JOIN:
MySQL Error: Unknown column 'o.recipient' in 'on clause'

Muss man da vielleicht noch irgendetwas in Klammern setzen oder so?
Denn die Spalte existiert.

 

[melmager]

nein klammern werden nicht gebraucht
setz mal ein as zwischen tabellenname und abkürzung

statt "order o" "order as o"

ansonsten wenn nicht schreibe mal den ganzen query hier rein

 

[splat]

Query sieht nun so aus:

	$qid = mysql_query("
	SELECT o.id
	FROM order o, product p
	LEFT JOIN address adr on o.recipient = adr.id
	LEFT JOIN customer c on o.sender = c.id 
	LEFT JOIN address ads on c.address = ads.id
	WHERE (o.id LIKE '$search' OR adr.name LIKE '$search' OR ads.name LIKE '$search')
	AND p.id = o.product
	ORDER by o.id DESC
	LIMIT 0, 100
	");

funktioniert auch mit address as adr nicht

 

[Mamphil]

Hi,

du solltest die per LEFT JOIN angebundenen Tabellen nicht im WHERE-Statement durchsuchen, sondern die entsprechenden Such-Einschränkungen schon im "ON"-Teil erledigen.

Ansonsten noch eine Empfehlung:
- Gewöhne dir eine saubere Schreibweise an: Alle MySQL-Teile GROß, also auch "AS" und "ON" groß.
- Vermeide MySQL-Injections per mysql_real_escape_string.

Mamphil

 

[splat]

Hallo Mamphil,

Zuerst einmal danke für die Tips.

Habe den WHERE Teil mal etwas abgespeckt. Das ganze sieht jetzt so aus:

$qid = mysql_query("
    SELECT o.id
    FROM order o, product p
    LEFT JOIN address adr ON o.recipient = adr.id
    LEFT JOIN customer c ON o.sender = c.id 
    LEFT JOIN address ads ON c.address = ads.id
    WHERE o.id LIKE '".mysql_real_escape_string($search)."'
    AND p.id = o.product
    ORDER BY o.id DESC
    LIMIT 0, 100
    ");

Bekomme aber leider immernoch den folgenden Fehler:
MySQL Error: Unknown column 'po.recipient' in 'on clause'

Wie binde ich die Sucheinschränkungen für ads.name LIKE '$search und adr.name LIKE '$search im ON teil richtig ein?

Marc

 

[melmager]

$qid = mysql_query("
    SELECT o.id
    FROM order AS o LEFT JOIN product AS p ON o.product = p.id
    LEFT JOIN address AS adr ON o.recipient = adr.id
    LEFT JOIN customer AS c ON o.sender = c.id 
    LEFT JOIN address AS ads ON c.address = ads.id
    WHERE o.id LIKE '".mysql_real_escape_string($search)."'
    AND p.id = o.product
    ORDER BY o.id DESC
    LIMIT 0, 100
    ");

 

[splat]

Ah... okay.. jetzt habe ichs auch kappiert

Funktioniert jetzt alles wunderbar...
...und die Suche ist jetzt auf einmal richtig schnell.. ;-)

vielen Dank für eure Hilfe!
Marc