PHPGangsta - Der praktische PHP Blog

PHP Blog von PHPGangsta


Unbekannte und nützliche MySQL-Funktionen

with 7 comments

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?

Written by Michael Kliewe

Mai 3rd, 2012 at 10:03 am

7 Responses to 'Unbekannte und nützliche MySQL-Funktionen'

Subscribe to comments with RSS or TrackBack to 'Unbekannte und nützliche MySQL-Funktionen'.

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. INSERT IGNORE hat bisher niemand erwähnt….

    Michael

    6 Mai 12 at 19:13

  7. 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

Leave a Reply

You can add images to your comment by clicking here.