MySQL Optimierung bei 3 Millionen+ Einträgen

Dieses Thema im Forum "Webentwicklung" wurde erstellt von Chrisomator, 21. Januar 2014 .

  1. 21. Januar 2014
    Hallo Leute,

    ich habe eine PHP betriebene Website, welche ihre Daten aus einer MySQL Datenbank bezieht. Die relevanten Daten liegen alle in einer Tabelle mit ca. 3 Millionen Einträgen, welche aus vielen verschiedenen Spalten besteht.
    Diese Tabelle möchte ich nun möglichst schnell nach gewissen Einträgen durchsuchen, Suchkriterien sind hierbei entweder eines oder mehrere der folgenden Felder:

    firma -> varchar(255)
    plz -> varchar(5) (Enthält allerdings nur Zahlen)
    ort -> varchar(255)
    branchen -> varchar(255)
    branchen2 -> varchar(255)
    premium -> int(11) -> ist 0 oder 1

    Der Inhalt der Felder wird ausschließlich mit einer WHERE-Clause überpüft und es finden keine JOINS o.ä. statt.

    Außerdem ist teilweise auch das Feld "id" relevant, welches ebenfalls ein integer ist mit den Attributen "AUTO_INCREMENT" und Primary Key.

    Zusätzlich werden die Felder nicht 1 zu 1 abgeglichen, sondern die Suchbegriffe sollen am Anfang und am Ende Wildcards (%) enthalten, damit mehr Ergebnisse ausgespuckt werden.

    Mein Problem ist jetzt, dass es (verständlicherweise) ewig dauert, bis Einträge gefunden werden, teilweise sogar solange, bis das PHP Skript einen Timeout zurückgibt.

    Meine Frage lautet jetzt, ob ihr Tipps habt, wie ich die Datenbank bzw. die Suche optimieren kann, damit das ganze schneller funktioniert?

    Ich habe bereits versucht Indizes für die oben genannten Felder zu erstellen (PHPMyAdmin => Struktur => Index), allerdings bin ich mir unsicher, ob die Felder nun wirklich einen Index besitzen, da die Aktion "Index" im PHPMyAdmin bei den Feldern immernoch anklickbar ist und nicht ausgegraut ist o.ä.


    Ich danke bereits im Voraus!

    MfG,
    Chrisomator
     
  2. 21. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    FULLTEXT Optimierung + Indizes und großzügiger Cache helfen schon viel.
    Bei solchen Mengen auch mal an einen Cluster denken und die Rechenlast ein wenig verteilen.
     
  3. 21. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    Hey, danke schonmal für die Antwort!

    Kannst du vielleicht etwas genauer erläutern, wie ich meine Datenbank für FULLTEXT-Suchen optimiere? Und sind die Felder auch indiziert, wenn im PMA Index noch anklickbar ist bei den entsprechenden Feldern?

    MfG
     
  4. 21. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    am einfachsten im phpmyadmin einen index typ fulltext auf die entsprechenden varchar legen die durchsucht werden. (vermutlich nur firma,branchen,branchen2)
    bei allen anderen einen ganz normalen index erstellen. plz sind doch nur zahlen? würde ich als INT machen.

    vorher backup, dann folgendes je varchar spalte:
    ALTER TABLE tabellenname ADD FULLTEXT INDEX index_name (spaltenname);

    auf alle anderen:
    ALTER TABLE tabellenname ADD INDEX index_name (spaltenname);

    die datenbank ist relativ klein und wenig komplex, sollte also alles unter 0,1 sekunde liegen ansonsten liegen fehler vor.
     
  5. 21. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    Hallo raid,

    erstmal danke für die Hilfe!

    Als ich einen Fulltext-Index auf eine der Spalten setzen wollte, wurde mir gesagt, dass dies nur mit MyISAM als Tabellentyp funktioniert. Also habe ich die Tabelle umgewandelt und testweise einen Fulltext-Index auf die Felder firma und branche gesetzt.

    Nachdem ich das erledigt habe, habe ich eine Testquery abgesendet: SELECT id FROM entrys WHERE firma LIKE '%schule%' OR branche LIKE '%schule%'.
    Das Ergebnis: 65824 rows in set (5.97 sec).

    //EDIT: Habe die Query in PMA nochmal ausgeführt und bekomme ein Ergebnis unter 1 Sekunde. Danke für die Hilfe!

    Ist das normal oder ist das noch was zu machen? Bin etwas skeptisch, da du meintest es sollte alles unter 0,1 Sekunden liegen. (Ist das nicht ein bisschen wenig für das Durchsuchen von 3 Millionen+ Einträgen?).

    MfG
     
  6. 21. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    dir ist schon bewusst das int und 0 am anfang keine gute Kombi is?
     
  7. 23. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    Hallo Leute,

    also die Querytime ist gesunken. (Sagt zumindest PMA).
    Allerdings besteht jetzt das Problem, dass die Seite trotzdem lange lädt. Ich habe mir auf der Seite die Query-Execution Time ausgegeben und die Renderzeit von PHP und die Werte sind sehr zufriedenstellend. (Alle Werte < 1s).
    Habt ihr vielleicht Ideen, woran das liegen könnte bzw. wie man Messen kann, wielange die einzelnen Komponenten des Servers brauchen, bis die Seite beim Client angekommen ist?
    Benutze Plesk in Verbindung mit dem Plesk Standard Apache Server.

    MfG
     
  8. 23. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    kann es ggf. sein das du alle ergebnisse ausgibst?
    pageing würde da sinn machen.
    10 ergebnisse auszugeben geht schneller als 1000.

    ist nur mal nachgefragt.
     
  9. 24. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    Hey,

    berechtigte Frage, aber: Pageing ist vorhanden. Ich selektiere in einer Query nur die notwendigen Ergebnisse mit LIMIT 10 und in einer anderen selektiere ich COUNT(*), mit der selben WHERE-Clause aber ohne Limit. Sozusagen 2 Queries, einmal eine um die aktuellen 10 Einträge auszugeben und einmal eine um die Anzahl der gefundenen Einträge zu bekommen, damit ich das Pageing erstellen kann. Oder gibt es da eine effizientere Lösung?

    LG, Chris
     
  10. 24. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    probier mal das:
    PHP:
    $sql  "SELECT SQL_CAL_FOUND_ROWS * FROM users LIMIT 0, 10" ;
    $result  mysql_query ( $sql );
    $sql  "SELECT FOUND_ROWS() AS `found_rows`;" ;
    $rows  mysql_query ( $sql );
    $rows  mysql_fetch_assoc ( $rows );
    $total_rows  $rows [ 'found_rows' ];
    du mußt somit keine weiter abfrage für count machen.

    um mal zu testen mach dir mal eine blanko seite und ohne dein ganzes php zeugs dazu.
    also nur ein php script für die abfrage und echo für die ausgabe.
    wenn sich an der geschwindigkeit nichts tut, dann muß weiter obtimiert werden.
    sonst könnte an einer anderen stelle deine performance leiden.
     
  11. 24. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    mit LIKE '%keyword%' wird nicht unbedingt der fulltext optimal genutzt, zudem liegt die erst anfrage im query-cache so das eine weitere anfrage sehr schnell ist, deshalb auch erst 5sek und dann 0,01sek das löst aber nicht das problem sondern entlastet die datenbank nur bei den gleichen anfragen.

    probier mal: SELECT id FROM entrys WHERE MATCH(firma,branche) AGAINST('schule')
    oder wenn das ergebniss nicht stimmt: SELECT id FROM entrys WHERE MATCH(firma) AGAINST('schule') OR MATCH(branche) AGAINST('schule')

    /etc/mysql/my.cnf optimieren damit der index im ram liegt.

    die config geht nur wenn ihr mindestens noch 1GB RAM frei habt.

    key_buffer = 1G
    join_buffer_size = 4M
    sort_buffer_size = 16M
    read_buffer_size = 6M
    read_rnd_buffer_size = 6M
    myisam_sort_buffer_size = 16M
    max_heap_table_size = 1G
    tmp_table_size = 1G
     
  12. 24. Januar 2014
    AW: MySQL Optimierung bei 3 Millionen+ Einträgen

    Hallo,

    danke für die hilfreichen Tipps. @onip: Die zweite Query konnte ich dank dir nun komplett weglassen! Danke hierfür.
    @raid: Ich habe die Config mal so übernommen und benutze nun MATCH AGAINST anstatt WHERE. Das ganze funktioniert nun schon deutlich besser als vorher.

    Falls ihr noch weitere Tipps habt gerne her damit.

    LG, Chris
     
  13. Video Script

    Videos zum Themenbereich

    * gefundene Videos auf YouTube, anhand der Überschrift.