Thorsten Rinne, alias arty konnte sich nicht zurückhalten und hat mir heute morgen erzählt, daß er wieder einen Schritt weiter sei bei der Anpassung von phpMyFAQ an MaxDB. Das zwang mich dazu die Software im aktuellen Stable-Release 1.5.0 zu installieren und zu beobachten was die Software mit einer MySQL 5.0.12-beta-max-log so macht. Thorsten hat
mir dann geholfen etwas über die Anwendung zu erfahren. Einen Index habe ich vermisst: Faktor 135 mit Testdaten.
Installation und Modifikation
Die Installation von phpMyFAQ ist denkbar einfach. Software herunterladen, Packet auspacken, Installationsskript im Browser öffnen, Datenbankparameter eingeben, Administratorpasswort festlegen, fertig. Genau so soll es sein für eine kleine PHP-Software. Nein, ich will kein PEAR benutzen müssen, um eine kleine PHP-Software zu installieren. Ich muß nichts anderes vorher installieren, nichts anderes bedien können. Auspacken, fertig.
Nach der Installation habe ich mir die Datei inc/mysqli.php vorgenommen und in der Methode query() eine Zeile eingefügt: print htmlspecialchars($query) . ". Dieser Quick-and-Dirty Hack sorgt dafür, daß alle SQL-Anweisungen auf dem Bildschirm ausgegeben werden. Es gibt anscheinend auch eingebaute Debugging Möglichkeiten, aber diese Lösung reichte aus, um die Frontseite zu begutachten.
\n";
Für die öffentliche Frontseite der FAQ werden eine ganze Reihe von SQL-Anfragen ausgeführt. Für jede der Queries haben Thorsten und ich uns mal die Ausgabe von EXPLAIN [anfrage] unter MySQL angeschaut. EXPLAIN gibt unter MySQL Auskunft darüber wie eine SELECT-Anfrage abgearbeitet werden würde, wenn diese ausgeführt werden würde. Anhand der Angaben kann man versuchen Schwächen im Ausführungsplan zu erkennen. Der Ausführungsplan beschreibt wie die Datenbank auf die angeforderten Daten zugreift.
Die Methode: EXPLAIN
Bei der Ausgabe von EXPLAIN handelt es sich oft um eine Momentaufnahme. Praktisch alle modernen Datenbanken verwenden einen Optimizer, der nicht regelbasiert arbeitet, sondern die aktuelle Datenlage bei der Analyse einer Anfrage betrachtet. Der von der Datenbank verwendete Ausführungsplan ist damit abhängig von der Datenlage. Und die wiederum wird zuminstest teilweise anhand von Statistiken beurteilt. Bevor man sich also auf das EXPLAIN stürzt ist ein ANALYZE TABLE fast schon Pflicht! Nur mit Kenntnis der realen Datenverteilung kann der Optimizer einen guten Ausführungsplan bestimmen. Das dumme an ANALYZE TABLE ist, daß es Last erzeugt und Lesesperren setzt. MaxDB hat das Problem auch (noch), aber versucht durch Parallelverarbeitung die Beeinträchtigungen so klein wie möglich zu halten. Wenn möglich, sollten Statistiken nur zu lastarmer Zeit aktualisiert werden.
(1)
SELECT sid
FROM faqsessions
WHERE sid = 310 AND ip = '127.0.0.1' AND time > 1126803298
(2)
SELECT id, lang, parent_id, name, description
FROM faqcategories
WHERE lang = 'de' ORDER BY id
(3)
SELECT count(sid)
FROM faqsessions
WHERE time > 1126889398
GROUP BY ip
(4)
SELECT DISTINCT
faqdata.id, faqdata.lang, faqdata.thema,
faqcategoryrelations.category_id, faqvisits.visits
FROM
faqvisits, faqdata
LEFT JOIN
faqcategoryrelations ON
faqdata.id = faqcategoryrelations.record_id AND
faqdata.lang = faqcategoryrelations.record_lang
WHERE
faqdata.lang = 'de' AND
faqdata.id = faqvisits.id AND
faqdata.lang = faqvisits.lang AND
faqdata.active = 'yes'
ORDER BY
faqvisits.visits DESC
(5)
SELECT DISTINCT
faqdata.id, faqdata.lang, faqcategoryrelations.category_id,
faqdata.thema, faqdata.datum, faqvisits.visits
FROM
faqvisits, faqdata
LEFT JOIN faqcategoryrelations ON
faqdata.id = faqcategoryrelations.record_id AND
faqdata.lang = faqcategoryrelations.record_lang
WHERE
faqdata.lang = 'de' AND
faqdata.id = faqvisits.id AND
faqdata.lang = faqvisits.lang AND
faqdata.active = 'yes'
ORDER BY
faqdata.datum DESC
(6)
SELECT datum, header, artikel, link, linktitel, target
FROM faqnews ORDER BY datum desc
(7)
SELECT id FROM faqdata
WHERE active = 'yes'
Wie ausgeführt macht es keinen Sinn SQL-Anfragen zu analysieren, wenn keine Daten in der Datenbank vorhanden sind. Deshalb habe ich eine FAQ-Kategorie und einen Artikel über die Admin Oberfläche angelegt und anschließend mit einigen Stored-Procedures zusätzliche Datensätze generiert. Wie man das macht, ist in Check Constraints mit Views/Trigger simulieren? verbloggt. Am Ende waren 9999 FAQ-Artikel in faqdata gespeichert, 99 Kategorien in faqcategories verzeichnet und 5563 Verknüpfungen zwischen FAQ-Artikeln und Kategorien in faqcategoryrelations verzeichnet.
Die Anfragen – der Reihe nach
Als harmlos erwies sich Anfrage (1). In der Ausgabe von EXPLAIN erscheinen die Tabellen in der Reihenfolge in der sie später der Datenbank ausgelesen werden würden. Bei dieser Anfrage ist nur eine Tabelle beteiligt und die bekommt die “id” 1. Als Bescheibung für den Anfragetyp vermerkt MySQL unter “select_type” den Wert “SIMPLE”, die einfachste Form von SELECT-Anfragen. Anschließend informiert MySQL in der Spalte “table” den Anwender, daß zur “id” 1 die Tabelle “faqsession” gehört. “type” gibt an wie auf die Tabelle zugegriffen wird. Hier muß man besonders genau hinschauen, wenn es sich um eine Verbundanfrage handelt. Der Wert von “type” gibt Auskunft wieviele Daten beim Ãœbergang von einer Tabelle in die nächste evaluiert werden müssen. Die folgende Spalte “possible_keys” beschreibt welche Indizes theoretisch zur Auswertung der gegebenen WHERE- und Verbund-Bedingungen verwenden könnte. Mit “key” schließt sich eine wichtige Spalte an, die sagt welcher Index tatsächlich verwendet werden würde, wenn man die SELECT-Anfrage jetzt ausführen würde. “key_len” welcher Teil des Indizes ausgelesen wurde, “rows” gibt eine Schätzung über die Anzahl der zu lesenden Datensätze ab und “Extra” kann Hinweise für den Administrator enthalten.
Bei dieser SELECT-Anfrage ist fast alles “bene”. Key: PRIMARY – Zugriff über den Primärschlüssel, besser geht es kaum. Die Bedingungen ip = '127.0.0.1' AND time > 1126803298 braucht MySQL gar nicht für die Suche nach dem Datensatz, genauer für die Suche nach dem Speicherplatz auszuwerten. Beide Bedingungen können ausgewertet werden, wenn der eindeutig über seinen Primärschlüssel definierte Datensatz gefunden wurde.
mysql> EXPLAIN SELECT sid FROM faqsessions WHERE sid = 310 AND ip = '127.0.0.1' AND time > 1126803298; +----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | faqsessions | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE faqsessions; +-------------+----------------------------------------------------------+ | Table | Create Table | +-------------+----------------------------------------------------------+ | faqsessions | CREATE TABLE `faqsessions` ( `sid` int(11) NOT NULL, `ip` text NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`sid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------------+----------------------------------------------------------+ 1 row in set (0.00 sec)
Nicht übertreiben
Die nächste Anfrage ist nicht sonderlich optimiert. Zunächst sieht es dramatisch aus. MySQL findet keinen Index, den es benutzen könnte. Und in der Spalte “Extra” erscheint mit dem Hinweis “Using filesort” etwas, was man eigentlich nie sehen will. MySQL muß eine Sortierung der Datensätze durchführen, weil sie mit ORDER BY id angefordert werden. Diese Sortierung wird
idealerweise im sort_buffer_size-Byte großen Sortierpuffer vorgenommen. Bei der geringen Anzahl von “rows” = 99 Datensätzen dürfte alles in den (Standard-)Puffer passen und die Sortierung sehr, sehr schnell sein. Mehr als 99 Kategorien werden nur die wenigsten Anwender haben, also kein Grund zur Panik.
Einige Sortierungen kann man durch einen Index beschleunigen. Ein Index ist sortiert. Werden Daten in Indexreihenfolge angefordert, dann kann der Indexzugriff benutzt werden, um eine Sortierung quasi “umsonst” zu bekommen. Auf dieser Tabelle steht nur der Primärschlüssel als Index zur Verfügung. Doch warum wird er nicht genutzt? Weil es sich nicht lohnt! Sie können MySQL gerne einen Primärschlüssel mit der Reihenfolge (lang, id) oder einen zusätzlichen Index mit derselben Kombination anbieten. Bei so wenig Datensätzen ist ein Tabellenscan schneller als ein Indexzugriff. Also: kein Grund zu Panik
mysql> EXPLAIN SELECT id, lang, parent_id, name, description
-> FROM faqcategories
-> WHERE lang = 'de' ORDER BY id
-> ;
+----+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | faqcategories | ALL | NULL | NULL | NULL | NULL | 99 | Using where; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE faqcategories;
+---------------+-----------------------------------------------------+
| Table | Create Table |
+---------------+-----------------------------------------------------+
| faqcategories | CREATE TABLE `faqcategories` (
`id` int(11) NOT NULL,
`lang` varchar(5) NOT NULL,
`parent_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`,`lang`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW INDEXES FROM faqcategories;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| faqcategories | 0 | PRIMARY | 1 | id | A | 99 | NULL | NULL | | BTREE | |
| faqcategories | 0 | PRIMARY | 2 | lang | A | 99 | NULL | NULL | | BTREE | |
+---------------+------------+--
Bei der nächsten Anfrage ist erstmals etwas Optimierungspotential vorhanden, naja zumindest theoretisch. 310 Sessions sind in der Datenbank gespeichert und müssen komplett durchsucht werden (“key” = NULL, “rows” = 310). Das klingt böse, doch wieder ist die Frage, wie groß die Tabelle ist. Nicht, daß ich fehlende Indizes verteidigen möchte, nein es lohnt sich manchmal einfach nicht einen Index anzulegen. Bei 310 Datensätzen und 7440 Bytes ist ein Tablescan noch nicht dramatisch. Wenn jedoch die Tabelle wächst könnte sich das Blatt wenden. Also doch Optimierungspotential?
mysql> EXPLAIN SELECT count(sid)
-> FROM faqsessions
-> WHERE time > 1126889398
-> GROUP BY ip;
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | faqsessions | ALL | NULL | NULL | NULL | NULL | 310 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE faqsessions;
+-------------+------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------+
| faqsessions | CREATE TABLE `faqsessions` (
`sid` int(11) NOT NULL,
`ip` text NOT NULL,
`time` int(11) NOT NULL,
PRIMARY KEY (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT table_name, data_length, index_length FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'faq' AND table_name = 'faqsessions';
+-------------+-------------+--------------+
| table_name | data_length | index_length |
+-------------+-------------+--------------+
| faqsessions | 7440 | 6144 |
+-------------+-------------+--------------+
1 row in set (0.01 sec)
Grundsätzlich können auch Gruppierungen in einem Index profitierten. Wie bereits gesagt ist ein Index immer sortiert. Hat man eine sortierte Liste, dann findet man sehr schnell die Gruppen indem man die Liste durchsucht und wartet bis man auf einen neuen Wert trifft. Jeder neue Wert markiert eine neue Gruppe. Der neue Wert kann in der sortierten Liste noch nicht vorgekommen sein, also muß es ein neuer Wert sein, der eine neue Gruppe einleitet. MySQL könnte also von Indizes über die Spalten (time, ip(15)) profitieren. Doch ob es hilft, da bin ich vorsichtig. Es hängt sehr von der konkreten Datenlage ab.
Ein klarer Fall?
Die anschließende Anfrage (4) scheint da ein klarer Fall. Das sieht gar nicht gut aus. Mal vorgelesen: lese jeden der vermutlich 7.500 Einträge aus der Tabelle “faqdata” und prüfe ob es eine Ãœbereinstimmung mit den vermutlich 5563 Einträgen in der Tabelle “faqcategoryrelations” gibt, die Du über einen Primärschlüsselzugriff ermittelst. Für alle Treffer, greife auf “faqvisits” über den Primärschlüssel zu. Es wird einen Eintrag in “faqvisits” pro Eintrag aus “faq.faqdata.id” erwartet. Aua, das macht 7.500 x 5563 x 1 = 41.722.500 Vergleiche – MySQL macht einen Nested Loop Join. 41 Millionen Vergleiche sind selbst für einen Weltrekordhalter wie MySQL eine Menge. Die Folge ist eine Ausführungszeit von über 2 Minuten auf einem P4 2.8 Ghz, 1.5GB, SATA-Raid 0: 11291 rows in set (2 min 15.25 sec). Bei kalten Zwischenspeichern sind sogar gut drei Minuten drin. Hier ist Aufmerksamkeit gefordert.
mysql> EXPLAIN SELECT DISTINCT
-> faqdata.id, faqdata.lang, faqdata.thema,
-> faqcategoryrelations.category_id, faqvisits.visits
-> FROM
-> faqvisits, faqdata
-> LEFT JOIN
-> faqcategoryrelations ON
-> faqdata.id = faqcategoryrelations.record_id AND
-> faqdata.lang = faqcategoryrelations.record_lang
-> WHERE
-> faqdata.lang = 'de' AND
-> faqdata.id = faqvisits.id AND
-> faqdata.lang = faqvisits.lang AND
-> faqdata.active = 'yes'
-> ORDER BY
-> faqvisits.visits DESC;
+----+-------------+----------------------+-------+---------------+---------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+-------+---------------+---------+---------+----------------------+------+----------------------------------------------+
| 1 | SIMPLE | faqdata | ALL | PRIMARY | NULL | NULL | NULL | 7500 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | faqcategoryrelations | index | NULL | PRIMARY | 22 | NULL | 5563 | Using index |
| 1 | SIMPLE | faqvisits | ref | PRIMARY | PRIMARY | 11 | faq.faqdata.id,const | 1 | Using where |
+----+-------------+----------------------+-------+---------------+---------+---------+----------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE faqdata;
+---------+----------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------+
| faqdata | CREATE TABLE `faqdata` (
`id` int(11) NOT NULL,
`lang` varchar(5) NOT NULL,
`active` char(3) NOT NULL,
`keywords` text NOT NULL,
`thema` text NOT NULL,
`content` longtext NOT NULL,
`author` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`comment` enum('y','n') NOT NULL default 'y',
`datum` varchar(15) NOT NULL,
PRIMARY KEY (`id`,`lang`),
FULLTEXT KEY `keywords` (`keywords`,`thema`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
Immer der Reihe nach und mit Ruhe. Das ist übrigends eine der größten Stärken des Handballschiedsrichters und MyPhpFAQ Autors. Ich weiß nicht wie man ihn aus der Ruhe bringen kann. Wird ein Join als Nested-Loop-Join abgearbeitet, dann bedeutet, dies nichts anderes als das ein paar Schleifen ineinander geschachtelt werden. Die beste Geschwindigkeit wird dabei erreicht, wenn die inneren Schleifen ihre Daten komplett aus einem Cache beziehen können. Eine Optimierung bei Nested-Loop-Joins besteht also darin die kleinen Tabellen in die innersten Schleifen – also ganz ans Ende der EXPLAIN-Ausgabe – zu bringen. Vor diesen Ãœberlegungen muß man jedoch zunächst prüfen ob man die Anzahl der Schleifendurchläufe durch eine bessere Selektivität begrenzen kann. Bevor man sich also um Details kümmert, die der Optimizer übernehmen sollte, will man erst einmal das häßliche “key” = NULL entfernen.
Die Tabelle “faqdata” wird über die Spalten “id”, “lang” und “active” abgefragt. “id” und “lang” sind im Primärschlüssel enthalten. “active” wird vermulich eine sehr geringe Selektivität aufweisen. Wahrscheinlich werden hier nur sehr wenige, unterschiedliche Daten gespeichert und ein Indexzugriff wird nicht rentabel sein. Es lohnt sich kaum, hier irgendetwas zu ändern. Damit fällt die Tabelle “faqdata” als Kandidat aus.
mysql> SHOW CREATE TABLE faqcategoryrelations; +----------------------+-----------------------------------------------------------------------------+ | Table | Create Table | +----------------------+--------------------------------------------------------------------------- -+ | faqcategoryrelations | CREATE TABLE `faqcategoryrelations` ( `category_id` int(11) NOT NULL, `category_lang` varchar(5) NOT NULL default '', `record_id` int(11) NOT NULL, `record_lang` varchar(5) NOT NULL default '', PRIMARY KEY (`category_id`,`category_lang`,`record_id`,`record_lang`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +----------------------+----------------------------------------------------------------------------+
Immer nur von links!
Auf “id” = 1 folgt “id” = 2 und damit “faqcategoryrelations”. Im Join wird die Tabelle von “faqdata” ausgehend über “record_id” und “record_lang” angesprochen. Diese beiden Felder können jedoch nicht über einen Index ausgewertet werden, weil sie keinen linken Prefix eines B-Baum Indexes ausgewertet werden. D.h., MyISAM kann keine schnelle Zugriffstrategie benutzen. Wenn man die Feldreihenfolge im Primärschlüssel umdreht auf (record_id, record_lang, category_id, category_lang), dann wird die Query schnell: 11291 rows in set (0.93 sec)
mysql> explain SELECT DISTINCT faqdata.id, faqdata.lang, faqdata.thema, faqcategoryrelations.category_id, faqvisits.visits FROM faqvisits, faqdata LEFT JOIN faqcategoryrelations ON faqdata.id = faqcategoryrelations.record_id AND faqdata.lang = faqcategoryrelations.record_lang WHERE faqdata.lang = 'de' AND faqdata.id = faqvisits.id AND faqdata.lang = faqvisits.lang AND faqdata.active = 'yes' ORDER BY faqvisits.visits DESC; +----+-------------+----------------------+------+-----------------------------+---------+---------+----------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+------+-----------------------------+---------+---------+----------------------+------+----------------------------------------------+ | 1 | SIMPLE | faqdata | ALL | PRIMARY,idx_lang,idx_active | NULL | NULL | NULL | 7500 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | faqcategoryrelations | ref | PRIMARY | PRIMARY | 11 | faq.faqdata.id,const | 37 | Using index | | 1 | SIMPLE | faqvisits | ref | PRIMARY | PRIMARY | 11 | faq.faqdata.id,const | 1 | Using where | +----+-------------+----------------------+------+-----------------------------+---------+---------+----------------------+------+----------------------------------------------+ 3 rows in set (0.00 sec)
Hier ist deshalb zumindest ein CREATE INDEX idx_record_id_lang(record_id, record_lang) ON faqcategoryrelations angebracht. Und 2:15 auf 0:01 Sekunden macht Faktor 135. Das dürfte den Speicherplatz wert sein, den der neue Index braucht. Mit diesen einen, neuen Index sieht auch die Anfrage (5) gleich halbwegs gesund aus.
mysql> explain SELECT DISTINCT
-> faqdata.id, faqdata.lang, faqcategoryrelations.category_id,
-> faqdata.thema, faqdata.datum, faqvisits.visits
-> FROM
->
-> faqvisits, faqdata
-> LEFT JOIN faqcategoryrelations ON
-> faqdata.id = faqcategoryrelations.record_id AND
->
-> faqdata.lang = faqcategoryrelations.record_lang
-> WHERE
-> faqdata.lang = 'de' AND
-> faqdata.id = faqvisits.id AND
-> faqdata.lang = faqvisits.lang AND
-> faqdata.active = 'yes'
-> ORDER BY
-> faqdata.datum DESC;
+----+-------------+----------------------+------+-----------------------------+--------------------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------+-----------------------------+--------------------+---------+----------------------+------+----------------------------------------------+
| 1 | SIMPLE | faqdata | ALL | PRIMARY,idx_lang,idx_active | NULL | NULL | NULL | 7500 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | faqcategoryrelations | ref | PRIMARY,idx_record_id_lang | idx_record_id_lang | 11 | faq.faqdata.id,const | 1 | |
| 1 | SIMPLE | faqvisits | ref | PRIMARY | PRIMARY | 11 | faq.faqdata.id,const | 1 | Using where |
+----+-------------+----------------------+------+-----------------------------+--------------------+---------+----------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)
mysql> SHOW INDEXES FROM faqdata;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| faqdata | 0 | PRIMARY | 1 | lang | A | NULL | NULL | NULL | | BTREE | |
| faqdata | 0 | PRIMARY | 2 | id | A | 9999 | NULL | NULL | | BTREE | |
| faqdata | 1 | idx_lang | 1 | lang | A | 1 | NULL | NULL | | BTREE | |
| faqdata | 1 | idx_active | 1 | active | A | 1 | NULL | NULL | | BTREE | |
| faqdata | 1 | keywords | 1 | keywords | NULL | 1 | NULL | NULL | | FULLTEXT | |
| faqdata | 1 | keywords | 2 | thema | NULL | 1 | NULL | NULL | | FULLTEXT | |
| faqdata | 1 | keywords | 3 | content | NULL | 1 | NULL | NULL | | FULLTEXT | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.01 sec)
An dieser Ausgabe stören noch “type” = ALL (Tablescan für faqdata) und “type” = ref . An letzerem läßt sich bei 1:n Beziehungen nichts drehen. SHOW INDEXES und die Spalte “Cardinality” zeigen warum hier die zusätzlich angebotenen Indizes idx_lang für faqdata.lang = 'de' und idx_active für faqdata.active = 'yes' nicht beachtet werden. Die Anzahl der unterschiedlichen Werte ist zu gering, ein Indexscan verspricht keine Geschwindigkeitsvorteile gegenüber einem Tabellenscan.
Der Rest
Haben Sie mitgedacht? Dann kennen Sie schon die Empfehlung für die Tabelle “faqnews”: ein Index könnte die Sortierung beschleunigen, aber es lohnt sich nur, wenn wirklich Daten drin sind. Ich würde langfristig Daten in der Tabelle erwarten und schlage deshalb vor CREATE INDEX idx_datum ON faqnews(datum DESC). Es bleibt noch Anfrage (7) SELECT id FROM faqdata WHERE active = 'yes'. Hier habe ich wenig Hoffnung aufgrund der vermutlich geringen Selektivität von acitve = 'yes' wird es immer wieder zu Tablescans kommen.
mysql> EXPLAIN SELECT datum, header, artikel, link, linktitel, target
-> FROM faqnews ORDER BY datum desc;
+----+-------------+---------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+---------------+------+---------+------+------+---------------------+
| 1 | SIMPLE | faqnews | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+----+-------------+---------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.00 sec)
mysql> show create table faqnews;
+---------+------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------+
| faqnews | CREATE TABLE `faqnews` (
`id` int(11) NOT NULL,
`header` varchar(255) NOT NULL,
`artikel` text NOT NULL,
`datum` varchar(14) NOT NULL,
`link` varchar(255) NOT NULL,
`linktitel` varchar(255) NOT NULL,
`target` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------+
1 row in set (0.00 sec)
Note: 2+
Mit nur einem neuen Index CREATE INDEX idx_record_id_lang(record_id, record_lang) ON faqcategoryrelations macht phpMyFAQ nach dem Kurztest richtig Spaß. Bislang habe ich nur gelabert über einen Freund. Doch die ultimative Lobdudelei darf nicht verschweigen, daß die Benutzung der Anwendung selbst Spaß gemacht hat! Für eine private, kostenloste Open-Source Software scheint die Doku sehr gut zu sein. Ich bitte, um Beteiligung an Thorsten’s Wunschliste. Es muß ja nicht gleich Auto, Haus, Yacht, Digitalkamera und Glotze sein.
Thorsten, ich freue mich auf die MaxDB-Version – nach dem Urlaub. Du glaubst nicht, welche Freude es mir machen wird mal einen weiteren Test zu machen.
Pingback: phpMyFAQ devBlog » Blog Archive » phpMyFAQ database scheme analyzed
Pingback: phpMyFAQ devBlog » Blog Archive » Status update for phpMyFAQ 1.5.2