Adventskalender Tag 3 – Spaß mit SQL: Kommentare untersuchen

MarketPress Adventskalender 3

SQL ist eigentlich eine schöne Sprache: Wir brauchen in den meisten Fällen nur ein knappes Vokabular, und die Abfragen folgen in ihrer Grammatik dicht der normalen englischen Sprache. Dennoch scheuen viele Einsteiger lange davor zurück, vielleicht weil hier das Risiko größer aussieht, etwas kaputtzumachen.

Heute wollen wir uns ein paar einfache SQL-Queries ansehen, die uns nähere Auskunft über unsere Kommentare geben. Ich empfehle dazu entweder eine Konsole oder den SQL-Editor der visuellen Datenbankverwaltung wie phpMyAdmin oder Adminer. Ein Tutorial soll dies nicht sein, sondern nur ein Ausgangspunkt für deine eigenen Recherchen.

Für die folgenden Queries greife ich auf eine Kommentardatenbank zurück, die mir Ramona Hapke freundlicherweise überlassen hat. Sie umfasst 12223 Einträge. Die Datenbank natürlich, nicht Ramona. 🙂

Wieviele dieser Einträge sind Spam?

Dazu sehen wir uns die Spalte comment_approved an. Hier speichert WordPress den Typ des Kommentars. Die Menge der unterschiedlichen Werte erfassen wir mit COUNT(*) und legen sie in der Variable amount ab. Die lassen wir uns auch ausgeben.

Ergebnis:

Die 1 steht für zugelassene, also echte Kommentare. Wir sehen schon jetzt: nur 0,85% aller Kommentare sind kein Spam. Das ist leider ein ganz normaler Wert. Das Gemeine ist: MySQL muss jedesmal durch die ganze Tabelle gehen, um die richtigen Kommentare für die Anzeige bei den veröffentlichten Beiträgen zu finden. Mit einem Index, einer für Abfragen optimierten Struktur, geht das schneller.

Welche Indexes gibt es?

Ergebnis

Gut, es gibt also einen Index für comment_post_ID und einen für Status und Datum zusammen. Darum müssen wir uns nicht kümmern. Allerdings habe ich gesehen, dass Ramona das Plugin Subscribe to Double-Opt-In Comments verwendet. Dieses fügt der Kommentardatenbank drei Spalten hinzu, jedoch keinen Index. Dann machen wir das doch einfach.

Einen Index erstellen wir in MySQL so:

Wir können auch einen eigenen Namen vergeben:

Und jetzt erzeugen wir drei Indexe für die zusätzlichen Spalten, damit die Abfragen ein wenig schneller gehen. Spezielle Namen brauchen wir nicht.

Der Index wird jetzt bei jedem Update der Datenbank aktualisiert. Das kostet Zeit und zusätzlichen Platz, deshalb sollten wir normalerweise nicht einfach jeden Index erzeugen, der uns in den Sinn kommt.

Welche Beiträge erleiden den meisten Spam?

Je populärer ein Beitrag ist, desto mehr Spam zieht er an. Manche Beiträge bekommen fast nie welchen ab, andere werden geradezu überrannt. Die 20 meistbespammten Beiträge bekommen wir mit diesem Query:

Ergebnis

Spam per Post

Wow, was für ein Gefälle! Diese 11062 Kommentare umfassen fast den kompletten Spam. 92 Beiträge mit Kommentaren gibt es derzeit, 123 sind es insgesamt. Jetzt könnten wir für diese Beiträge die Kommentare mit einer Anmeldepflicht schützen oder ganz schließen. Beides nicht ideal.

Sehen wir uns einmal nicht die Ziele des Spams an, sondern dessen Herkunft.

Woher kommen die meisten Spamkommentare?

Ergebnis

Diagramm: Spam per IP

Hier können wir ansetzen. Von manchen IP-Adressen kommt so viel Spam, dass wir sie getrost aussperren können. Das geht beispielsweise in der .htaccess so:

Allerdings ist das immer noch etwas umständlich; deshalb zeigt euch Christian bald, wie man Kommentare komplett verhindert, von deren IP-Adresse schon einmal Spam gesendet wurde.

Für heute habe ich euch hoffentlich ein wenig Lust darauf gemacht, mit SQL herumzuspielen. Reine SELECT-Queries sind ungefährlich. Im schlimmsten Fall fragt ihr zu viele Daten ab, und PHP oder MySQL nehmen sich eine Pause.

Beitrag teilen

Author Avatar

Ich bin Entwickler und Autor für MarketPress. In meiner Freizeit moderiere ich mehrere WordPress-Communities.

Auch interessant:

woonews19

WooCommerce Wochenrückblick #19: Google Analytics, kostenlose Themes und neue Kennzeichnungspflicht

von Michael Firnkes

Die Shop-Analyse ist wichtig, muss aber rechtlich abgesichert werden. Für WooCommerce gibt es neue Theme-Inspirationen und ein neues German Market. Dies u ...

Weiterlesen
slack-blog

Neues Plugin: Slack Connector - Verbinde WordPress, WooCommerce und Slack

von Michael Firnkes

Anfangs wollten wir lediglich unsere eigenen Slack-Prozesse optimieren. Mit automatisierten Benachrichtigungen aus dem MarketPress-Shop, dem Blog und unser ...

Weiterlesen
woo26

WooCommerce 2.6: Deutlich mehr Versandoptionen, bessere Bestellübersicht

von Michael Firnkes

WooCommerce 2.6 "Zipping Zebra" steht kurz vor der Veröffentlichung, das Release ist für kommenden Dienstag geplant. Wir zeigen dir, was sich für dich ...

Weiterlesen
wp-geburtstag

13 Jahre WordPress - 13 Mal ein WooCommerce- oder Blog-Theme gewinnen

von Michael Firnkes

WordPress wird 13. Und wiederholt seine Erfolgsgeschichte: Mit WooCommerce. Feiere mit und gewinne eines unserer Top-Themes. WordPress hat einen rasante ...

Weiterlesen

Kommentare

4 Kommentare

  1. #1

    Reicht es demnach nicht, eine IP-Adresse in WP in die Blacklist einzutragen?

  2. #3

    .. und erzeugt somit Last, Performance-Thema. Die Lösung von Thomas fängt das ab, so dass Ressourcen in der WP Installation frei werden.

  3. #4

    Man kann sich mit SQL das Leben ganz schön leichter machen. Auch wenn man – so wie ich – eigentlich keine Ahnung davon hat. Aber zum Glück gibt es im Netz inzwischen tonnenweise sehr gute Tipps (wie auch dieser hier!!), die einem auf die Sprünge helfen.

    Danke!
    Freu mich aufs nächste Kästchen. 🙂

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *

Du kannst folgende HTML Tags verwenden: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">