Adventskalender Tag 3 – Spaß mit SQL: Kommentare untersuchen

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:

WooCommerce Wochenrückblick #32: Woo 3.1, Live Chats und Snippet Tutorials

von Michael Firnkes

Die neue WooCommerce-Version verbessert hauptsächlich den Import und Export von Produkten. Und: Live Chats können - richtig eingesetzt - deinen Umsatz an ...

Weiterlesen

Wir suchen Verstärkung: WordPress PHP Entwickler/in in Dresden oder Homeoffice

von Michael Firnkes

MarketPress wächst, dank unserer WooCommerce-Suite German Market. Wir suchen dich - um diesen Erfolg weiterzuführen, und zur Entwicklung neuer Produkte. ...

Weiterlesen

WooCommerce Wochenrückblick #29: Beliebteste Zahlungsarten und sinnvolle Produktauswahl

von Michael Firnkes

Kauf auf Rechnung ist beliebter, als gedacht. Und zu viele Produkte können deine Kunden auch verwirren - wenn sie schlecht präsentiert werden. Dies und m ...

Weiterlesen

WooCommerce 3.0: Neue Galerie für Produktbilder, mehr Geschwindigkeit und Update-Hinweise

von Michael Firnkes

Das Team von WooCommerce hat die Version 3.0 seines extrem erfolgreichen Onlineshop-Systems veröffentlicht. Das Release läuft unter dem Namen "Bionic But ...

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 mit * 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="">

Abonniere unseren Newsletter

Trage hier deine E-Mail Adresse ein, damit du unseren Newsletter abonnieren kannst.