Fehlende Zeilen ermitteln, Select optimieren

Hallo!

Ich möchte in einer Tabelle überprüfen, für welche
Parameterkombinationen es keine entsprechenden Zeilen in der Tabelle
gibt.

Feld Typ Attribute Null Standard Extra
id int(11) Nein auto_increment
reporterid int(10) UNSIGNED Nein 0
partnerid int(10) UNSIGNED Ja 0
valueid int(10) UNSIGNED Nein 0
value double(20,3) Ja NULL
periodid

Indizes:

PRIMARY PRIMARY 1053415 id
reporterid INDEX 241 reporterid
partnerid INDEX 238 partnerid

Reporter

Die Tabelle enthält ~ 3 Millionen Zeilen.

Ich möchte nun herausfinden, für welche Jahre und für welche Partner
(partnerid) Handelspartner X (reporterid) keine Angaben über den
Handel gemacht hat.

Momentan löse ich es folgendermaßen:

"Select value from source_valuerelations where reporterid =3D '$memberId'
and partnerId =3D '$partnerId' AND periodid =3D '$year' AND valueid =3D 5";

Die Variablen werden von entsprechenden php Schleifen "gefüttert". Nur
dauern die Abfragen im Moment halbe Ewigkeiten (auch mal 20 Minuten
für bestimmte reporterids). Da ich nun aber überzeugt bin dass MySQL
dass deutlich schneller kann - wie kann ich so eine Überprüfung
effizienter bewerkstelligen?

Die DB läuft auf einem WAMP (win xp, apache 2, php 4, mysql 4) mit 2
GB Hauptspeicher und einem amd xp 3200+.

Re: Fehlende Zeilen ermitteln, Select optimieren

Bib schrieb:

> Hallo!
>
> Ich möchte in einer Tabelle überprüfen, für welche
> Parameterkombinationen es keine entsprechenden Zeilen in der Tabelle
> gibt.
>
> Feld Typ Attribute Null Standard Extra
> id int(11) Nein auto_increment
> reporterid int(10) UNSIGNED Nein 0
> partnerid int(10) UNSIGNED Ja 0
> valueid int(10) UNSIGNED Nein 0
> value double(20,3) Ja NULL
> periodid
>
> Indizes:
>
> PRIMARY PRIMARY 1053415 id
> reporterid INDEX 241 reporterid
> partnerid INDEX 238 partnerid
>
> Reporter
>
> Die Tabelle enthält ~ 3 Millionen Zeilen.
>
> Ich möchte nun herausfinden, für welche Jahre und für welche Partner
> (partnerid) Handelspartner X (reporterid) keine Angaben über den
> Handel gemacht hat.
>
> Momentan löse ich es folgendermaßen:
>
> "Select value from source_valuerelations where reporterid =3D '$memberId'
> and partnerId =3D '$partnerId' AND periodid =3D '$year' AND valueid =3D 5=
";
>
> Die Variablen werden von entsprechenden php Schleifen "gefüttert". Nur
> dauern die Abfragen im Moment halbe Ewigkeiten (auch mal 20 Minuten
> für bestimmte reporterids). Da ich nun aber überzeugt bin dass MySQL
> dass deutlich schneller kann - wie kann ich so eine Überprüfung
> effizienter bewerkstelligen?
>
> Die DB läuft auf einem WAMP (win xp, apache 2, php 4, mysql 4) mit 2
> GB Hauptspeicher und einem amd xp 3200+.

Ich sollte noch hinzufügen dass ich überprüfe ob mein Select ein
Ergebnis brachte oder nicht, dass wird dann entsprechend vermerkt.

Re: Fehlende Zeilen ermitteln, Select optimieren

Bib wrote:
^^^ Was fürn Ding?

> Ich möchte in einer Tabelle überprüfen, für welche
> Parameterkombinationen es keine entsprechenden Zeilen in der Tabelle
> gibt.
>
> Feld Typ Attribute Null Standard Extra
> id int(11) Nein auto_increment
> reporterid int(10) UNSIGNED Nein 0
> partnerid int(10) UNSIGNED Ja 0
> valueid int(10) UNSIGNED Nein 0
> value double(20,3) Ja NULL
> periodid

Ziemlich unleserlich. Bitte 'SHOW CREATE TABLE\G' in mysql eingeben.

> Ich möchte nun herausfinden, für welche Jahre und für welche Partner
> (partnerid) Handelspartner X (reporterid) keine Angaben über den
> Handel gemacht hat.

> Momentan löse ich es folgendermaßen:
>
> "Select value from source_valuerelations where reporterid = '$memberId'
> and partnerId = '$partnerId' AND periodid = '$year' AND valueid = 5";

Fehlt da noch ein ... WHERE value IS NULL?

> Die Variablen werden von entsprechenden php Schleifen "gefüttert".

Und woher kommen die Daten? Aus anderen Tabellen? Wenn ja, kann man da mit
einem Join vermutlich was machen...


> Nur dauern die Abfragen im Moment halbe Ewigkeiten (auch mal 20 Minuten
> für bestimmte reporterids). Da ich nun aber überzeugt bin dass MySQL
> dass deutlich schneller kann - wie kann ich so eine Überprüfung
> effizienter bewerkstelligen?

Wie sehen die anderen Joins aus?


Thomas

Re: Fehlende Zeilen ermitteln, Select optimieren

>Ziemlich unleserlich. Bitte 'SHOW CREATE TABLE\G' in mysql eingeben.

CREATE TABLE `source_valuerelations` (
`id` int(11) NOT NULL auto_increment,
`reporterid` int(10) unsigned NOT NULL default '0',
`partnerid` int(10) unsigned default '0',
`valueid` int(10) unsigned NOT NULL default '0',
`value` double(20,3) default NULL,
`sourceid` int(10) unsigned NOT NULL default '0',
`periodid` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `reporterid` (`reporterid`),
KEY `partnerid` (`partnerid`)
) TYPE=3DMyISAM
>Fehlt da noch ein ... WHERE value IS NULL?

Leider nicht, falls Daten für eine Parameterkombination nicht
vorhanden sind, existiert überhaupt keine entsprechende Zeile in der
Tabelle.

>Und woher kommen die Daten? Aus anderen Tabellen? Wenn ja, kann man da mit
>einem Join vermutlich was machen...
Die IDs kommen aus einer anderen Tabelle:

common_entity:
CREATE TABLE `common_entity` (
`name` varchar(255) NOT NULL default '',
`type` tinyint(3) unsigned NOT NULL default '0',
`ID` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`ID`),
FULLTEXT KEY `name` (`name`)
) TYPE=3DMyISAM

In common_entity werden weitere Informationen über die Handelspartner
gespeichert, source_valuerelations entspricht dabei reporterid und
partnerid.
Weitere Joins gibt es da erst mal nicht, ich hole nur in einer ersten
Abfrage solche common_entity.ID raus, die einen bestimmten Wert by
common_entity.type haben, das auch eigentlich nur um die Abfragen zu
stückeln.

Im Endeffekt soll für jeden Handelspartner mit jedem Handelspartner
geprüft werden, ob für ein bestimmtes Jahr und eine bestimmte
Handelsart Daten in Tabelle source_valuerelations vorliegen. Wie
gesagt, dass heißt dann aber nicht, dass nur das Feld
source_valuerelations.value leer ist, sondern dass dann die komplette
Zeile einfach nicht vorhanden ist.

Re: Fehlende Zeilen ermitteln, Select optimieren

>>Ziemlich unleserlich. Bitte 'SHOW CREATE TABLE\G' in mysql eingeben.
>
> CREATE TABLE `source_valuerelations` (
> `id` int(11) NOT NULL auto_increment,
> `reporterid` int(10) unsigned NOT NULL default '0',
> `partnerid` int(10) unsigned default '0',
> `valueid` int(10) unsigned NOT NULL default '0',
> `value` double(20,3) default NULL,
> `sourceid` int(10) unsigned NOT NULL default '0',
> `periodid` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `reporterid` (`reporterid`),
> KEY `partnerid` (`partnerid`)
> ) TYPE=MyISAM

>>Fehlt da noch ein ... WHERE value IS NULL?
>
> Leider nicht, falls Daten für eine Parameterkombination nicht
> vorhanden sind, existiert überhaupt keine entsprechende Zeile in der
> Tabelle.

Hm, das macht die Sache etwas komplizierter. Aber nicht unmöglich.

Schau Dir mal das Kapitel JOIN genau an, da gibts Sachen, die nennen sich
LEFT JOIN und RIGHT JOIN. Bei denen wird auf der "gegenüberliegenden"
Tabelle (also der rechten bei LEFT JOIN und der linken bei RIGHT JOIN)
angenommen, sie enthielte eine Zeile voller NULL-Werte, wenn keine passende
existiert.

Bei Dir läufts dann auf so was heraus wie

SELECT ... FROM common_entity ce_rpt LEFT JOIN source_valuerelations svr on
(ce_rpt.ID=svr.reporterid) RIGHT JOIN common_entity ce_partner on
(ce_partner.ID=svr.partnerid) WHERE svr.reporterid IS NULL AND ...;

wobei hinter das AND dann noch die anderen Bedingungen eingetragen werden
(die dann nicht aus einer DB kommen?)

Die zu selectenden Felder sind dann vermutlich ce_rpt.ID und ce_partner.ID
(hängt von Deiner Anwendung ab).

HTH,

Thomas

Re: Fehlende Zeilen ermitteln, Select optimieren

Ah, und noch vergessen:

> SELECT ... FROM common_entity ce_rpt LEFT JOIN source_valuerelations svr
> on (ce_rpt.ID=svr.reporterid) RIGHT JOIN common_entity ce_partner on
> (ce_partner.ID=svr.partnerid) WHERE svr.reporterid IS NULL AND ...;

Die noch fehlenden Bedingungen (periodid = '$year' AND valueid = 5) mußt Du
in einen der ON-Abschnitte reinpfriemeln, damit er das gescheit auswerten
kann.

> Die zu selectenden Felder sind dann vermutlich ce_rpt.ID und ce_partner.ID
> (hängt von Deiner Anwendung ab).

und dazu noch die Werte von periodid und valueid.

Statt das in einer Schleife zu machen, könntest Du dann diese Werte noch in
eine temporary table einzufügen und die dann mitzujoinen.


Zum Weiterexperimentieren:

create temporary table t1 (a int);
create temporary table t2 (b int);
create temporary table t3 (a int, b int);
insert into t1 values (1),(3),(5),(7),(9);
insert into t2 values (1),(2),(4),(5),(7),(8);
insert into t3 values (1,1),(5,5),(9,8);

select * from t1 left join t3 using (a) right join t2 using (b);
select * from t1 left join t2 on 1 left join t3 on t1.a=t3.a and t2.b=t3.b;

alter table t3 add column ab;
update t3 set ab=a*b;

select * from t1 left join t2 on 1 left join t3 on t1.a=t3.a and t2.b=t3.b;

insert into t3 values (1,5,5),(5,1,5);
select * from t1 left join t2 on 1 left join t3 on t1.a=t3.a and t2.b=t3.b;

select * from t1 left join t2 on 1 left join t3 on t1.a=t3.a and t2.b=t3.b
and ab=5;
select * from t1 left join t2 on 1 left join t3 on t1.a=t3.a and t2.b=t3.b
where ab=5;

Mache Dir den Unterschied zwischen den beiden letzten Zeilen klar:

wenn die Bedingung ab=5 im ON-Abschnitt steht, kommen weiterhin alle a und
b-Kombinationen ausgegeben, dort wo keine 5 in ab steht, wird dann eben
NULL ausgegeben.

Steht sie im WHERE-Abschnitt, werden nur die Zeilen, wo ab=5 ist,
ausgegeben.


HTH,

Thomas

Re: Fehlende Zeilen ermitteln, Select optimieren

Hallo und vielen Dank für die Mühe!

Ich habe es zunächst so probiert:

SELECT * FROM common_entity ce_rpt left JOIN source_valuerelations svr
on
(ce_rpt.ID=3Dsvr.reporterid AND svr.valueid =3D 15 AND svr.periodid =3D 197=
0)
right JOIN common_entity ce_partner on (ce_partner.ID=3Dsvr.partnerid)
WHERE svr.reporterid IS NULL;

leider bringt das kein Ergebnis - die Query läuft ewig (ich habs mal
aus Spass eine Stunde laufen lassen) und kommt zu keinem Ende. Habe ich
in der ON - Klausel die Bedingungen falsch eingefügt?

Re: Fehlende Zeilen ermitteln, Select optimieren

Bib wrote:

> Ich habe es zunächst so probiert:
>
> SELECT * FROM common_entity ce_rpt left JOIN source_valuerelations svr
> on
> (ce_rpt.ID=svr.reporterid AND svr.valueid = 15 AND svr.periodid = 1970)
> right JOIN common_entity ce_partner on (ce_partner.ID=svr.partnerid)
> WHERE svr.reporterid IS NULL;
>
> leider bringt das kein Ergebnis - die Query läuft ewig (ich habs mal
> aus Spass eine Stunde laufen lassen) und kommt zu keinem Ende. Habe ich
> in der ON - Klausel die Bedingungen falsch eingefügt?


Was sagt denn EXPLAIN dazu?

(Syntax: Explain select * from ...)

Thomas

Re: Fehlende Zeilen ermitteln, Select optimieren

Leicht modifiziertes SELECT mit EXPLAIN:

EXPLAIN SELECT *
FROM common_entity ce_rpt
LEFT JOIN source_valuerelations svr ON ( ce_rpt.ID = svr.reporterid AND
svr.valueid = 15 )
RIGHT JOIN common_entity ce_partner ON ( ce_partner.ID = svr.partnerid
)
WHERE svr.value IS NULL;

table type possible_keys key key_len ref rows Extra
ce_rpt ALL NULL NULL NULL NULL 310
ce_partner ALL NULL NULL NULL NULL 310
svr ALL NULL NULL NULL NULL 1053415 Using where

Re: Fehlende Zeilen ermitteln, Select optimieren

Bib wrote:

> table type possible_keys key key_len ref rows Extra
> ce_rpt ALL NULL NULL NULL NULL 310
> ce_partner ALL NULL NULL NULL NULL 310
> svr ALL NULL NULL NULL NULL 1053415 Using where


Wenn Dus mit \G abgeschlossen hättest statt mit ; wäre es etwas lesbarer.

Aber wenn ich die Tabelle richtig verstehe, verwendet er keine Keys, weil
keine "möglich" sind, und muß deshalb 1053415*310*310 Möglichkeiten
durchspielen. Damit wird er in diesem Leben vermutlich nicht mehr fertig.

Was ist mit den Indizes geschehen, die Du in einem der Artikel davor noch
drinstehen hattest?

Thomas

Re: Fehlende Zeilen ermitteln, Select optimieren

Sorry, habs auf der falschen Maschine laufen lassen :/

Und leider auch wieder unübersichtlich, \G löst bei mir nur
Fehlermeldungen aus.

table type possible_keys key key_len ref rows Extra
ce_rpt ALL NULL NULL NULL NULL 310
ce_partner ALL NULL NULL NULL NULL 310
svr ref reporterid,partnerid reporterid 4 ce_rpt.ID 4371 Using where

Auch das läuft sich irgendwie tot, er rechnet und rechnet und ..

Re: Fehlende Zeilen ermitteln, Select optimieren

Bib wrote:

> table type possible_keys key key_len ref rows Extra
> ce_rpt ALL NULL NULL NULL NULL 310
> ce_partner ALL NULL NULL NULL NULL 310
> svr ref reporterid,partnerid reporterid 4 ce_rpt.ID 4371 Using where
>
> Auch das läuft sich irgendwie tot, er rechnet und rechnet und ..

Naja, wenn die ce-Tabelle nun mal tatsächlich 310 Einträge hat, kann es
durchaus sein, daß es o.a. Ausmaße annimmt...

Evtl. kommst Du weiter, wenn Du

* die Reihenfolge der Tabellen änderst,
* STRAIGHT_JOIN verwendest,
* mit FORCE INDEX arbeitest.

Beschränke Dich evtl. in einer Testdatenbank auf weniger Werte und
experimentiere damit rum...


Thomas
--
Warum passiert am Tag immer nur soviel, wie am nächsten Tag in die
Zeitung paßt?