#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 + Multi-Zitat Zitieren
#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. + Multi-Zitat Zitieren
#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 + Multi-Zitat Zitieren
#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. + Multi-Zitat Zitieren
#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 + Multi-Zitat Zitieren
#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? + Multi-Zitat Zitieren
#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 + Multi-Zitat Zitieren
#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. + Multi-Zitat Zitieren
#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 + Multi-Zitat Zitieren
#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. + Multi-Zitat Zitieren
#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 + Multi-Zitat Zitieren
#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 + Multi-Zitat Zitieren