Unbekannte und nützliche MySQL-Funktionen
Auf der Suche nach einer schnelleren Möglichkeit Daten aus einer MySQL Tabelle auszulesen bin ich auf die MySQL-Funktion GROUP_CONCAT() gestossen. Wenn man beispielsweise eine kommaseparierte Liste aller Usernamen benötigt würde man normalerweise folgendes tun:
$usernames = array();
$result = mysql_query('SELECT Username FROM users ORDER BY UserId');
while ($row = mysql_fetch_assoc($result)) {
$usernames[] = $row["Username"];
}
echo join(',', $usernames);
Mit GROUP_CONCAT() kann man sich die Schleife sparen und erhält direkt einen kommaseparierten String (der Trenner kann natürlich geändert werden, siehe übernächstes Beispiel):
$result = mysql_query('SELECT GROUP_CONCAT(Username) AS ConcatUsernames FROM users ORDER BY UserId');
$row = mysql_fetch_assoc($result);
echo $row['ConcatUsernames'];
Bei sehr großen Tabellen kann es auch etwas Traffic zwischen MySQL-Server und Webserver sparen, ebenso ist die zweite Lösung deutlich schneller.
GROUP_CONCAT() funktioniert auch, wie es der Name schon erahnen lässt, wenn GROUP BY verwendet wurde, also beispielsweise von allen Benutzern alle Login-IP-Adressen aufgelistet werden sollen:
SELECT Username,
GROUP_CONCAT(DISTINCT IpAddress
ORDER BY LoginDate DESC SEPARATOR ' ')
FROM logins
GROUP BY Username;
Welche MySQL-Funktionen nutzt ihr die eher weniger bekannt sind? Womit habt ihr die größten Performancegewinne erzielt?
Meine besten sind u.a. IF(), CASE() und ORDER BY CASE, sowie ON DUPLICATE KEY und die diversen JOINs und VIEWs.
Zu allen werde ich aber noch Artikel verfassen, das GROUP_CONCAT benutze ich aber auch selten mal.
Man kann seine Anwendung wirklich stark beschleunigen, wenn man MySQL voll ausnutzt und Arbeiten, die in der Datenbank optimiert ausführbar sind, auch wirklich dort stattfinden lässt.
Ich bin immer wieder überrascht, dass selbst „große“ Software noch nicht auf die Idee kommt, z.B. einfache Variablen in MySQL zu benutzten, geschweige denn VIEWs oder gar STORED PROCEDURES.
Zum Debuggen eignet sich übrigend PROCEDURE ANALYSE sehr gut, oder EXPLAIN SELECT zum Einstieg.
So, jetzt eure Vorschläge 😉
Sascha Presnac
3 Mai 12 at 10:13
Bei unseren Projekten haben wir das auch teilweise im Einsatz. Bedenkt dennoch, dass hier unnötige CPU Performance beim MySQL Server verbraten wird, diese lieber auf den Webserver auslagern, da dieser meistens mehr Resourcen zur Verfügung hat (Freie CPU Resourcen).
Also immer mit Bedacht verwenden.
Der Vorteil hierbei ist, dass die Übertragungspakete etwas kleiner sind, wenn man Group_Concat verwendet, da nur 1 Zeile (im oberen Beispiel) zurück gibt. Und nicht noch die ganzen Zeilen und Spalten Kommandos, usw.
MySQL ist dennoch sehr schnell, so dass es bei wenigen Daten nicht so viel Power verbraucht.
Steffen Grabaum
3 Mai 12 at 10:19
Benutze oft IFs, um beispielsweise Datensätze mit gewissen Werten in einer Spalte zuoberst im Set zu haben (die restliche Ordnung spielt dann meistens keine Rolle).
Also z.B.:
SELECT
id,
text,
language,
IF (language = ‚de‘, 1, 0) AS sortKey
FROM
table
ORDER BY
sortKey DESC
Wobei die Spalte language alle möglichen Länderkürzel
DukeNightcrawler
3 Mai 12 at 10:42
GROUP_CONCAT birgt allerdings ein „Risiko“,sollte die Liste sehr lang sein.
Es gibt einen MySQL Parameter, der die maximale Länge dieser Liste festlegt (group_concat_max_length).
Default ist 1024, sollte es länger werden (Achtung bei UTF-8 nur 512), schneidet MySQL kommentarlos einfach ab. Dies kann auch mitten in einer ID stattfinden (Bsp: „10001, 10002, 10003, 10“) oder direkt nach dem Komma (Bsp: „10001, 10002, 10003, „), was zu unerwarteten Ergebnissen führen könnte.
Michael
3 Mai 12 at 19:50
Ich nutze am meisten folgende Funktionen:
CONCAT
DATE_FORMAT
IF
IN
INET_ATON
INET_NTOA
ON DUPLICATE KEY
REPLACE
Robbyn
6 Mai 12 at 13:21
INSERT IGNORE hat bisher niemand erwähnt….
Michael
6 Mai 12 at 19:13
TRIGGER und FOREIGNKEYs.
Wie viel Datenleichen (oder gigantische Aufräum scripte) hätte ich nur ohne Sie *grins*.
T-Rex
6 Mai 12 at 20:24