MySQL Cross Join allgemeine Fragen

JackJTP

Hallo,

ich weiß nun nicht, ob dieses das korrekte Forum ist, da es ja arum geht, Daten aus einer Datenbank zu lesen- mit PHP aus mySQL um genau zu sein.

Mein Problem ist relativ schnell umschrieben und ich habe auch schon viel gegooglet, aber ich stehe hier auf der Leitung befürchte ich. Ich habe schon länger mit PHP und MySQL zu tun und bin daher kein Neuling, dem ihr erst noch die Grundlagen erklären müsstet.

Nun zu meiner Frage:

Ich habe 2 Tabellen. In der einen stehen Bild ID und andere Details zu hochgeladenen bildern wie der original-Name, datum etc. und in der anderen stehen nur BildID und UserID.

Mein Vorhaben: Eine Übersicht der letzten Bilder zu erstellen. ich kann hier nicht einfach die Bildnamen verwenden, da die Bilder in Unterordnern sind, welche die UserID des Uploaders als Namen tragen.

Ich müsste nun also aus der Tabelle mit den Bildnamen und ID´s eben diese auslesen, was ich antürlich mit Leichtigkeit schaffe- auch die Datumsgeschichte um die letzten 5 zu nehmen. Zusätzlich muss ich neben select bilder where time is... nun auch noch die ID tabelle auslesen- also in der tabelle die passende bildID suchen und die entsprechende UserID auswählen um den Bildpfad zu ermitteln.

Folgendes habe ich bisher:

PHP

<?php
$sql = "SELECT * FROM wcf2_rGallery_items ORDER by itemAddedDate LIMIT 6";
$refs = mysql_query($sql) or die("Anfrage nicht erfolgreich");
while($row = mysql_fetch_array($refs))
   {  
$addType = $row['itemPath'];
echo $addType;
echo '.';
$addExt = $row['itemOrigExtension'];
echo $addExt;
echo "<br>";
}
?>

Alles anzeigen

Vorab möchte ich mich natürlich schon einmal bedanken. Bis später dann

threadi

Tabellenverknüpfung mit MySQL läuft über die Angabe von den anzusprechenden Tabellen im FROM und deren Verbindung zueinander im WHERE. Beispiel:

SQL

SELECT * FROM bilder, user WHERE bilder.userid = user.userid

Näheres:
http://dev.mysql.com/doc/refman/5.1/de/select.html
http://www.schattenbaum.net/php/mstart.php
http://alice-grafixx.de/PHP-MYSQL-Tuto…ERE-Klausel-151
...

JackJTP

Aaaah- super. danke. es fällt mir wie schuppen von den augen

SinnlosS

Ich persönlich präferiere JOINs, aus Gründen einer übersichtlicheren Strukturierung. Die Bedingungen für die Tabellenverknüpfungen haben nach meiner Auffassung nichts in der WHERE-Klausel verloren. Die WHERE-Klausel ist dazu da die Ergebnismenge einzuschränken.

JackJTP

na dann- ein beispiel wäre nett wenn ich beide alternativen kenne, kann ich mir auch eine meinung bilden, was besser passt. danke

SinnlosS

Code

Tabelle `image`
id | filename | user_id | addDate | isPublished


Tabelle `user`
id | name




SELECT
	i.id,i.filename,u.name
FROM
	image i
	INNER JOIN
		user u
		ON u.id=i.user_id
WHERE 
	i.isPublished=1
ORDER BY
	i.addDate DESC
LIMIT 6

Alles anzeigen

JackJTP

hmm-ok. Auch nicht wesentlich komplizierter. Ich denke, die Entscheidung, welche man nimmt, ist auch eher eine Geschmacksfrage, oder? Schliesslich gibt es keine Funktion, die ermöglicht wurde um nicht angewendet zu werden, oder liege ich jetzt daneben? Allerdings muss ich zugeben, dass diese Lösung auch schön übersichtlich ist.

SinnlosS

Intern macht es von der Geschwindigkeit keinen wirklichen Unterschied. Ich habe allerdings in größeren Projekten häufiger mal JOINs über 5+ Tabellen, und da ist es ohne JOIN-Klausel sehr mühselig wenn man nach mehreren Wochen nochmal an so eine Abfrage ran muß um Anpassungen vorzunehmen. Daher verwende ich prinzipiell nur noch JOINs.
Mal abgesehen davon, dass das Beispiel von threadi nur eine Alternative für INNER JOINs bietet, nicht für OUTER JOINs.

JackJTP

ok, das ist wahr- aber da bei mir ja wirklich nicht sehr viel abgefragt werden muss und es ja auch nur inner ist, war seine antwort ja schon passend. bei den größeren sachen ist es völlig klar- wenn man es da auf die erste weise versucht, muss man schon nen eidetisches gedächniss haben

SinnlosS

Ja klar, das wollte ich auch nicht bestreiten. Sein Beispiel funktioniert für deinen Fall genauso einwandfrei und ich kenne einige Leute denen diese Schreibweise besser gefällt.
Ich bevorzuge für mich persönlich halt einfach aus Prinzip JOINs

threadi

JOINs haben den Nachteil, dass sie innerhalb der Datenbank-Engine mehr Abfragen erzeugen. Führe mal ein Explain und Miss die Zeit für ein Statement mit JOIN aus und mach das selbe nochmal mit WHERE. Ich habe die Erfahrung gemacht, dass man auf JOIN besser verzichten sollte. Die Geschwindigkeit, gerade bei großen Datenbanken, kann dadurch noch gesteigert werden. Die Übersichtlichkeit kann man dagegen mit einer ordentlichen Einrückung und Zeilenumbrüchen einfach erreichen.

SinnlosS

Code

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=41 ;


CREATE TABLE IF NOT EXISTS `image` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `filename` varchar(30) NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `addDate` datetime NOT NULL,
  `isPublished` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`isPublished`),
  KEY `user_id_2` (`user_id`),
  KEY `isPublished` (`isPublished`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

Alles anzeigen

EXPLAINs bringen bei beiden Abfragen ein identisches Ergebnis.

Zeitmessung:

PHP

<?php
$db = new mysqli("localhost","root","","jointest");
$sql = "
SELECT 
	i.id, i.filename, u.name
FROM 
	image i, user u
WHERE 
	i.isPublished =1
	AND 
	u.id=i.user_id
ORDER BY 
	i.addDate DESC 
LIMIT 6";
$res = $db->prepare($sql);
$start = microtime(true);
for($i=0;$i<10000;$i++) {
	$res->execute();
}
$stop = microtime(true);
$time = $stop-$start;
echo "<pre>WHERE:\t{$time}\n";
unset($res);
$sql = "
SELECT 
	i.id,i.filename,u.name
FROM 
	image i
		INNER JOIN 
		user u 
			ON 
			u.id=i.user_id
WHERE 
	i.isPublished=1
ORDER BY 
	i.addDate DESC 
LIMIT 6";
$res = $db->prepare($sql);
$start = microtime(true);
for($i=0;$i<10000;$i++) {
	$res->execute();
}
$stop = microtime(true);
$time = $stop-$start;
echo "JOIN:\t{$time}</pre>";
?>

Alles anzeigen

5 Beispieldurchläufe:

Code

WHERE:	3.526524066925
JOIN:	3.4969871044159


WHERE:	3.7058770656586
JOIN:	3.4664239883423


WHERE:	3.4749619960785
JOIN:	3.3867881298065


WHERE:	3.4011709690094
JOIN:	3.4289638996124


WHERE:	3.5800061225891
JOIN:	3.5392849445343

Alles anzeigen

threadi

Wieviele Datensätze sind da drinne? Welche MySQL-Version? Schonmal "Messen" im phpmyadmin probiert? (dort vergleiche ich sowas gerne)

SinnlosS

Das waren nur wenige Test-Datensätze, 20 Bilder, 40 User. MySQL-Version kann ich dir grad nicht sagen, bin erst Sonntag wieder zuhause, dann kann ich mal nachschauen. Ich teste das nächste Woche auch nochmal auf unserem Produktiv-Server in der Firma, da kann ich auf Tabellen mit ein paar Millionen Datensätzen testen. Interessiert mich schon, da meines Wissens beide Versionen von MySQL intern identisch verarbeitet werden (EXPLAIN schmeißt wie gesagt bei beiden Abfragen auch das identische Ergebnis).
Ich kann mir aber eigentlich nicht vorstellen, dass die Performance bei vielen Datensätzen auf einmal unverhältnismäßig auseinanderdriftet.
Generell muss ich sagen, ich habe sehr lange auch bei jedem "Kleinscheiß" auf Performance geachtet und allen möglichen Kram getestet. Davon bin ich aber völlig abgekommen, ich halte mich mittlerweile an den Leitsatz, dass als erste ausschließlich darauf hin entwickelt wird die Testcases ans Laufen zu kriegen und den Code übersichtlich und wartbar zu halten. Von Beginn an auf Performance zu achten ist nach meinen Erfahrungen absolut kontraproduktiv und sorgt nur für unnötige Verzögerungen in der Entwicklung. Performance-Optimierungen stehen an wenn das System fertig ist und läuft, und sich dann eben Performance-Probleme zeigen. Und da liegen die Flaschenhälse dann i.d.R. in ganz anderen Bereichen, als solchen Kleinigkeiten. Vielleicht revidiere ich da meine Meinung bezüglich unseres Falles hier nochmal, sollten sich tatsächlich bei großen Datenmengen stark spürbare Divergenzen ergeben. Kann ich mir aber wie gesagt bei den bisherigen Ergebnissen nicht vorstellen und habe ich auch vorher noch nie gehört.
Bezüglich der Übersichtlichkeit/Lesbarkeit: Klar, bei so einer simplen Abfrage wie hier ist das ziemlich latte, trotzdem präferiere ich auchda schon JOINs, weil da eben das zusammensteht, was auch logisch zusammengehört, und nicht zerstückelt und über die Abfrage verteilt wird.

SinnlosS

Ich habe den Test jetzt nochmal auf einem anderen Server mit zwei anderen Tabellen durchgeführt. Erste Tabelle mit 5,5 Millionen Datensätzen, gejointe Tabelle mit knapp über 100.000 Datensätzen. Mysql-Version 5.1.49.
Kein Geschwindigkeitsunterschied zwischen den beiden Abfragen.

Tags