[SQL] Joins Optimierung

T

Talion

...
ID: 33759
L
20 April 2006
794
75
Hallo,

ich könnte mal einen kleinen Denkanstoß gebrauchen, ob/wie folgendes optimiert werden muss.
Ich habe folgende Tabelle:

Code: 
CREATE TABLE `abforum_accountlinker` (
  `link_super` int(10) unsigned NOT NULL default '0',
  `link_sub` int(10) unsigned NOT NULL default '0',
  KEY `super` (`link_super`),
  KEY `sub` (`link_sub`)
) TYPE=MyISAM;

Für den Fall, dass es jemand testen will, pack ich noch ein paar Daten dazu:
Code: 
INSERT INTO `abforum_accountlinker` VALUES (1, 10);
INSERT INTO `abforum_accountlinker` VALUES (1, 17);
INSERT INTO `abforum_accountlinker` VALUES (1, 73);
INSERT INTO `abforum_accountlinker` VALUES (1, 248);
INSERT INTO `abforum_accountlinker` VALUES (1, 899);
INSERT INTO `abforum_accountlinker` VALUES (1, 1);
INSERT INTO `abforum_accountlinker` VALUES (2, 4);
INSERT INTO `abforum_accountlinker` VALUES (3, 2);
INSERT INTO `abforum_accountlinker` VALUES (4, 6);
INSERT INTO `abforum_accountlinker` VALUES (5, 3);
INSERT INTO `abforum_accountlinker` VALUES (6, 2);
INSERT INTO `abforum_accountlinker` VALUES (7, 7);
INSERT INTO `abforum_accountlinker` VALUES (8, 5);
INSERT INTO `abforum_accountlinker` VALUES (8, 18);
INSERT INTO `abforum_accountlinker` VALUES (9, 9);
INSERT INTO `abforum_accountlinker` VALUES (10, 8);
INSERT INTO `abforum_accountlinker` VALUES (10, 11);
INSERT INTO `abforum_accountlinker` VALUES (10, 16);
INSERT INTO `abforum_accountlinker` VALUES (10, 44);
INSERT INTO `abforum_accountlinker` VALUES (11, 20);
INSERT INTO `abforum_accountlinker` VALUES (11, 12);
INSERT INTO `abforum_accountlinker` VALUES (12, 15);
INSERT INTO `abforum_accountlinker` VALUES (12, 13);


Nun will ich zu einer link_sub sämtliche anderen link_sub's mit der selben link_super haben. Oder einfacher ausgedrückt: Gegeben ist eine sub-Id (zu verstehen als Id eines Unteraccounts), und ich will sämtliche anderen sub-Ids haben, die zu dem selben Oberaccount (super-id) gehören.

(Ich nehm als Beispiel jetzt immer sub_id = 1)

Mit Subqueries wäre es ja simpel:
Code: 
SELECT link_sub FROM `abforum_accountlinker` WHERE link_super = (SELECT link_super FROM `abforum_accountlinker` WHERE link_sub = 1)
Aber die stehen mir leider nicht zur Verfügung.
Nun wüsste ich gerne, was die sinnvollste Alternative ist. Eine Möglichkeit wäre ja, daraus zwei Queries zu machen, was natürlich nicht sehr schön ist. Die anderen Möglichkeiten basieren darauf, die Tabelle mit sich selbst zu joinen:
Code: 
SELECT DISTINCT l2.link_sub FROM `abforum_accountlinker` AS l1 LEFT JOIN `abforum_accountlinker`  AS l2 ON l1.link_super = l2.link_super WHERE l1.link_sub = 1 

oder

SELECT DISTINCT l2.link_sub FROM `abforum_accountlinker` AS l1 , `abforum_accountlinker`  AS l2 WHERE l1.link_sub = 1  AND l1.link_super = l2.link_super ;

Was ist nun am sinnvollsten?
Eigentlich gefallen mir die self-joins nicht so, da mysql dann ja erstmal seeehr viele Resultate erzeugt und distinct nur hinterher alle mehrfachen raussortiert. Gibt es eine gute Lösung ohne subquery?

Die distinct-lösungen haben nebenbei noch den Nachteil, dass ich mir nicht gleichzeitig link_super ausgeben lassen kann, da dann das Ergebnis vermurkst wird.


Danke,
Hannes
 
Talion schrieb:
Nun wüsste ich gerne, was die sinnvollste Alternative ist. Eine Möglichkeit wäre ja, daraus zwei Queries zu machen, was natürlich nicht sehr schön ist. Die anderen Möglichkeiten basieren darauf, die Tabelle mit sich selbst zu joinen:
Zum Vergrößern anklicken....
Ich bin zwar nicht so der Fachmann, was Performance-Fragen angeht, aber wenn ich höre Joinen vs. 2 Queries, dann dürften doch die Queries viel schneller gehen, zumindest ja beide Queries wegen der Indizes optimiert werden ;)
 
Vielleicht kann das ja noch jemand bestätigen, in die selbe Richtung gingen meine Überlegungen nämlich auch ;)
Bin auch nicht so der Fachmann für Performance, daher wollt ich lieber mal nachfragen.

Danke schonmal!
 
Ich war grade mal neugierig:
PHP: 
<?php

include_once("v3/include/class.mysql.php");

$db=new db_mysql();
$db->connect("localhost","...","...");
$db->select_db("test");
$db->connect();

function microtime_float()
{
   list($usec, $sec) = explode(" ", microtime());
   return ((float)$usec + (float)$sec);
}

@set_time_limit(0);
@header("content-type: text/plain");
if(0) {
  echo "Creating test table... ";
  flush();
  
  $db->queryf("DROP TABLE IF EXISTS `abforum_accountlinker`");
  $db->queryf("CREATE TABLE `abforum_accountlinker` (
    `link_super` int(10) unsigned NOT NULL default '0',
    `link_sub` int(10) unsigned NOT NULL default '0',
    KEY `super` (`link_super`),
    KEY `sub` (`link_sub`)
  ) TYPE=MyISAM");
  
  for($i=0;$i<50000;$i++)
  {
    $db->queryf("INSERT INTO `abforum_accountlinker` VALUES (%u, %u)",rand(1,10000),rand(1,50000));
  }
  
  echo "ok\n";
  flush();
}


echo "Joining... ";
$start_time=microtime_float();

for($i=0;$i<10000;$i++)
{
  $res=$db->queryf("SELECT DISTINCT l2.link_sub FROM `abforum_accountlinker` AS l1
LEFT JOIN `abforum_accountlinker`  AS l2 ON l1.link_super = l2.link_super
WHERE l1.link_sub = %u",5000);
  $db->free_result($res);
}

$end_time=microtime_float();
echo "OK\n";
echo "Time: ".($end_time-$start_time)." seconds\n\n";



echo "Querying with 2 queries... ";
$start_time=microtime_float();

for($i=0;$i<10000;$i++)
{
  $row=$db->queryf_result("SELECT link_super FROM `abforum_accountlinker` WHERE link_sub=%u LIMIT 1",5000);
  $res=$db->queryf("SELECT link_sub FROM `abforum_accountlinker` WHERE link_super=%u",$row['link_super']);
  $db->free_result($res);
}

$end_time=microtime_float();
echo "OK\n";
echo "Time: ".($end_time-$start_time)." seconds\n\n";

?>
Ich hab nur 4x durchlaufen lassen, aber die Ergebnisse waren sich einig:
Joining... OK
Time: 14.5095999241 seconds

Querying with 2 queries... OK
Time: 20.7806470394 seconds
Zum Vergrößern anklicken....
 
Vielen Dank... Nur ist mir gerade aufgefallen, dass ich viel zu umständlich gedacht habe, und lieber das Tabellenlayout nochmal umschmeiße :roll:

Aber immerhin haben wir ja jetzt gelernt, dass sich joins gegenüber zwei queries durchaus lohnen können ^^


Sorry, dass du dir jetzt deswegen Mühe gemacht hast.


Edit: Die Mühe hat sich doch noch gelohnt, beim neuen Layout brauch ich sowas ähnliches.
 
Zuletzt bearbeitet:
neija da jeder join-subpart eigene indexe verwenden kann, ist es doch oft performanter (da auch der overhead durch 2 mal query senden, 2 mal queryoptimizer gespart wird)

Momentan würde ich einen Join einem subquery vorziehen, da der Queryoptimizer noch KEINE subquerys optimiert, also nur den stamm-query nicht den sub-query.
Zudem sind joins immer schneller wenn du den Join-Typ selbst definierst, Grund?
MySQL probiert ala brutforce aus, welches die schnellste Methode und wählt dann aus diesen Kombinationen die schnellste, so kann ein Join über 15 Tabellen schonmal 90sec dauern, die der query-optimizer nutzt, und dann nur noch 1 sec für die ausführung.
soviel kurz zum thema

Edit: wer sich dafür interessant, dem empfehle ich das Buch "MySQL High Performance" aus dem O'Reilly-Verlag ;)
 
Auch wenn es sich schon erledigt habe habe ich mal theHacker sein Script genommen.

mit 300023 Einträgen:
Joining... OK
Time: 4.8178870677948 seconds

Querying with 2 queries... OK
Time: 2.6091411113739 seconds
Zum Vergrößern anklicken....
bei den gleichen Einträgen mit meinem JOIN query und einer id spalte(PRIMARY KEY):
Joining... OK
Time: 1.6157529354095 seconds

Querying with 2 queries... OK
Time: 2.3142890930176 seconds
Zum Vergrößern anklicken....

jeden Query 10k mal durchlaufen lassen.

mein Query:
PHP: 
mysql_query("SELECT a.link_super FROM abforum_accountlinker AS a JOIN abforum_accountlinker AS b USING(id) WHERE a.link_sub = 5000");

*edit
aufgrund ice-breaker´s post
Hier nochmal die stats eines INNER JOIN
Joining... OK
Time: 1.5479249954224 seconds

Querying with 2 queries... OK
Time: 2.399197101593 seconds
Zum Vergrößern anklicken....
 
dann hat er nur einmal die abfrage ausgeführt und dann immer aus dem cache geladen, also nicht wirklich ungewöhnlich, dass beide ergebnisse so dicht beieinander sind
 
Hmm naja ob nu mit Cache oder ohne scheint bei mir egal zu sein.

Hier mal mit SELECT SQL_CACHE:
Code: 
Joining... OK
Time: 1.5213899612427 seconds

Querying with 2 queries... OK
Time: 2.5240609645844 seconds
Hier mal mit SELECT SQL_NO_CACHE:
Code: 
Joining... OK
Time: 1.7014169692993 seconds

Querying with 2 queries... OK
Time: 2.6378679275513 seconds

Cache Type ist On also sollte das so bei mir funktionieren den Cache zu umgehen. Ich vergaß evtl zu erwähnen das die zwei Query variante LIMIT 1 als Anhang hat.
 
ich vergaß, nach der ersten abfrage schreibt er alles in dem Arbeitsspeicher von daher ist es egal, interessant wird es, wenn die Datenbank größer als der Arbeitsspeicher ist ;)
Aber ist ja nun auch egal :biggrin:
 
ice-breaker schrieb:
Zudem sind joins immer schneller wenn du den Join-Typ selbst definierst, Grund?
MySQL probiert ala brutforce aus, welches die schnellste Methode und wählt dann aus diesen Kombinationen die schnellste, so kann ein Join über 15 Tabellen schonmal 90sec dauern, die der query-optimizer nutzt, und dann nur noch 1 sec für die ausführung.
soviel kurz zum thema
Zum Vergrößern anklicken....

nein... wenn du nur JOIN schreibst ist das ein CROSS JOIN, der wiederrum in mysql das selbe ist wie nen INNER JOIN wenn du ne join bedingung dran hängst... (also ON oder USING). von daher macht das geschwindigkeits technisch keinen unterschied ob du JOIN oder INNER JOIN schreibst... und was soll mysql da bitte testen? nen JOIN ist ne CROSS JOIN... da kann nicht plötzlich nen LEFT JOIN draus werden. aber ich glaub ich weiß was du meinst... das mit 90sekunden und 1sekunde ausfürhung kommt mir bekannt vor. da gings aber drum das mysql versucht die join reinfolge zu optimieren, aber ich bezweifle stark dass das per bruteforce passiert... ergibt für mich keinen sinn.

ice-breaker schrieb:
ich vergaß, nach der ersten abfrage schreibt er alles in dem Arbeitsspeicher von daher ist es egal, interessant wird es, wenn die Datenbank größer als der Arbeitsspeicher ist
Zum Vergrößern anklicken....

nicht nach der ersten abfrage... die daten waren höchstwahrscheinlich schon im speicher. die abfrage greift nur auf die indexe zu... so brauch mysql noch nichtmal auf die eigentliche daten zugreifen, sondern hollt sich alles aus dem index.
 
ZeroCCC schrieb:
nein... wenn du nur JOIN schreibst ist das ein CROSS JOIN, der wiederrum in mysql das selbe ist wie nen INNER JOIN wenn du ne join bedingung dran hängst... (also ON oder USING). von daher macht das geschwindigkeits technisch keinen unterschied ob du JOIN oder INNER JOIN schreibst... und was soll mysql da bitte testen? nen JOIN ist ne CROSS JOIN... da kann nicht plötzlich nen LEFT JOIN draus werden. aber ich glaub ich weiß was du meinst... das mit 90sekunden und 1sekunde ausfürhung kommt mir bekannt vor. da gings aber drum das mysql versucht die join reinfolge zu optimieren, aber ich bezweifle stark dass das per bruteforce passiert... ergibt für mich keinen sinn.
Zum Vergrößern anklicken....
argh, sry, meinte die reihenfolge, hast recht, gut bruteforced wird es nicht direkt sein, aber er probiert dutzende möglichkeiten aus, habe das von einem MySQL-Profi bei yahoo, die hatten da dieses Problem mit einem join über 15 Tabellen.[/quote]


ZeroCCC schrieb:
nicht nach der ersten abfrage... die daten waren höchstwahrscheinlich schon im speicher. die abfrage greift nur auf die indexe zu... so brauch mysql noch nichtmal auf die eigentliche daten zugreifen, sondern hollt sich alles aus dem index.
Zum Vergrößern anklicken....
Doch, sobald du Daten in eine Datenbank schreibst, sind diese noch ent im Ram, wenn du aber viel frequentierte Daten hast, werden die zusätzlich im Ram gehalten, da ein Lesevorgang der Datensätze auf Grund der Indexe zu lange dauern würde. Die Indexe liegen natürlich im Ram (nach einem Neustart des MySQL-Servers aber auch erst nach dem ersten Lesezugriff), aber die dazugehörigen Datensätze eben net, also Bedingungen die mittels eines Indexes aufgelöst werden können, benötigen den Festplattenzugriff um die dazugehörigen Datensätze zu laden (falls nicht im Ram).
Von daher macht es wieder einen Geschwindigkeitsunterschied, wenn man öfter Datensätze selected, ob die nur auf der Platte sind oder zusätzlich im Ram gehalten werden.
 
ice-breaker schrieb:
argh, sry, meinte die reihenfolge, hast recht, gut bruteforced wird es nicht direkt sein, aber er probiert dutzende möglichkeiten aus, habe das von einem MySQL-Profi bei yahoo, die hatten da dieses Problem mit einem join über 15 Tabellen.
Zum Vergrößern anklicken....

Aus Interesse: Ist das das aus "MySQL High Performance"? Wenn ja, dann muss ich mir den Wälzer auch mal besorgen :mrgreen:
 
Jup, also ist echt nen sau geiles Buch, hatte mich vorher ja schon damit beschäftigt aber da steht noch Unmengen mehr drinne und net nur alles theoretisch sondern auch was für Probleme die beiden Authoren selbst gestoßen sind (das mal ne Optimeirung nach hinten losging etc)
 
Du musst dich einloggen oder registrieren, um hier zu antworten.
Zurück
Oben