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
  #11  
Alt 11.03.2011, 20:13:25
Myribo Myribo ist offline
Anfänger
 
Registriert seit: Nov 2005
Beiträge: 19
AW: Bräuchte Hilfe mit... Indizes (?)

Alles klar...! ;-)

SHOW CREATE... :
Code:
mysql> SHOW CREATE TABLE ways;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ways  | CREATE TABLE `ways` (
  `id` int(11) NOT NULL,
  `maxlat` double NOT NULL,
  `maxlon` double NOT NULL,
  `minlat` double NOT NULL,
  `minlon` double NOT NULL,
  `quicknodes` longtext NOT NULL,
  `quickamenity` varchar(11) NOT NULL,
  `quickleisure` varchar(11) NOT NULL,
  `quickhighway` varchar(11) NOT NULL,
  `quickbuilding` varchar(11) NOT NULL,
  `quicklanduse` varchar(11) NOT NULL,
  `quickname` varchar(11) NOT NULL,
  `quicklevel` int(11) NOT NULL,
  `optimizernodes` int(11) NOT NULL,
  `optimized` int(11) DEFAULT NULL,
  `visible` tinyint(4) DEFAULT NULL,
  `user` varchar(50) DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  KEY `lat` (`minlat`,`maxlat`),
  KEY `lon` (`minlon`,`maxlon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
EXPLAIN SLELECT... :
Code:
mysql> EXPLAIN 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;
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | ways  | ALL  | lat,lon       | NULL | NULL    | NULL | 229509 | Using where; Using filesort | 
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.03 sec)
Die Buffer-Abfrage:
Code:
mysql> SHOW GLOBAL VARIABLES LIKE '%buffer%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608  | 
| innodb_buffer_pool_size | 16777216 | 
| innodb_log_buffer_size  | 8388608  | 
| join_buffer_size        | 131072   | 
| key_buffer_size         | 16777216 | 
| myisam_sort_buffer_size | 8388608  | 
| net_buffer_length       | 8192     | 
| preload_buffer_size     | 32768    | 
| read_buffer_size        | 262144   | 
| read_rnd_buffer_size    | 524288   | 
| sort_buffer_size        | 524288   | 
| sql_buffer_result       | OFF      | 
+-------------------------+----------+
12 rows in set (0.00 sec)
Das ganze läuft lokal und mein Rechner hat 4Gb RAM zur Verfügung.

Hoffe, die Infos sind okay.

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

Perfekt, so läßt es sich gut analysieren.

Was gleich auffällt ist, dass der EXPLAIN keinen der beiden Index verwendet, siehe Spalte "KEY". Eventuell ist die Kardinalität der Spalten schlecht.

Bitte noch folgendes ausprobieren und das Ergebnis hier zeigen:
Code:
mysql>SHOW INDEX FROM ways;
Musst Du unbedingt alle Spalten lesen (also unbedingt SELECT * FROM ways ... ) oder kannst Du Dich auf bestimmte Spalten beschränken?

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

SHOW INDEX... :
Code:
mysql> SHOW INDEX FROM ways;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ways  |          1 | lat      |            1 | minlat      | A         |      259259 |     NULL | NULL   |      | BTREE      |         | 
| ways  |          1 | lat      |            2 | maxlat      | A         |      259259 |     NULL | NULL   |      | BTREE      |         | 
| ways  |          1 | lon      |            1 | minlon      | A         |      259259 |     NULL | NULL   |      | BTREE      |         | 
| ways  |          1 | lon      |            2 | maxlon      | A         |      259259 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.25 sec)


Zitat:
Zitat von thomas_w
Musst Du unbedingt alle Spalten lesen (also unbedingt SELECT * FROM ways ... ) oder kannst Du Dich auf bestimmte Spalten beschränken?
Nein - da hast Du Recht.
Habs reduziert, jedoch ohne erkennbaren Geschwindigkeitsunterschied.

Code:
SELECT 
quicknodes, quickamenity, quickleisure, quickhighway, quickbuilding, quicklanduse, quickname, quicklevel

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
Schönen Gruß!
Mit Zitat antworten
  #14  
Alt 11.03.2011, 21:00:10
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

Okay, also hier mal der erste Tipp.

diese zwei neue Index erzeugen:

Code:
CREATE INDEX idx_min_max on ways (maxlat, maxlon, minlat, minlon, id );

CREATE INDEX idx_quickl on ways (quicklevel);
die bisherigen beiden Index 'lat' und 'lon' entfernen.

Nun mit diesem SQL einen neuen EXPLAIN erstellen.

Code:
SELECT quicknodes, quickamenity, quickleisure, quickhighway, quickbuilding, quicklanduse, quickname, quicklevel
  FROM ways w1
  JOIN ( SELECT id FROM ways  
          WHERE maxlon > 7.61655923529412 
            AND minlon < 7.63538276470588 
            AND maxlat > 51.9546756470588 
            AND minlat < 51.9664403529412 ) w2
    ON w1.id = w2.id

WHERE w1.quickamenity = 'parking' 
   OR w1.quickhighway <> '' 
   OR w1.quicklanduse = 'forest'
   OR w1.quickbuilding = 'yes'

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

So - Index sind geändert.
Geschwindigkeit hat sich nicht geändert.

Abfrage liefert:
Code:
mysql> EXPLAIN SELECT quicknodes, quickamenity, quickleisure, quickhighway, quickbuilding, quicklanduse, quickname, quicklevel
    ->   FROM ways w1
    ->   JOIN ( SELECT id FROM ways  
    ->           WHERE maxlon > 7.61655923529412 
    ->             AND minlon < 7.63538276470588 
    ->             AND maxlat > 51.9546756470588 
    ->             AND minlat < 51.9664403529412 ) w2
    ->     ON w1.id = w2.id
    -> 
    -> WHERE w1.quickamenity = 'parking' 
    ->    OR w1.quickhighway <> '' 
    ->    OR w1.quicklanduse = 'forest'
    ->    OR w1.quickbuilding = 'yes'
    -> 
    -> ORDER BY w1.quicklevel ASC;
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL        | NULL    | NULL |    755 | Using temporary; Using filesort | 
|  1 | PRIMARY     | w1         | ALL   | NULL          | NULL        | NULL    | NULL | 223134 | Using where; Using join buffer  | 
|  2 | DERIVED     | ways       | range | idx_min_max   | idx_min_max | 8       | NULL | 111567 | Using where; Using index        | 
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
3 rows in set (1.05 sec)
Schönen Gruß!
Mit Zitat antworten
  #16  
Alt 11.03.2011, 21:23:01
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

okay, zweiter Schritt.

Bitte mal diesen SQL ausführen und das Ergebnis zeigen

Code:
SELECT 
  COUNT(*),
  COUNT(DISTINCT quickamenity), 
  COUNT(DISTINCT quickhighway), 
  COUNT(DISTINCT quicklanduse), 
  COUNT(DISTINCT quickbuilding)
  
 FROM ways;
und dritter Schritt (diesen Parameter von 16 MB auf 64 MB) in der my.ini erhöhen und anschließend die MySQL neu starten.

Code:
innodb_buffer_pool_size =  65536000
Jetzt bitte noch mal den EXPLAIN mit meinem SQL von vorhin ausführen.

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

1)
Code:
mysql> SELECT 
    ->   COUNT(*),
    ->   COUNT(DISTINCT quickamenity), 
    ->   COUNT(DISTINCT quickhighway), 
    ->   COUNT(DISTINCT quicklanduse), 
    ->   COUNT(DISTINCT quickbuilding)
    ->   
    ->  FROM ways;
+----------+------------------------------+------------------------------+------------------------------+-------------------------------+
| COUNT(*) | COUNT(DISTINCT quickamenity) | COUNT(DISTINCT quickhighway) | COUNT(DISTINCT quicklanduse) | COUNT(DISTINCT quickbuilding) |
+----------+------------------------------+------------------------------+------------------------------+-------------------------------+
|   223834 |                           65 |                           37 |                           44 |                            25 | 
+----------+------------------------------+------------------------------+------------------------------+-------------------------------+
1 row in set (1.31 sec)
2) -> geändert!

3)
Code:
mysql> EXPLAIN SELECT quicknodes, quickamenity, quickleisure, quickhighway, quickbuilding, quicklanduse, quickname, quicklevel
    ->   FROM ways w1
    ->   JOIN ( SELECT id FROM ways  
    ->           WHERE maxlon > 7.61655923529412 
    ->             AND minlon < 7.63538276470588 
    ->             AND maxlat > 51.9546756470588 
    ->             AND minlat < 51.9664403529412 ) w2
    ->     ON w1.id = w2.id
    -> 
    -> WHERE w1.quickamenity = 'parking' 
    ->    OR w1.quickhighway <> '' 
    ->    OR w1.quicklanduse = 'forest'
    ->    OR w1.quickbuilding = 'yes'
    -> 
    -> ORDER BY w1.quicklevel ASC;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: osm_muenster

+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL        | NULL    | NULL |    755 | Using temporary; Using filesort | 
|  1 | PRIMARY     | w1         | ALL   | NULL          | NULL        | NULL    | NULL | 250759 | Using where; Using join buffer  | 
|  2 | DERIVED     | ways       | range | idx_min_max   | idx_min_max | 8       | NULL | 125379 | Using where; Using index        | 
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+---------------------------------+
3 rows in set (1.66 sec)
Schönen Gruß!

// Bin für heute Abend erstmal weg, kann also morgen früh erst wieder antworten. Natürlich aber schon vielen Dank bis hierhin!
Mit Zitat antworten
  #18  
Alt 11.03.2011, 21:58:10
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

hm, ich sehe gerade, die Tabelle ways hat gar keinen PRIMARY KEY

Code:
ALTER TABLE ways 
  ADD PRIMARY KEY (id);
So, nun noch mal den EXPLAIN mit meinem SQL ausführen.

Okay, dann bis morgen.

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

Guten Morgen,

der PRIMARY KEY ist gesetzt.

EXPLAIN SELECT:
Code:
mysql> EXPLAIN SELECT quicknodes, quickamenity, quickleisure, quickhighway, quickbuilding, quicklanduse, quickname, quicklevel
    ->   FROM ways w1
    ->   JOIN ( SELECT id FROM ways  
    ->           WHERE maxlon > 7.61655923529412 
    ->             AND minlon < 7.63538276470588 
    ->             AND maxlat > 51.9546756470588 
    ->             AND minlat < 51.9664403529412 ) w2
    ->     ON w1.id = w2.id
    -> 
    -> WHERE w1.quickamenity = 'parking' 
    ->    OR w1.quickhighway <> '' 
    ->    OR w1.quicklanduse = 'forest'
    ->    OR w1.quickbuilding = 'yes'
    -> 
    -> ORDER BY w1.quicklevel ASC;
+----+-------------+------------+--------+---------------+-------------+---------+-------+--------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key         | key_len | ref   | rows   | Extra                           |
+----+-------------+------------+--------+---------------+-------------+---------+-------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL        | NULL    | NULL  |    755 | Using temporary; Using filesort | 
|  1 | PRIMARY     | w1         | eq_ref | PRIMARY       | PRIMARY     | 4       | w2.id |      1 | Using where                     | 
|  2 | DERIVED     | ways       | range  | idx_min_max   | idx_min_max | 8       | NULL  | 106144 | Using where; Using index        | 
+----+-------------+------------+--------+---------------+-------------+---------+-------+--------+---------------------------------+
3 rows in set (0.07 sec)
Schönen Gruß!
Mit Zitat antworten
  #20  
Alt 12.03.2011, 18:53:33
thomas_w thomas_w ist offline
Junior Member
 
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: Bräuchte Hilfe mit... Indizes (?)

So, trotz des schönen Wetters hier und der schlechten Nachrichten aus Japan, gehts nun weiter.

Die Abfrage sollte jetzt schon deutlich schneller sein. Laut EXPLAIN werden viel weniger Rows für das Ergebnis verarbeitet. Abschließend noch ein Test. Ich hoffe er beschleunigt das Ganze noch ein wenig, aber eventuell auch nicht. Also zusätzlich noch diesen Index anlegen und den EXPLAIN noch mal ausführen.

Code:
CREATE INDEX idx_quick on ways (quickamenity, quickhighway, quicklanduse, quickbuilding);
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
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 08:06:27 Uhr.


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


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