✔ [MySQL]Vereinfachung einer Abfrage

[jannicars]

Hallo,

Ich poste mal ein kleines stückchen code:

$sql_categories = 'SELECT id, name
			       FROM ' . CATEGORIES_TABLE . ' WHERE visible=1 ORDER BY sort_order ASC';
$result_categories = $db->sql_query($sql_categories);
$clan_total = 0;
while ($row_categories = $db->sql_fetchrow($result_categories)) {
	$categories_id = $row_categories["id"];
	$categories_name = $row_categories["name"];
	$sql_count_categories = 'SELECT COUNT(id) AS counts
			                 FROM ' . USER_TABLE . ' WHERE categories_id=' . $categories_id . ' AND visible=1 LIMIT 1';
	$result_count_categories = $db->sql_query($sql_count_categories);
	$zeilen_count = (int) $db->sql_fetchfield('counts');
	$clan_total = $clan_total + $zeilen_count;
	$db->sql_freeresult($result_count_categories);
}
$db->sql_freeresult($result_categories);

Das Problem ist jetzt, dass wenn ich eine Kategorie mehr habe, immer ein Query mehr gemacht wird.
Könnte man das nicht auch eleganter lösen?
Oder klappt das nur so?

Die Ausgabe sieht so z.B. aus:

Insgesamt: 24 | Kategorie1:8 | Kategorie2:8 | Kategorie3:8

Indem Fall werden 4(Einmal alle kategorien auslesen, und alle kategorien Inhalte zählen) Querys gemacht; insgesamt wird einfach aus allen gegebenen addiert.
Sowas könnte bei 200 Kategorien schon ein bisschen dauern.

Nicht wundern, dass keine Echos dort sind, hab ich alle entfernt, da es anders
per Framework und so läuft.

Tabellen:
categories:
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)

user:
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
categories_id INT(11),
name VARCHAR(255)

Wenn ihr mehr Informationen braucht, einfach bescheid sagen!

 

[para_noid]

Also wenn du die Anzahl der Queries minimieren möchtest, fallen mir bei deinem Beispiel nur zwei Wege ein:

1. Subselect
2. Inner Join + Zählen über die Schleife

1. in's Blaue:

$sql = '
SELECT  '.CATEGORIES_TABLE.'.id, 
 '.CATEGORIES_TABLE.'.name,
(
  SELECT COUNT(*)
  FROM  '.USER_TABLE.'
  WHERE '.USER_TABLE.'.categories_id = '.CATEGORIES_TABLE.' .id
) AS 'counts'
FROM  '.CATEGORIES_TABLE.' 
WHERE visible=1 
ORDER BY sort_order ASC';

$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result)) {
    $counts[] = $row['name'].': '.$row['counts'];
}
echo implode(' | ', $counts);

Ich kann dir aber beim besten Willen nicht sagen, in welchem Verhältnis ein Query mit Subselect mit "einfachen", aber mehreren Queries in Bezug auf die Performance steht.

2. in's Blaue

$sql = '
SELECT  '.USER_TABLE.'.id AS 'userid',
'.CATEGORIES_TABLE.'.id AS 'categoryid', 
'.CATEGORIES_TABLE.'.name

FROM  '.USER_TABLE.'
INNER JOIN '.CATEGORIES_TABLE.' ON  '.USER_TABLE.'.categories_id = '.CATEGORIES_TABLE.'.id
WHERE '.CATEGORIES_TABLE.' .visible=1 
ORDER BY '.CATEGORIES_TABLE.' .sort_order ASC';

$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result)) {
  $user[$row['name']][] = $row['userid'];
}

while(list($category, $value) = each($user)){
  $counts[] = $category.': '.count($value);
}

echo implode(' | ', $counts);

Hier bin ich mir nicht ganz sicher. Das INNER JOIN schließt alle Kategorien aus, die nicht mit einem User verbunden sind, also würde die spätere Ausgabe keine Angabe wie "Kategorie_x: 0" beinhalten. Wenn du das möchtest, musst du INNER mit RIGHT ersetzen.

Beide ungetestet.

@ Yaslaw: aye, das GROUP BY hatte ich vollkommen unterschlagen das macht es natürlich optimal.

 

[jannicars]

Danke für deine Mühe und Antwort!
Werde mal heute Nachmittag alle 3 Varianten durch einen Perfomance test schicken, um zu gucken,
welche sich am besten eignet!

 

[Yaslaw]

Ein JOIN und ein GROUP BY sollten ausreichen

SELECT
	ct.id,
	ct.name,
	COUNT(ut.id) AS counts
FROM 
	CATEGORIES_TABLE AS ct
	LEFT JOIN USER_TABLE AS ut
		ON ut.categories_id = ct.id
WHERE
	ct.visible=1		
GROUP BY
	ct.id,
	ct.name
ORDER BY
	ct.sort_order ASC;

Damit musst du nicht noch in einer Schleife zählen. Das Resultat beinhaltet alle Resultate. Du kannst also noch genau eine Schleife für die Ausgabe ma

 

[jannicars]

Auch nochmals danke dir für die Antwort!

Würde es nicht perfomancetechnisch mehr Sinn machen,
wenn ich die Anzahl der benutzer gleich bei der Kategorie dazuspeichere?
Da ich es als Statistik nutze würde.
Nur Admins können neue Benutzer hinzufügen etc. ,
dann Update ich doch dann einfach immer die Spalte,
die z.B. count genannt wird.
Und alles läuft viel viel perfomanter!
Und ich muss nur von allen Kategorien kurz die Spalte "count" auslesen.

Ist doch eine gute Idee oder?

 

[Yaslaw]

Jetzt kommt es auf die Datenzahlen an. Reden wir von 100'000 Users und 10'000 Kategorien oder beides unter 1'000?

Im ersten Fall - ja. Im Zweiten Fall, mach dir mal keine Sorge wegen der Performance in der DB.

 

[jannicars]

Ja sind deutlich unter 1.000 von beidem.
User: Wenn es hoch kommt max. 100 (Seltener Fall)
Kategorien: Im Extremfall max. 10 vllt.

Also söllt ich lieber deinen vorgeschlagenen query nutzen?
Oder ist der sehr perfomancestark?
Man Söllte auch beachten,
dass die Statistik auf jeder Seite immer am Schluss angezeigt wird.

 

[jannicars]

So läuft perfekt!
Danke nochmals!