MySQL sehr langsam

F

Felix Weber

Angesehenes Mitglied
Hallo,
ich brauche eure Hilfe:
ich habe gerade eine MySQL Datenbank eingerichtet.
Die Datenbank enthält eine Tabelle mit folgender Struktur:

id idnumber name
Beispiel: 1 4002293900704 Max Mutermann

Die Datenbank hat davon etwa 13,000,000 Einträge.

Sope, ich durchsuche die Datenbank jetzt nach der idnummer um den dazu gehörigen Namen
zu bekommen.

CODE SELECT *
FROM `users`
WHERE `idnumber` = '4002293900704'
LIMIT 0 , 30


Diese Abfrage braucht in phpMyAdmin etwa 12 bis 14 Sekunden!
Per PHP ist die Abfrage auch sehr langsam.

Das ganze läuft auf einen Virtual Server mit 1 GB RAM.

Womit könnte ich die Performance steigern, vor allem unter dem Gesichtspunkt, dass
die Größe der Datenbank zunehmen wird.
Bringt ein Update auf "2 GB RAM" etwas?

Danke für eure Hilfe!
 
CODE SELECT `name`
FROM `users`
WHERE `idnumber` = '4002293900704'
LIMIT 0 , 30


anstatt *

Weiterhin sollten dir richtig gesetzte Indizes einen Performanceschub bringen.



MfG
TTlong
 
Wenn es eindeutig ist wird kein LIMIT benötigt.


Wie ist die Tabelle aufgebaut?
Ist idnummer mit einen Index versehen?
Was gibt ein EXPLAIN des Query zurück?
 
Hallo,
alle Empfehlungen haben nicht wirklich etwas gebracht!
Die Zeit die eine Abfrage braucht schwankt immer noch zwischen 11 und 12 Sekunden!

EXPLAIN sagt:

CODE Field Type Null Key Default Extra
id bigint(20) NO PRI NULL auto_increment
idnumber varchar(255) NO NULL
name varchar(255) NO NULL

 
QUOTE (Felix Weber @ Fr 24.02.2012, 12:38) Hallo,
alle Empfehlungen haben nicht wirklich etwas gebracht!
Die Zeit die eine Abfrage braucht schwankt immer noch zwischen 11 und 12 Sekunden!


wie lange dauert ein

CODE
SELECT `name`
FROM `users`
WHERE `id` = '6502'


 
QUOTE (Felix Weber @ Fr 24.02.2012, 11:38)[...]
EXPLAIN sagt:


CODE Field  Type  Null  Key  Default  Extra
id  bigint(20)  NO  PRI  NULL  auto_increment
idnumber  varchar(255)  NO     NULL  
name  varchar(255)  NO     NULL  




Da kein Index auf idnummer ist, gibt es vermutlich verscheidene Möglichkeiten:
  • idnumber mit einen Teilindex versehen von bspw. 10 Zeichen (was bei größeren Zahlen mit 15 und mehr Zeichen wieder zu gleichen Problemen führen kann)
  • idnumber mit einen kompletten Index versehen, was zur Folge hat, dass ggf. eine kleine Tabelle sich zu sehr aufbläht und der Index die Situation nur noch verschlechtert
  • Umwandeln des Feldes in ein INT-, BIGINT-Feld, und setzen eines Index
  • Umwandeln des Feldes in ein INT-, BIGINT-Feld, und setzen eines UNIQUE Index, wenn die Zahl nur einmal vergeben werden soll
Aber das setzen eines neuen Index wurde glaube ich bereits angesprochen. Ich würde hier auch wohl dazu Raten das Feld in den Datentyp zu konvertieren, der auch gespeichert werden soll. Da es sich bei der einen Abfrage zu mindestens um eine Zahl handelt also vermutlich als BITINT. Wenn keine negativen Zahlen benötigt werden, kann auch noch ein UNSIGNED als Option gesetzt werden.


Nichts desto trotz wäre eine gesamte Tabellenstruktur ggf. immer noch Hilfreich um abschätzen zu können, wie man vorgehen kann.



PS: Die Namenswahl idnumber ist meiner Meinung nach auch sehr problematisch.
 
Das sieht katastrophal aus
wink.gif


CODE id  bigint(20)  NO  PRI  NULL  auto_increment


bigint(20) wahrscheinlich zu viel.

Ein int(9) sollte hier reichen und lässt fortlaufende Nummern (Integerwerte) bis zu 999 Millionen zu.

Keinen Defaultwert setzen.



CODE idnumber  varchar(255)  NO     NULL


Sofern hier nur Zahlen (Integerwerte) reinkommen, ebenfalls statt varchar(255) ein int(9). Bin mir grad nicht sicher ob ein int(x) führende Nullen zulässt, aber wenn du keine führenden Nullen hast, auf jeden Fall int() nutzen. Negativ sollten diese Werte ja ohnehin nicht sein nehm ich mal an. Default NULL raus.

Auf diese Spalte einen Index setzen. Wert 3-6 je nachdem wie groß die Werte sind bzw. wieviele Stellen diese Werte haben.

Ich hatte dir dazu einen Beitrag vorher den Link zu MySQL gepostet, wenn du diesen liest solltest du verstehen, warum richtig gesetzte Indizes so verdammt wichtig sind, gerade bei mehreren Millionen Einträgen.


CODE name  varchar(255)  NO     NULL


varchar(255) verkürzen. Vielleicht auf varchar(30) bis varchar(50). Da musst du einfach mal schauen wie lang die Namen sind/sein dürfen. Wenn die Namen bsp. aus einem Formularfeld kommen und dort ohnehin nur 30 Zeichen zulässig sind, macht es keinen sind MySQL 255 Zeichen dafür verwenden/reservieren zu lassen.

Default NULL raus.


Vielleicht auch mal ein gutes Buch lesen indem verständlich erklärt wird, wie MySQL was speichert, welcher Platz für was reseviert wird und wie MySQL mit verschiedenen Datentypen umgeht. Das sollte dir helfen in Zukunft ein Datenbankdesign halbwegs vernünftig zu planen und umzusetzen.


MfG TTlong
 
QUOTE (TTlong @ Fr 24.02.2012, 13:37)Vielleicht auch mal ein gutes Buch lesen

oder einfach mal die Forenantworten lesen

(ups, Du selbt sagtes das was nicht gelesen wurde .... )


QUOTE (TTlong @ Do 23.02.2012, 18:41)richtig gesetzte Indizes einen Performanceschub bringen.


QUOTE (Felix Weber @ Fr 24.02.2012, 12:38)Hallo,
alle Empfehlungen haben nicht wirklich etwas gebracht!
Die Zeit die eine Abfrage braucht schwankt immer noch zwischen 11 und 12 Sekunden!

EXPLAIN sagt:

Field Type Null Key Default Extra
id bigint(20) NO PRI NULL auto_increment
idnumber varchar(255) NO NULL
name varchar(255) NO NULL

Eine Empfhlung die man nicht umsetzt, kann auch schlecht etwas bringen


Field Type Null Key Default Extra
id bigint(20) NO ****PRI**** NULL auto_increment
idnumber varchar(255) NO ****hier sehe ich nix**** NULL
name varchar(255) NO NULL
 
Hallo,

Danke erst mal für euer Engagement!

Ich habe leider noch sogut wie nicht mit MySQL gemacht und die Datenbank von jemanden Externes aufbauen lassen.

Ich habe nun folgende Änderungen gemacht:

CODE id bigint(20) NO PRI NULL auto_increment

auf int(9)


CODE idnumber varchar(255) NO NULL

auf int(9)


CODE name varchar(255) NO NULL

werde ich wohl auf 130 reduzieren!

Aber wie setzte ich dies um:

CODE Default NULL raus.
Auf diese Spalte einen Index setzen. Wert 3-6 je nachdem wie groß die Werte sind bzw. wieviele Stellen diese Werte haben.

?

oder

CODE Umwandeln des Feldes in ein INT-, BIGINT-Feld, und setzen eines UNIQUE Index, wenn die Zahl nur einmal vergeben werden soll

Ist jetzt int, aber wie setzte ich den neuen Index?


CODE Nichts desto trotz wäre eine gesamte Tabellenstruktur ggf. immer noch Hilfreich um abschätzen zu können, wie man vorgehen kann.

Sicherlich! Wie?
wink.gif




CODE
SELECT `name`
FROM `users`
WHERE `id` = '6502'


Braucht nach den oben genannten Änderungen etwa 1 Sekunde! (Wenn ich mich nicht geirrt habe!)



QUOTE Vielleicht auch mal ein gutes Buch lesen
oder einfach mal die Forenantworten lesen
(ups, Du selbt sagtes das was nicht gelesen wurde .... )


Sorry, ich nutze die MySQL nur als BackEnd für eine Andorid App und mir fehlt es einfach am Wissen!
sad.gif

 
uy da hab ich zu früh gesendet, hab schnell noch nen Edit(löschung vorne) gemacht ....



QUOTE (Felix Weber @ Fr 24.02.2012, 17:47)
CODE
SELECT `name`
FROM `users`
WHERE `id` = '6502'


Braucht nach den oben genannten Änderungen etwa 1 Sekunde! (Wenn ich mich nicht geirrt habe!)


und warum?

grün(1sekunde) rot(12sekunden) .... darum

QUOTE (Felix Weber @ Fr 24.02.2012, 17:47)

CODE idnumber  varchar(255)  NO     NULL

auf int(9)

aber das rote ist immernoch rot




CODE SHOW CREATE TABLE  `users`

zeig mal was das spricht ... (vielleicht spricht das EXPLAIN ja mit geheinhaltung? vielleicht ist mein rot hier ja grün, was ich aber nicht glaube)

CODE ALTER TABLE `users`  ADD INDEX `idnumber` ( `idnumber` )

misst die Stoppuhr immernoch 12 Sekunden?
 
Hallo,

QUOTE aber das rote ist immernoch rot

Ich würde es so gerne grün machen, weiß aber nicht wie
wink.gif
!


CODE SHOW CREATE TABLE  `users`

sagt:

CODE
Table  Create Table
users  CREATE TABLE `users` (
`id` int(9) NOT NULL AUTO...




CODE ALTER TABLE `users`  ADD INDEX `idnumber` ( `idnumber` )

->
Ihr SQL-Befehl wurde erfolgreich ausgeführt. ( die Abfrage dauerte 130.6512 sek. )



QUOTE misst die Stoppuhr immernoch 12 Sekunden?

also

CODE SELECT `name`
FROM `users`
WHERE `idnumber` = '9783150079775'
LIMIT 0 , 30

braucht jetzt 70.6969 sek!

Und


CODE EXPLAIN SELECT `name`
FROM `users`
WHERE `idnumber` = '4002293900704'
LIMIT 1

gibt zurück
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users ref idnumber idnumber 4 const 10 Using where
 
Mit der Option NOT NULL, über phpMyAdmin geht das relativ leicht, ansonsten:

SQL ALTER TABLE tablename CHANGE columnname_old columnname_new VARCHAR(130) NOT NULL;
 
Gib doch einfach mal die Tabellenstruktur, sonst kann man Dir auch nicht mehr richtig helfen. Der Befehl von XE800 hätte die gesamte Struktur ausliefern müssen und dürfte eigentlich nicht mit ... auf Kommandozeile enden.

Gebe uns bitte nach möglichkeit folgende Informationen:
  • Komplette Tabellenstruktur mit den gesetzten Indexen der aktuellen Tabelle, entweder durch den Befehl SHOW CREATE TABLE tbl_name; (sollte auf Kommandozeile alles ausgeben) oder den Export von phpMyAdmin
  • Anzahl der aktuellen Datensätze
  • Aktuelle Tabellengröße (in KB, MB, GB oder TB)
  • Beantwortung bitte die Frage, wie mit idnumber umzugehen ist (UNIQUE? Zahl? (vermutlich) Kann diese auch negativ sein?)
  • Bitte die Ausgabe von EXPLAIN EXTENDED SELECT `name` FROM `users` WHERE `idnumber` = '9783150079775' LIMIT 0 , 30; von der aktuellen Tabelle
 
Hallo,

SHOW CREATE TABLE tbl_name;

CODE CREATE TABLE `users` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`idnumber` int(9) NOT NULL,
`name` varchar(255) COLLATE latin1_german2_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `idnumber` (`idnumber`)
) ENGINE=MyISAM AUTO_INCREMENT=13048542 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci


Anzahl der aktuellen Datensätze 13,048,540
Tabellengröße 1,0 GiB


QUOTE Beantwortung bitte die Frage, wie mit idnumber umzugehen ist (UNIQUE? Zahl? (vermutlich) Kann diese auch negativ sein?)

UNIQUE -> theoretisch ja, aber beim import sind mir leider einige Einträge doppelt importeirt worden.
Zahl? -> ja
negativ -> nein

EXPLAIN EXTENDED SELECT `name` FROM `users` WHERE `idnumber` = '9783150079775' LIMIT 0 , 30;[/B]


CODE
id  select_type  table  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1  SIMPLE  users  ref  idnumber  idnumber  4  const  10  100.00  Using where



Danke euch!
 
Dann würde ich Dir als erstes dazu raten genau dies zu korrigieren.
Nun habe ich aber leicht den Eindruck, das es sich hierbei um eine Verknüpfungstabelle handelt, oder das der Schüssel id ggf. nicht benötigt wird.
Wird id wirklich benötigt? Ich empfinde zwei UNIQUE-Keys etwas seltsam wovon einer vorgeben wird und der andere noch hochgezählt.

Nun war die Zahl der Abfrage bisher immer 10 Zeichen lang, daher wundert mich etwas das int(9). Du kannst aber allgemein Deine Integer-Werte mit der Option UNSIGNED versehen, würde die Werte genauer widerspiegeln und Du müsstest nicht irgendwann verfrüht auf BIGINT umwechseln (ok, Du bist noch weit weg davon, aber das Vorzeichen muss nun auch nicht noch ausgewertet/gespeichert werden).
 
Hallo,
QUOTE
Nun habe ich aber leicht den Eindruck, das es sich hierbei um eine Verknüpfungstabelle handelt, oder das der Schüssel id ggf. nicht benötigt wird.
Wird id wirklich benötigt? Ich empfinde zwei UNIQUE-Keys etwas seltsam wovon einer vorgeben wird und der andere noch hochgezählt.


Nein wird er nicht, denn die idnumber ist einzigartig!
Soll ich jetzt unter "Struktur" einfach das Feld "id" löschen?
Und bei den "Indizes:" den "PRIMARY" ?

Oder soll ich:

CODE ALTER TABLE `users` DROP PRIMARY KEY, ADD PRIMARY KEY(`idnumber`)

ausführen?

OPTIMIZE TABLE `users`
ausgeführt!
 
Sie müssen sich einloggen oder registrieren, um hier antworten zu können.
Zurück
Oben