[MySQL] Anfrage Optimieren

[justsmile]

hallo,

ich bräuchte mal etwas Hilfe für eine SQL Anfrage, die dauert einfach zu lange.

SELECT te.adressnr, te.datum  
FROM (SELECT adressnr, datum FROM tap_eingang WHERE datum BETWEEN '2005-10-01' AND '2005-10-31') te 
LEFT JOIN ( SELECT aa.adressnr , pd.planzeit_von FROM per_dienstplan pd
INNER JOIN per_dienstplan_tourendaten pdt ON pd.dienstplan_id=pdt.dienstplan_id 
INNER JOIN adr_abholadressen aa ON
pdt.abholadress_id=aa.abholadress_id WHERE date(pd.planzeit_von) BETWEEN '2005-10-01' and '2005-10-31') tmp ON te.adressnr=tmp.adressnr AND  te.datum=date(tmp.planzeit_von) WHERE tmp.adressnr IS NULL

EXPLAIN gibt mir das hier aus

 id | select_type |    table    | type | possible_keys |    key |     key_len |      ref                     | rows | Extra
1 	  PRIMARY     <derived2>     ALL 	   NULL 	    NULL 	    NULL 	    NULL 	                13326 	 
1 	  PRIMARY     <derived3>     ALL 	   NULL 	    NULL 	    NULL 	    NULL 	                29449 	Using where; Not exists
3 	  DERIVED 	    pd 	    ALL 	  PRIMARY 	   NULL 	    NULL 	   NULL 	               24068 	Using where
3 	  DERIVED 	    pdt 	   ref 	dienstplan_id,
                                          abholadress_id  dienstplan_id 	4 	DB350647.pd.dienstplan_id 	11 	 
3 	  DERIVED 	    aa 	    eq_ref 	PRIMARY 	PRIMARY 	4 	     DB350647.pdt.abholadress_id 	1 	 
2 	  DERIVED      tap_eingang   range        datum 	datum 	     3 	        NULL 	              12706 	Using where

Das Problem sind sicherlich die anweisungen bei denen "type=all" steht doch leider weiß ich nicht wie ich das mit den Indizes mache.

Wäre sehr dankbar für eure vorschläge

 

[Sebbo]

Könntest du etwas mehr schreiben?
z.B. was die Abfrage genau liefern soll? Und wo schon Indexe vorhanden sind?

 

[justsmile]

Also die Sache sieht so aus:

Ich habe zwei Tabellen in denen jeweils einmal eine Adressnummer und einmal ein Datum gespeichert ist natürlich sind da noch paar mehr spalte.
Ich möchte nun alle Adressnr/Datum Tupel welche nicht in der Tabelle2 sind

Tabelle1 Tabelle2
------------------------- -------------------------
|.Adressnr..|..Datum..........|... .........|.Adressnr..|..Datum.........|
|..32904....|.2005-05-23....|.......|........|...32904...|..2005-05-23...|
|..32904....|.2005-05-24....|.......|........|...32904...|..2005-05-24...|
|..32904....|.2005-05-25....|.......|........|...92602...|..2005-05-23...|
|..92602....|.2005-05-23....|.......|........|...92602...|..2005-05-25...|
|..92602....|.2005-05-24....|.......|........|...32904...|..2005-05-23...|
|..92602....|.2005-05-25....|.......|........|..............|.....................|
|..92602....|.2005-05-26....|.......|........|..............|.....................|
|..32904....|.2005-05-23....|.......|........|..............|.....................|

die tabelle1(tap_eingang) existiert und die tabelle2 erzeuge ich mir durch Inner Joins von 3 tabellen.Meine Abfrage hab ich jetzt schon wie folgt hinbekommen

SELECT te.eingang_id, te.adressnr, te.datum 
FROM tap_eingang te 
LEFT JOIN 
(SELECT distinct aa.adressnr, date(pd.planzeit_von) AS datum
FROM per_dienstplan pd
INNER JOIN per_dienstplan_tourendaten pdt ON pd.dienstplan_id = pdt.dienstplan_id
INNER JOIN adr_abholadressen aa ON pdt.abholadress_id = aa.abholadress_id 
WHERE pd.planzeit_von BETWEEN '2005-10-01 00:00:00' AND '2005-10-31 23:59:59' 
ORDER BY adressnr, planzeit_von ASC )tmp 
ON te.adressnr = tmp.adressnr AND te.datum = tmp.datum
WHERE te.datum BETWEEN '2005-10-01' AND '2005-10-31'
AND tmp.adressnr IS NULL order by adressnr, datum ASC

Das Problem ist das sie einfach zu langsam ist. Explain sagt mir dazu folgendes:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY te range datum datum 3 NULL 12706 Using where; Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16497 Using where; Not exists
2 DERIVED pd range PRIMARY,planzeit_von planzeit_von 8 NULL 2716 Using where; Using temporary; Using filesort
2 DERIVED pdt ref dienstplan_id,abholadress_id dienstplan_id 4 DB350647.pd.dienstplan_id 11 Distinct
2 DERIVED aa eq_ref PRIMARY PRIMARY 4 DB350647.pdt.abholadress_id 1 Distinct


Ich denke mal das das Problem bei meiner durch Join zusammengestellten Tabelle liegt, also die die ich nach dem LEFT JOIN erzeuge.
Sie hat keinen Index deshalb ist die Anfrage meiner Meinung auch so langsam.

Tabelle1 ist wie folgt aufgebaut:
CREATE TABLE tap_eingang (
eingang_id int(11) NOT NULL auto_increment,
niederlsg_id tinyint(4) NOT NULL default '0',
adressnr int(11) NOT NULL default '0',
datum date NOT NULL default '0000-00-00',
abholzeit varchar(25) NOT NULL default '',
flags tinyint(4) unsigned NOT NULL default '0',
bemerkung varchar(100) NOT NULL default '',
mac_bearbeitet int(11) NOT NULL default '0',
PRIMARY KEY (eingang_id),
UNIQUE KEY adressnr_datum (adressnr,datum),
KEY datum (datum),
KEY niederlsg_id (niederlsg_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ich hoffe mal die Infos reichen.
Vielleicht liegt das Problem auch woanders aber ich denke mal es wird schon der eine fehlende Index sein oder

danke

 

[ZeroCCC]

Du kannst noch versuchen in tap_eingang adressnr und datum als Index zu setzen (als EIN Index), aber mehr kannst du in der Form auch nicht raushollen.

 

[justsmile]

auf denen ist doch schon ein Index. Aber kann man die Abfrag nicht noch irgendwie umgestalten das bei allen Unteranfragen ein Index benutzt wird?


id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY te range datum datum 3 NULL 12706 Using where; Using temporary; Using filesort
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16497 Using where; Not exists
2 DERIVED pd range PRIMARY,planzeit_von planzeit_von 8 NULL 2716 Using where; Using temporary; Using filesort
2 DERIVED pdt ref dienstplan_id,abholadress_id dienstplan_id 4 DB350647.pd.dienstplan_id 11 Distinct
2 DERIVED aa eq_ref PRIMARY PRIMARY 4 DB350647.pdt.abholadress_id 1 Distinct

 

[ZeroCCC]

auf denen ist doch schon ein Index. Aber kann man die Abfrag nicht noch irgendwie umgestalten das bei allen Unteranfragen ein Index benutzt wird?

Ah hab ich übersehen... liegt scheinbar am Subquery dass der Index nicht benutzt wird. Und nein den Subquery kann man nicht umgestallten dass er ein ein Index verwendet, ein Subquery hat kein Index.

PS: vielleicht versuchst du das mal ohne Subquery zu lösen...

 

[justsmile]

Subquery vermeiden ist also das einzige was ich machen kann? Hast Du vielleicht ein vorschlag wie ich das machen könnte?
Hab mich schonmal dran versucht aber noch nix hinbekommen.

 

[ZeroCCC]

Joins sind meist effektiver als jeder Subquery. Wie du das machen könntest musst du aber schon selbst herausfinden... du steckst doch in der Datenbank drin. Das einzige was ich von der Datenbank kenne ist die Struktur die ich mir aus deinen beiden unlesserlichen Queries herleiten kann. Ich kenn weder den Einsatzbereich, noch hab ich ein Gefühl für die Daten, noch verstehe ich das Problem richtig (dein Beispiel wiederspricht in meinen Augen deiner Anforderung).

Spontan würde ichs mal so versuchen. Das sollte dir alle Eingänge ohne Tourdaten raussuchen... ugly

tap_eingang LEFT JOIN
per_dienstplan_tourendaten ON (tap_eingang.abholadress_id = per_dienstplan_tourendaten.abholadress_id) LEFT JOIN
per_dienstplan ON (...dienstplan_id... AND DATE(per_dienstplan.planzeit_von) = tap_eingang.datum)

und

WHERE
        per_dienstplan.dienstplan_id IS NULL

 

[justsmile]

Hallo,

ich bräuchte nochmal Hilfe. Ich habe meine Anfrage nun ohne Subquery realisiert aber sie ist immer noch sehr langsam. Hat einer vielleicht noch einen Tip was ich machen kann?

Folgende Tabellen habe ich:

https://www.chopperunion.de/postcon/tabellen.png

Die Anfrage sieht nun folgendermaßen aus:

https://www.chopperunion.de/postcon/SQL.png

ich will nun alle Datensätze(adressnr,datum) aus tap_eingang haben wofür es keine Tour gibt.



Explain sagt mir folgendes dazu:

https://www.chopperunion.de/postcon/Explain.png


Wäre für jeden Tipp dankbar.

Gruß