Performanceproblem mit DB Abfrage

**AmicaNoctis** · 18.03.2010, 19:03

Hallo,

seh ich das richtig, dass du Mehrfeldprimärschlüssel dort drin hast? Das könnte das Problem sein. Ansonsten sieht's super aus: zwei Equi-Joins, besser geht's nicht.

Gruß,

Amica

**xtramen** · 18.03.2010, 19:31

Hi,

also wenn du meinst das teilweise der PRIMARY Key 2 Werte hat....das stimmt.
Aber eigentlich macht es doch keinen Unterschied ob ich einen Index mit 2 Werten erstelle oder ob 2 Werte im PRIMARY Key vorhanden sind.

Die Werte im PRIMARY Key werden ja auch abgefragt bzw. benötigt für die Abfrage.

Beispiel: Tabelle products_description benötigt für die Abfrage einen Index mit products_description und language_id. Beide Werte sind im PRIMARY Key vorhanden.
Und soweit ich sehen kann werden alle Indizes von MySQL richtig verwendet.

Bitte korrigiert mich wenn ich mich Irre.

Gruss

**AmicaNoctis** · 18.03.2010, 20:13

Das macht schon einen Unterschied, es sei denn, du vergleichst immer alle schlüsselbildenden Felder. Das tust du aber nicht, also muss MySQL den Index teilen. Probier's einfach mal mit Einfeldprimärschlüsseln aus. Du kannst ja trotzdem einen zweiten Unique Key auf die betreffenden Spalten legen, wenn du willst, aber nur eine Spalte sollte den Primärschlüssel bilden.

**xtramen** · 18.03.2010, 20:27

Nur nochmal um dich nicht falsch zu verstehen:

Aus dieser Tabelle:

Code:

CREATE TABLE IF NOT EXISTS `products_description` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL DEFAULT '1',
  `products_name` varchar(64) NOT NULL,
  `products_description` text,
  `products_url` varchar(255) DEFAULT NULL,
  `products_viewed` int(5) DEFAULT '0',

  PRIMARY KEY (`products_id`,`language_id`),
  KEY `products_id` (`products_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40000;

sollte diese werden:

Code:

CREATE TABLE IF NOT EXISTS `products_description` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL DEFAULT '1',
  `products_name` varchar(64) NOT NULL,
  `products_description` text,
  `products_url` varchar(255) DEFAULT NULL,
  `products_viewed` int(5) DEFAULT '0',

  PRIMARY KEY (`products_id`),
  KEY `products_id` (`products_id`),
  KEY `products_id+language_id` (`products_id`, `language_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40000;

Und es macht dann ein Unterschied ob der Optimizer bei der ersten Variante auf PRIMARY zugreift oder in der unteren Variante auf den Index products_id+language_id?

Gruss und danke für die Mühe!

**AmicaNoctis** · 18.03.2010, 20:30

Fast ja. Den zweiten Key kannst du dir schenken, der ist identisch zum Primärschlüssel.

**xtramen** · 18.03.2010, 22:15

Also ich habs nun mal getestet.
Macht leider keinen Unterschied.

Ich hab keinen Plan mehr warum die Abfrage solange dauert.

Sonst gibt es ja nicht viel Möglichkeiten an was es liegen kann.

Noch ne Idee?

Gruss

**AmicaNoctis** · 18.03.2010, 22:25

Hast du bei den anderen Tabellen auch alles in Einfeldprimärschlüssel geändert - mindestens dort, wo nur eine Spalte davon benutzt wird?

Ansonsten wäre es hilfreich zu wissen, ob du lokal auf die Datenbank zugreifst und wie du diese Zeit misst, also ob da auch die Übertragung der Daten mit eingeschlossen ist.

**xtramen** · 18.03.2010, 23:38

Ich hab die DB mit XAMPP lokal auf meinem Rechner.
Die Zeit entnehme ich von phpmyadmin.
Gibt es da noch andere Möglichkeiten die Zugriffszeiten rauszufinden?

Ich habe alle relevanten Tabellen geändert.

Gruss

**AmicaNoctis** · 18.03.2010, 23:56

Ja, über die Kommandozeile, da ist bei mir der Wert immer kleiner als z. B. in PHPMyAdmin. Arbeitest du an einem Notebook? Wie stark variiert die Zeit bei z. B. 10 Versuchen?

**xtramen** · 19.03.2010, 00:04

Mit KOmmandozeile kenn ich mich leider nich so aus.
Was müsste ich da eingeben?

Ich arbeite an einem relativ starken PC mit Windows 7.

Die Zeiten variieren um ca. 0.01 - 0.05 sek speziell bei dieser Abfrage.
Manchmal hab ich auch utopische Zahlen da stehn, aber ganz selten. Wie z.b. 999.999999 sek.

**AmicaNoctis** · 19.03.2010, 00:33

Du öffnest ein Konsolenfenster, gehst in das bin-Verzeichnis von MySQL (nur für den Fall, dass dein PATH nicht gesetzt ist) und schreibst rein:

Code:

mysql -u[I]USER[/I] -p[I]PASS[/I]
use [I]DBNAME[/I]
select ... from ... where ...[B];[/B]

Edit: Semikolon nicht vergessen

**xtramen** · 19.03.2010, 01:14

Mal eine grundlegende Frage zu Indizes.
Man sollte doch für jede abgefragte Spalte einen Index setzen.

Wenn möglich nur die PRIMARY Keys vergleichen...wenn das nicht geht auf jedenfall einen Index setzen.

So!....jetzt habe ich in einer Abfrage 2 Werte derselben Spalte welche ich vergleiche.

z.b.

Code:

WHERE p.products_id =  p2c.products_id
AND p2c.categories_id = cd.categories_id

Hier wäre es doch sinnvoll in der Tabelle p2c einen 2 Spaltigen Index zu setzen.
Ist es anscheinend nicht. Denn wenn ich das tue braucht die Abfrage 0.25 Sekunden für 20.000 Datensätze LIMIT 10.

Das ist eeeewig!

Wenn ich allerdings den Index oder PRIMARY nur auf products_id setze dann dauert die Abfrage lediglich 0.0002 Sekunden.

Fern mir jeglicher Logik.
Sorry für die vielen Fragen aber ich bin grad am Input sammeln.
Hab mich zuvor nicht so sehr mit Datenbanken beschäftigt.

Aber was meinst du dazu?

**AmicaNoctis** · 19.03.2010, 01:24

Vergiss das mal mit deinen mehrspaltigen Indizes

Die bringen nur was, wenn sie auch jeweils paarweise verglichen werden.

Beispiel: Tabelle A hat Spalte A1, A2 und A3. Tabelle B hat Spalte B1, B2 und B3. Wenn eine Abfrage (on-Klausel eines Joins oder Where-Klausel)

Code:

A1 = B1 and A2 = B2

fordert, macht ein Mehrfeldschlüssel über A1 und A2 nur Sinn, wenn er analog auch in Tabelle B über B1 und B2 existiert und am besten die Datentypen auch noch paarweise gleich sind (A1 und B1 vom gleichen Typ und auch A2 und B2).

Ein weiterer Anwendungsfall sind mehrspaltige Unique-Indizes, wenn man sicherstellen will, dass z. B. nicht nur der Nachname in der Tabelle eindeutig sein soll, sondern die Kombination aus Nachname und Geburtsdatum eindeutig sein muss, aber ein Nachname mehrfach vorkommen darf, wenn das Geburtsdatum jeweils unterschiedlich ist.

In allen anderen Fällen solltest du keine Mehrfeldschlüssel verwenden. Immer wenn MySQL den erst aufspalten muss, weil nur eine Spalte aus dem Mehrfeldschlüssel verglichen wird, verlierst du Performance.

**xtramen** · 19.03.2010, 09:54

Alles klar.
Solangsam kapiere ich das besser mit den indizes.
Hab aufgrund dieses Beitrages 10 Tabellen optimieren können.

Danke erstmal dafür. Ohne dich wär ich noch tagelang da drann gesessen :-)

Wenn ich deine Hilfe noch einmal in Anspruch nehmen dürfte?! :-)
Denn bei folgender Abfrage welche ich von vorher 0.2 sek auf nun 0.0005 sek optimieren konnte, besteht noch ein kleines Problem wofür ich keine Erklärung habe.

Ich will aus:

Code:

select count(*) as total

from products p

inner join products_to_categories p2c on p.products_id = p2c.products_id
inner join products_description pd on pd.products_id = p2c.products_id,

manufacturers m ,
specials s

where p.products_status = '1'

and p.manufacturers_id = m.manufacturers_id
and p.products_id = s.products_id
and pd.language_id = '2'
and p2c.categories_id = '52'
and (p.section_id = '0' || p.section_id = '13')

dieses machen:

Code:

select count(*) as total

from products p

inner join products_to_categories p2c on p.products_id = p2c.products_id
inner join products_description pd on pd.products_id = p2c.products_id,

manufacturers m 

where p.products_status = '1'

and p.manufacturers_id = m.manufacturers_id
and pd.language_id = '2'
and p2c.categories_id = '52'
and (p.section_id = '0' || p.section_id = '13')

Wenn ich die Spalte specials nicht mehr in der Abfrage drinn habe dann braucht die Abfrage 0.3 - 0.4 sekunden anstatt 0.0005 sek.

Müsste doch eigentlich weniger bzw genauso schnell sein wie vorher.
Denn ich frage ja weniger Spalten ab.
Das Ergebniss(Datensätze) ist allerdings dasselbe, ist auch gewollt so.

Ich komm einfach nich drauf und das fuchst mich so, das glaubst du gar nicht!

P.S. was mir spontan auffällt ist das p2c vorher den Primary Key verwendet und nachher NULL. Zwinge ich den Optimizer den primary zu verwenden hilft das aber auch nich weiter.

Haste vielleicht noch ne Idee?

Performanceproblem mit DB Abfrage