SELFPHP: Version 5.8.2 Befehlsreferenz - Tutorial – Kochbuch – Forum für PHP Einsteiger und professionelle Entwickler

SELFPHP


Professional CronJob-Service

Suche



CronJob-Service    
bei SELFPHP mit ...



 + minütlichen Aufrufen
 + eigenem Crontab Eintrag
 + unbegrenzten CronJobs
 + Statistiken
 + Beispielaufrufen
 + Control-Bereich

Führen Sie mit den CronJobs von SELFPHP zeitgesteuert Programme auf Ihrem Server aus. Weitere Infos



:: Buchempfehlung ::

Das Zend Framework

Das Zend Framework zur Buchempfehlung
 

:: Anbieterverzeichnis ::

Globale Branchen

Informieren Sie sich über ausgewählte Unternehmen im Anbieterverzeichnis von SELFPHP  

 

:: Newsletter ::

Abonnieren Sie hier den kostenlosen SELFPHP Newsletter!

Vorname: 
Name:
E-Mail:
 
 

Zurück   PHP Forum > SELFPHP > MySQLi/PDO/(MySQL)

MySQLi/PDO/(MySQL) Anfänger, Fortgeschrittene oder Experten können hier Fragen und Probleme rund um MySQLi/PDO/(MySQL) diskutieren

Antwort
 
Themen-Optionen Ansicht
  #1  
Alt 10.03.2011, 20:08:49
Myribo Myribo ist offline
Anfänger
 
Registriert seit: Nov 2005
Beiträge: 19
Bräuchte Hilfe mit... Indizes (?)

Hallo!

Ich bin relativ neu im Bereich MySQL und würde mich freuen, wenn mir jemand helfen könnte.
Im Moment arbeite ich an einem Navigationsprogramm. Dazu benutze ich die Daten der OpenStreetMap (openstreetmap.org) und eine MySQL Datenbank mit einem etwas älteren Schema als dieses hier (im Grunde aber nicht viel anders):

http://wiki.openstreetmap.org/wiki/File:Osm_model.png

Die Tabelle 'ways' habe ich ein wenig modifiziert. Sie sieht jetzt so aus:

http://img688.imageshack.us/img688/1646/sql1a.jpg

Die Felder maxlat,minlat,maxlon,minlon werden benutzt, um jene Wege zu suchen, die im aktuellen Kartenausschnitt liegen. Mit den quick*-Feldern können Wege ebenfalls gefiltert werden. Mein Renderprogramm ruft einen Query auf, der z.B. so aussieht:

Code:
SELECT * FROM ways WHERE ((quickamenity='parking') OR (quickhighway<>'') OR (quicklanduse='forest') OR (quickbuilding='yes')) AND maxlon>7.61655923529412 AND minlon<7.63538276470588 AND maxlat>51.9546756470588 AND minlat<51.9664403529412 ORDER BY quicklevel ASC
Alles funktioniert so weit und sieht gut aus. Wenn ich jetzt beispielsweise komplett Münster schreibe, hat die Tabelle 'ways' schon an die 230.000 Einträge und die Abfragen dauern schon sehr lange. Das ist natürlich viel zu viel für eine Live-Map. Also habe ich mal nach einer schnelleren Lösung gesucht und einen Ansatz mit Indizes aufgestellt:

http://img197.imageshack.us/img197/9894/sql2h.jpg

Jedoch benütigt der oben genannte Query immernoch an die 2,5 Sekunden. Ich habe leider keine Ahnung, ob das so schlau ist, bzw. ob und wie man Indizes am besten benutzt.

Ich würde mich freuen, wenn mir jemand helfen könnte, meine Tabellenstruktur, Indizeslösung oder Abfrage zu verbessern.

Schönen Gruß,
Myribo
Mit Zitat antworten
  #2  
Alt 10.03.2011, 21:14:19
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

Mit Hilfe von "EXPLAIN SELECT ... " läßt sich ermitteln, wie MySQL auf die Daten zugreifen kann.

Beispiel EXPLAIN

Code:
mysql> EXPLAIN SELECT * FROM person;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | person | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql>
Weiterhin benötige ich ein paar Info's von der Tabelle

Code:
mysql>SHOW CREATE TABLE ways;
sowie ein paar MySQL Einstellungen:

Code:
mysql> SHOW GLOBAL VARIABLES LIKE '%buffer%';
und bitte alles schön mit Hilfe der [ code ] Tags formatieren, sonst läßt es sich schwer lesen.

EDIT ... so noch den bisherigen Query etwas formatiert

Code:
SELECT *
  FROM ways
WHERE ((quickamenity = 'parking') OR 
       (quickhighway <> '') OR 
       (quicklanduse = 'forest') OR 
       (quickbuilding = 'yes')
      ) 
  AND maxlon > 7.61655923529412 
  AND minlon < 7.63538276470588 
  AND maxlat > 51.9546756470588 
  AND minlat < 51.9664403529412
ORDER BY quicklevel ASC
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

Geändert von thomas_w (10.03.2011 um 21:27:44 Uhr) Grund: EDIT
Mit Zitat antworten
  #3  
Alt 10.03.2011, 21:57:17
Myribo Myribo ist offline
Anfänger
 
Registriert seit: Nov 2005
Beiträge: 19
AW: Bräuchte Hilfe mit... Indizes (?)

Hallo Thomas,

vielen Dank schonmal für die fixe Antwort :-)

Die Infos sollst du natürlich haben... (ich hoffe, dass das die von dir gemeinten sind ;-) )

EXPLAIN SELECT... bei der gegebenen Abfrage:

http://img151.imageshack.us/img151/8186/59733349.jpg


Code:
SHOW CREATE TABLE ways
liefert:

http://img827.imageshack.us/img827/7558/19245649.jpg


Code:
SHOW GLOBAL VARIABLES LIKE '%buffer%'
liefert:

http://img718.imageshack.us/img718/4581/35093551.jpg

---

// EDIT: Falls das wichtig ist: MySQL-Client-Version: 5.0.51a, Standardeinstellungen weitesgehend gelassen

Mit Sicherheit habe ich schon gegen zig Konventionen verstoßen, aber bitte hab in diesen Fällen das Nachsehen mit mir...

Schönen Gruß!
Mit Zitat antworten
  #4  
Alt 10.03.2011, 22:13:13
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

Okay, da gäbe es viel zu tun. Aber kannst Du (anstatt der Bilder) die Ausgabe hier nicht als Text reinstellen (am Besten in der MySQL Kommandozeile ausführen ). Dann könnte man direkt darauf antworten ohne alles nochmal abzuschreiben bzw. hin- und her zu blättern.

1) Frage : Müssen die "quick" Felder wirklich vom Typ TEXT sein. Das dürfte schon mal Platzverschwendung sein. Vermutlich reicht VARCHAR.

2) Du redest von zwei Indices, http://img197.imageshack.us/img197/9894/sql2h.jpg
die Du reingebaut hast, in dem "CREATE TABLE ..." sind die nicht drin.

Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
Mit Zitat antworten
  #5  
Alt 10.03.2011, 22:39:12
Myribo Myribo ist offline
Anfänger
 
Registriert seit: Nov 2005
Beiträge: 19
AW: Bräuchte Hilfe mit... Indizes (?)

Zitat:
Zitat von thomas_w
Aber kannst Du (anstatt der Bilder) die Ausgabe hier nicht als Text reinstellen (am Besten in der MySQL Kommandozeile ausführen ).
Werd ich machen...!

Zitat:
Zitat von thomas_w
1) Frage : Müssen die "quick" Felder wirklich vom Typ TEXT sein. Das dürfte schon mal Platzverschwendung sein. Vermutlich reicht VARCHAR.
Sind auf VARCHAR geändert. Hab die Länge mal ganz intuitiv auf 11 gesetz, ist das Okay?

Zitat:
Zitat von thomas_w
2) Du redest von zwei Indices, http://img197.imageshack.us/img197/9894/sql2h.jpg
die Du reingebaut hast, in dem "CREATE TABLE ..." sind die nicht drin.
Das stimmt. Das SQL Schema hab ich in der Import-Datei noch nicht drin. Die Indices habe ist später hinzugefügt, als die Tabelle schon voll war (hoffe, das war kein Fehler). Vorher lasse ich die ganze Tabelle nämlich noch durch ein anderes Programm laufen, welches z.B. die quick-, max- und min - Felder befüllt. Mit den Indices lat und lon ist dies extrem langsam. Deswegen setze ich vor dieser Optimierung die id als Index, weil ich auf die Zeilen über

Code:
... WHERE id=xxx
zugreife.

Ist der Ansatz mit den beiden 2-zeiligen Indices denn schonmal richtig?
Mit Zitat antworten
  #6  
Alt 11.03.2011, 08:24:39
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

Zitat:
Zitat von Myribo Beitrag anzeigen
Sind auf VARCHAR geändert. Hab die Länge mal ganz intuitiv auf 11 gesetz, ist das Okay?

Das stimmt. Das SQL Schema hab ich in der Import-Datei noch nicht drin. Die Indices habe ist später hinzugefügt, als die Tabelle schon voll war (hoffe, das war kein Fehler). Vorher lasse ich die ganze Tabelle nämlich noch durch ein anderes Programm laufen, welches z.B. die quick-, max- und min - Felder befüllt. Mit den Indices lat und lon ist dies extrem langsam. Deswegen setze ich vor dieser Optimierung die id als Index, weil ich auf die Zeilen über

Code:
... WHERE id=xxx
zugreife.

Ist der Ansatz mit den beiden 2-zeiligen Indices denn schonmal richtig?
Ob die VARCHAR Länge mit 11 richtig ist, kann Du nur selber herausfinden. Wenn Du mehr als 11 Zeichen einfügen willst, gibt es einen Fehler.

Mit

Code:
SELECT MAX(LENGTH(varcharspalte)) FROM tabelle
läßt sich die bisherige maximale Belegung einer Tabellenspalte ermitteln.

Das Du die Indices erst nach dem Laden der Daten erzeugst ist richtig und die schnellste Variante. Prinzipiell ist die Idee mit den combined Index richtig.

CROSSPOSTING
http://forums.mysql.com/read.php?70,...478#msg-411478



Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

Geändert von thomas_w (11.03.2011 um 08:32:26 Uhr) Grund: CROSSPOSTING
Mit Zitat antworten
  #7  
Alt 11.03.2011, 09:46:21
Myribo Myribo ist offline
Anfänger
 
Registriert seit: Nov 2005
Beiträge: 19
AW: Bräuchte Hilfe mit... Indizes (?)

Vielen Dank für die Antwort!

War mir mit der VARCHAR Länge nicht sicher, da ich im Internet nachgelesen habe, VARCHAR sei ebenfalls von beliebiger Länge.

-> http://dev.mysql.com/doc/refman/5.0/en/char.html

Muss ich mal schauen, welche Länge da passen würde.

Auch schön zu hören, dass das mit den Indices nicht allzu falsch war.

Ich habe die Nacht über mal komplett Nordrhein-Westfalen in die Datenbank geschrieben und der SELECT * FROM ways...-Query benötigt jetzt schon 45-60 Sekunden.

Das muss doch irgendwie schneller hinzubekommen sein...

Kannst Du mir vielleicht noch Hinweise geben, wie ich die Datenbank, Tabelle oder den Query schneller, bzw. effizienter machen kann?
Ich bin Dir mit Dank verbunden!

Schönen Gruß!
Mit Zitat antworten
  #8  
Alt 11.03.2011, 10:39:55
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

Zitat:
Zitat von Myribo Beitrag anzeigen
Kannst Du mir vielleicht noch Hinweise geben, wie ich die Datenbank, Tabelle oder den Query schneller, bzw. effizienter machen kann?
hab ich schon, siehe #2 und #4 , allerdings hast Du die wesentlichen Fragen nicht alle beantwortet
http://www.selfphp.de/forum/showpost...82&postcount=2

Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
Mit Zitat antworten
  #9  
Alt 11.03.2011, 16:20:16
Myribo Myribo ist offline
Anfänger
 
Registriert seit: Nov 2005
Beiträge: 19
AW: Bräuchte Hilfe mit... Indizes (?)

Hi,

Du sprachst in #2 davon, dass man viele Dinge optimieren könne. Für mich war das bisher nur das Umstellen von TEXT auf VARCHAR bei den quick* Feldern. Ferner hast du nach weiteren Informationen von der Tabelle gefragt. Die hatte ich doch soweit rausgesucht, oder was meinst Du mit:
Zitat:
allerdings hast Du die wesentlichen Fragen nicht alle beantwortet
Welche Fragen und Optimierungsvorschläge meinst Du denn?

Versteh mich bitte nicht falsch, ich freue mich natürlich, dass Du mir hilfst!

Schönen Gruß!
Mit Zitat antworten
  #10  
Alt 11.03.2011, 19:30:25
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

Zitat:
Zitat von thomas_w aus #4:

2) Du redest von zwei Indices, http://img197.imageshack.us/img197/9894/sql2h.jpg
die Du reingebaut hast, in dem "CREATE TABLE ..." sind die nicht drin.
In #4 wollte ich einen neuen EXPLAIN auf die Tabelle, die die beiden zusätzlichen Indices hat. Das Ergebnis bitte dann nicht als Bild, sondern als Text. Dann zeigt sich, ob andere Indices etwas bringen oder nicht.

Weiterhin bitte nochmal die MySQL Parameter:
Code:
mysql> SHOW GLOBAL VARIABLES LIKE '%buffer%';
Ich glaube, die InnoDB Buffer kann größer werden. Wieviel RAM hat der Server insgesamt zur Verfügung?


Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

Geändert von thomas_w (11.03.2011 um 19:43:49 Uhr) Grund: Schreibfehler korrigiert
Mit Zitat antworten
Antwort


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge hochzuladen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind aus.
[IMG] Code ist aus.
HTML-Code ist aus.

Gehe zu

Ähnliche Themen
Thema Autor Forum Antworten Letzter Beitrag
Hilfe, Hilfe, Hilfe adoganay Support 1 08.02.2010 09:05:08
Bräuchte mal Hilfe Psyder PHP Grundlagen 1 30.09.2009 17:58:36
Query/DB-Optimierung Phaltôn MySQLi/PDO/(MySQL) 14 21.12.2008 00:56:36
bräuchte mal kurz hilfe Fabian85 MySQLi/PDO/(MySQL) 15 27.03.2005 09:52:30
Help!!! Hilfe!!! Presster PHP Grundlagen 1 16.12.2004 21:28:57


Alle Zeitangaben in WEZ +2. Es ist jetzt 16:13:13 Uhr.


Powered by vBulletin® Version 3.8.3 (Deutsch)
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.


© 2001-2024 E-Mail SELFPHP OHG, info@selfphp.deImpressumKontakt