✔ MySql Abfrage optimieren

Thomasio · 5. Oktober 2011

Ich schreibe ein Übersetzungstool für eine Multilingua-Seite.
Die Seite nutzt eine MySql Tabelle, wo alle Texte in folgenden Spalten stehen

id | folder | page | piece | language | content

Inhalt der Zeilen wäre z.B.

1 | members | login | st_title | 1 | Log In
2 | members | login | st_title | 2 | Einloggen

Das Ganze funktioniert super, bis auf eine Kleinigkeit.
Damit der Übersetzer sich nicht dusselig sucht, auf welchen Seiten noch Übersetzungen fehlen, will ich ihm eine hübsch nach Ordner und Seite sortierte Liste ausgeben, die nur die Seiten anzeigt, wo mindestens ein Teil fehlt.
Problem ist, das sind heftige viele Zeilen, so wie ich es jetzt habe dauert die Abfrage 35 Sek., aber max script runtime ist beim Provider nur 30 Sek., sprich das gibt kein Ergebnis, sondern Timeout.
Darum die Frage: Kann ich den folgenden Code irgendwie optimieren, so dass es schneller geht?

Code:

$allfiles = array();

$abfrage = mysql_query("SELECT DISTINCT `folder` FROM `sitetext` WHERE `language` = 1 ORDER BY `folder`");
while($row = mysql_fetch_object($abfrage)) {

$abfrage2 = mysql_query("SELECT DISTINCT `page` FROM `sitetext` WHERE `folder` = '".$row->folder."' AND `language` = 1 ORDER BY `page`");
while($row2 = mysql_fetch_object($abfrage2)) {

$abfrage3 = mysql_fetch_array(mysql_query("SELECT COUNT(`id`) FROM `sitetext` WHERE `folder` = '".$row->folder."' AND `page` = '".$row2->page."' AND `language` = 1"));

$abfrage4 = mysql_fetch_array(mysql_query("SELECT COUNT(`id`) FROM `sitetext` WHERE `folder` = '".$row->folder."' AND `page` = '".$row2->page."' AND `language` = 2 AND `content` <> ''"));

if ($abfrage3[0] != $abfrage4[0]) {
$allfiles[] = $row->folder."/".$row2->page;
}

}
}

Edit: Sorry, bin im falschen Forum gelandet, kann das jemand bitte nach PHP verschieben?

Bratkartoffel · 5. Oktober 2011

Hi,

würde hier die Struktur deiner MySQL-Tabelle etwas mehr nach den Normalformen gestalten.
Was ich damit im Speziellen meine, ist die Aufteilung in mehrere Tabellen:

============================================
languages:
bezeichnung: varchar

folders:
name: varchar

pages:
name: varchar

pieces:
name: varchar

sitetext:
id: int
folder: varchar (REFERENCES folders.name)
page: varchar (REFERENCES pages.name)
piece: varchar (REFERENCES pieces.name)
language: varchar (REFERENCES languages.bezeichnung)
content: varchar
============================================
Legende:
Fett: Tabelle
Unterstrichen: Primärschlüssel
Kursiv: Index / Fremdschlüssel

Das sollte deine Performanceprobleme größtenteils beseitigen

Gruß
BK

Thomasio · 5. Oktober 2011

Das geht leider nicht, weil die Tabelle nicht von mir ist.
Ich soll nur das Übersetzungstool (neu) schreiben, nachdem der original Programmierer sich dünn gemacht hat.
Hauptproblem ist, der Inhaber der Seite hat genausoviel Angst wie keine Ahnung, wenn ich dem vorschlage die Struktur seiner DB grundlegend zu ändern bekommt er vermutlich Schüttelfrost oder sowas.

Auf jeden Fall danke für den Tipp, ich werde mal versuchen, wie weit ich die Jungs überreden kann.

Falls noch jemand eine Idee hat, wie es ohne Reorganisation der Tabelle geht, immer her damit.

deepthroat · 5. Oktober 2011

Hi.

Der Datenbank Bereich wäre wohl passender gewesen. Du hast da ein RDBMS - warum benutzt du es nicht? ;-]

SQL:

select lang1.folder, lang1.page from
  (select folder, page, count(`id`) as ids from
   sitetext where language = '1'
   group by folder, page) as lang1
inner join
  (select folder, page, count(`id`) as ids from
   sitetext where language = '2'
   group by folder, page) as lang2
using (folder, page)
where lang1.ids <> lang2.ids

Gruß

Bratkartoffel · 5. Oktober 2011

Hi,

wenn du dir ein Backup deiner Datenbank erstellst und jeglichen Zugriff von aussen blockierst, dann sollte die Änderung...
a) nicht großartig sein
b) nicht schiefgehen können
c) die performance dauerhaft erhöhen.

Falls du ihn von der Änderung überzeugen konntest, hier mal mein Ansatz (nur rudimentär getestet, würde das vorher an einer Kopie der Originaldatenbank testen bevor du das auf die "kritischen" Datensätzen loslässt

)

SQL:

-- Transaktion starten
SET AUTOCOMMIT=0;
START TRANSACTION;

-- Tabellen erstellen
CREATE TABLE `languages` (
  `bezeichnung` VARCHAR(32) PRIMARY KEY NOT NULL
) ENGINE=innodb;
 
CREATE TABLE `folders` (
  `name` VARCHAR(64) PRIMARY KEY NOT NULL
) ENGINE=innodb;
 
CREATE TABLE `pages` (
  `name` VARCHAR(64) PRIMARY KEY NOT NULL
) ENGINE=innodb;
 
CREATE TABLE `pieces` (
  `name` VARCHAR(64) PRIMARY KEY NOT NULL
) ENGINE=innodb;
 
-- Tabellen füllen
INSERT INTO `languages` SELECT DISTINCT `language` FROM `sidetext`;
INSERT INTO `pages` SELECT DISTINCT `page` FROM `sidetext`;
INSERT INTO `folders` SELECT DISTINCT `folder` FROM `sidetext`;
INSERT INTO `pieces` SELECT DISTINCT `piece` FROM `sidetext`;
 
-- Tabelle sidetext anpassen und Indices sowie Fremdschlüssel setzen
ALTER TABLE `sidetext` ENGINE innodb;
ALTER TABLE `sidetext` CHANGE `language` `language` VARCHAR(32) NOT NULL;
 
ALTER TABLE `sidetext`
	ADD INDEX(`folder`),
	ADD INDEX (`piece`),
	ADD INDEX (`page`),
	ADD INDEX (`language`);

ALTER TABLE `sidetext`
  ADD CONSTRAINT `sidetext_fk_folder` FOREIGN KEY (`folder`) REFERENCES `folders` (`name`) ON UPDATE CASCADE,
  ADD CONSTRAINT `sidetext_fk_language` FOREIGN KEY (`language`) REFERENCES `languages` (`bezeichnung`) ON UPDATE CASCADE,
  ADD CONSTRAINT `sidetext_fk_page` FOREIGN KEY (`page`) REFERENCES `pages` (`name`) ON UPDATE CASCADE,
  ADD CONSTRAINT `sidetext_fk_piece` FOREIGN KEY (`piece`) REFERENCES `pieces` (`name`) ON UPDATE CASCADE;

-- Alles fertig, Änderungen durchziehen
COMMIT;

Vollautomatisch innerhalb einer Transaktion

Gruß
BK

Thomasio · 5. Oktober 2011

Ich danke euch beiden.
Inzwischen habe ich mit ihm gesprochen und ich hatte die Idee mit einer Kopie der DB auch schon.
Er will es sich überlegen hat er gesagt.
Bis dahin probiere ich mal den Vorschlag von deepthroat.
Nochmals vielen Dank.

deepthroat · 6. Oktober 2011

Hi.

Bratkartoffel hat gesagt.:

SQL:

-- Tabellen erstellen
CREATE TABLE `languages` (
  `bezeichnung` VARCHAR(32) PRIMARY KEY NOT NULL
) ENGINE=innodb;
 
CREATE TABLE `folders` (
  `name` VARCHAR(64) PRIMARY KEY NOT NULL
) ENGINE=innodb;
 
CREATE TABLE `pages` (
  `name` VARCHAR(64) PRIMARY KEY NOT NULL
) ENGINE=innodb;
 
CREATE TABLE `pieces` (
  `name` VARCHAR(64) PRIMARY KEY NOT NULL
) ENGINE=innodb;
 
-- Tabellen füllen
INSERT INTO `languages` SELECT DISTINCT `language` FROM `sidetext`;
INSERT INTO `pages` SELECT DISTINCT `page` FROM `sidetext`;
INSERT INTO `folders` SELECT DISTINCT `folder` FROM `sidetext`;
INSERT INTO `pieces` SELECT DISTINCT `piece` FROM `sidetext`;
 
-- Tabelle sidetext anpassen und Indices sowie Fremdschlüssel setzen
ALTER TABLE `sidetext` ENGINE innodb;
ALTER TABLE `sidetext` CHANGE `language` `language` VARCHAR(32) NOT NULL;
 
ALTER TABLE `sidetext`
	ADD INDEX(`folder`),
	ADD INDEX (`piece`),
	ADD INDEX (`page`),
	ADD INDEX (`language`);

ALTER TABLE `sidetext`
  ADD CONSTRAINT `sidetext_fk_folder` FOREIGN KEY (`folder`) REFERENCES `folders` (`name`) ON UPDATE CASCADE,
  ADD CONSTRAINT `sidetext_fk_language` FOREIGN KEY (`language`) REFERENCES `languages` (`bezeichnung`) ON UPDATE CASCADE,
  ADD CONSTRAINT `sidetext_fk_page` FOREIGN KEY (`page`) REFERENCES `pages` (`name`) ON UPDATE CASCADE,
  ADD CONSTRAINT `sidetext_fk_piece` FOREIGN KEY (`piece`) REFERENCES `pieces` (`name`) ON UPDATE CASCADE;

-- Alles fertig, Änderungen durchziehen
COMMIT;

Das finde ich nicht wirklich sinnvoll. Wozu die extra Tabellen? Ein UNIQUE INDEX auf die entsprechenden Spalten hätte genau den gleichen Effekt.

Man hätte höchstens einen stellvertretenden Primärschlüssel einführen können, da wäre eine extra Tabelle durchaus sinnvoll gewesen.

Außerdem bedeuten die Indizes und Fremdschlüssel einen Mehraufwand für die Datenbank (bei Änderungen der Daten). Da sollte man erstmal das Gesamtbild anschauen, bevor man da auf eine bestimmte Abfrage hin optimiert.

Gruß

Bratkartoffel · 6. Oktober 2011

Hi,

klar gibt es keine 0815-Lösung zur Optimierung, lediglich Vorschläge die je nach bestehenden Schema getestet und analysiert werden müssen.

Jetzt wo ich mir meine Abfragen so nochmals durchsehe und drüber nachdenke fallen mir auch noch einige Änderungen ein, die ich machen würde. War anscheinend gestern nicht mehr so ganz auf der Höhe und hab im Eifer des Gefechts dann mit Kannonen auf Spatzen gezielt

. Kann aber auch sein, dass ich ein notorischer Datenbank-Struktur-Verbesserer bin, spiele mich da gern rum um die verschiedenen Möglichkeiten zu betrachten.

Was mir jetzt noch fix für den ersten Post einfällft, ist dass man eventuell das "mysql_fetch_object" durch ein "mysql_fetch_assoc" austauschen könnte. Soweit ich das noch richtig im Kopf hab, war das um einiges schneller. (Edit: Benchmark)

Edit: @thomasio: Bitte melde dich dann nochmal wenn du dich für eine Lösung entschieden hast und um wieviel sich deine Ergebnisse verbesser haben

Gruß
BK

Thomasio · 13. Oktober 2011

Sorry, war ein paar Tage im Urlaub, darum späte Antwort.
Deine Lösung haut leider nicht hin, weil pages und pieces mehrfach den selben Eintrag haben können, erst in der Kombination von folder/page/piece wird es unique.
Mit dem Vorschlag von deepthroat gewinne ich genug Zeit um unterm Timeout zu bleiben, wieviel genau kann ich nicht sagen, weil sich die Daten ständig ändern, wenn die Übersetzer an der Arbeit sind, geschätzt ca. 40%.
Nochmal danke euch beiden, für den Moment ist mir geholfen.

✔ MySql Abfrage optimieren

Thomasio

Erfahrenes Mitglied

Bratkartoffel

gebratene Kartoffel

Thomasio

Erfahrenes Mitglied

deepthroat

Erfahrenes Mitglied

Bratkartoffel

gebratene Kartoffel

Thomasio

Erfahrenes Mitglied

deepthroat

Erfahrenes Mitglied

Bratkartoffel

gebratene Kartoffel

Thomasio

Erfahrenes Mitglied

Neue Beiträge