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
|
:: Anbieterverzeichnis ::
Globale Branchen
Informieren Sie sich über ausgewählte Unternehmen im Anbieterverzeichnis von SELFPHP
:: Newsletter ::
Abonnieren Sie hier den kostenlosen
SELFPHP Newsletter!
|
MySQLi/PDO/(MySQL) Anfänger, Fortgeschrittene oder Experten können hier Fragen und Probleme rund um MySQLi/PDO/(MySQL) diskutieren |
11.03.2011, 19:13:25
|
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ß!
|
11.03.2011, 19:34:44
|
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.
|
11.03.2011, 19:52:56
|
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ß!
|
11.03.2011, 20:00:10
|
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.
|
11.03.2011, 20:13:08
|
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ß!
|
11.03.2011, 20:23:01
|
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.
|
11.03.2011, 20:46:39
|
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!
|
11.03.2011, 20:58:10
|
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.
|
12.03.2011, 09:59:51
|
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ß!
|
12.03.2011, 17:53:33
|
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.
|
Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
|
|
Themen-Optionen |
|
Ansicht |
Linear-Darstellung
|
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.
HTML-Code ist aus.
|
|
|
Alle Zeitangaben in WEZ +2. Es ist jetzt 11:36:31 Uhr.
|