MySQL/MariaDB, MyISAM – DROP INDEX, CREATE INDEX oder ALTER TABLE- Statements?

Im Zuge eines datenbank-lastigen PHP-Projektes stolperte ich gerade über ein Problem, bei dem etwa 20 MyISAM-Tabellen um relative viele generierte Datensätze ergänzt werden müssen. Nun greife ich ja schon von Haus aus zu den üblichen Maßnahmen: einer Behandlung möglichst vieler Records in in einem INSERT-Statement oder aber im Fall großer Record-Anzahlen auch zum Umweg über Dateien und LOAD DATA INFILE.

Beim Einsatz von LOAD DATA INFILE ist es klug, auf Indices erstmal zu verzichten. D.h., man wählt folgende Vorgehensweise:

  • Schritt 1: Ggf. TRUNCATE TABLE,
  • Schritt 2: Elimination erforderlicher Indices,
  • Schritt 3: Durchführung von LOAD DATA INFILE,
  • Schritt 4: Aufbau der Indices

Das ist nach meiner Erfahrung immer schneller als ein LOAD DATA INFILE bei gleichzeitigem Update mehrerer aktiver Indices.

Die Performance von “LOAD DATA INFILE” ist dann zwar sehr gut, aber das Löschen/Anlegen von Indices kostet im Vergleich u.U. erheblich (!) Zeit. Also ist man bemüht, auch an dieser Ecke da noch etwas an Performance herauszuholen.

“ALTER TABLE tablename DROP INDEX” statt “DROP INDEX indexname ON tablename”

Mein erster Tipp aufgrund der aktuellen Erfahrungen ist, nicht den bequemen Weg des SQL-Statements “DROP INDEX” zu wählen. Es zeigt sich, dass ein Statement der Art:

ALTER TABLE tablename DROP INDEX indexname

auf MyISAM-Tabellen fast immer um einen spürbaren Betrag schneller ist als

DROP INDEX indexname ON tablename

Man gewinnt mit “ALTER TABLE” zwischen 30% bis 45% an Schnelligkeit.

Was ist mit mehreren Indices?

Als PHP-Entwickler neigt man dazu, bekannte Indices in einem Loop – also nacheinander – zu löschen. Threading auf der PHP-Seite ist auf den meisten Web-Servern ja nicht gegeben. Es geht dennoch schneller. Das “ALTER TABLE” Statement bietet nämlich die Möglichkeit, mehrere zu eliminierende Indices anzugeben:

ALTER TABLE tablename DROP INDEX indexname1, DROP INDEX indexname2, DROP INDEX indexname3, …

Und siehe da, in einem Testbeispiel ging die Zeit in einem Testbeispiel mit einer kleineren Tabelle mit drei Indices von 0,14 Sek auf 0,072 Sek runter. Das sind in meinem Fall dann also nochmal ca. 45% Reduktion. Ich erkläre mir das in meinem Fall so, dass der Datenbankserver dabei 2 Prozessor-Cores einsetzen kann.

Wie sieht es bei CREATE INDEX aus?

Auch das Aufbauen der Indices geht mit

ALTER TABLE tablename ADD INDEX indexname1, ADD INDEX indexname2, ADD INDEX indexname3, …

deutlich schneller als mit einem “Create Index”-Statement.

Merke:
Benötigt man optimale Performance beim Löschen oder beim Aufbau von Indices, so sind “ALTER TABLE”-Statements den klassischen “Drop Index”- oder “CREATE INDEX”-Statements vorzuziehen. Das gilt im besonderen bei MySQL/MariaDB-Datenbankservern mit mehreren Prozessorcores.

phpmyadmin, charts, MariaDB – a strange bug with x-axis value order and ORDER clauses in sub-SELECTS

A customer of mine wants to use phpMyAdmin to create some charts whilst working with a complex database. I recommended a trial with phpMyAdmin and its chart functionality. Unfortunately, this did not always work out as expected:

You may e.g. create an ordered resultset first – with a suitable SELECT and ORDER clause – e.g. to get two columns – an index “i” and associatd values “val”. Rows of the resultset ordered e.g. ASC with the “i”-values. But after using the columns “i” a simple line graph the order on the x-axis may appear different than requested by your ORDER clause.

I have posted this as an issue to the (nice) developers of phpmyadmin – who are working on it :-).

There is a workaround. But what I found more interesting is the cause of the problem, which appears on MariaDB and NOT on MySQL. It has apparently to do with a different handling and interpretation of SQL standards for ORDER clauses in sub-SELECTs! See:
https://mariadb.com/kb/en/library/why-is-order-by-in-a-from-subquery-ignored/

This is of general interest for developers using MySQL or MariaDB-databases. I, myself, was not aware of it. Besides an already different handling of database replication, I regard this point as one more indication for a growing “distance” between both RDBM-systems…

If you stumble across the phpmyadmin-chart problem, see a discussion and a workaround on the following site:
https://github.com/phpmyadmin/phpmyadmin/issues/14239

I also want to explicitly thank M. Jayaratne who supplied me with the valuable information referenced above.