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 |
03.11.2010, 23:26:38
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Für Interessierte:
Die Performance liess sich nochmal deutlich steigern, indem ich dem COUNT Statement ein LIMIT 0,30 spendierte. Ich kann es mir nicht erklären, welchen Einfluss LIMIT hier nimmt, aber der Query spuckt tatsächlich die korrekte Anzahl von 61'271 Datensätzen aus und wir sind neu bei 2,2 Sekunden.
Anbei der letzte EXPLAIN:
Code:
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
| 1 | PRIMARY | t1 | index | PRIMARY,deleted,idx_id_deleted | idx_id_deleted | 5 | NULL | 61270 | Using where; Using index |
| 1 | PRIMARY | p11 | ref | lang,idx_labels_art_lang_del | idx_labels_art_lang_del | 43 | store_dwh.t1.id,const,const | 1 | Using index |
| 1 | PRIMARY | p12 | ref | deleted,idx_art_del_sup | idx_art_del_sup | 5 | const,store_dwh.t1.id | 1 | Using where; Using index |
| 1 | PRIMARY | r20 | eq_ref | PRIMARY,deleted | PRIMARY | 4 | store_dwh.p12.supplierid | 1 | |
| 1 | PRIMARY | p13 | ref | supplierid,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.t1.id,store_dwh.r20.id | 1 | Using index |
| 1 | PRIMARY | p15 | ref | idx_art_val_del | idx_art_val_del | 5 | const,store_dwh.t1.id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | p15b | ref | validfrom,idx_art_val_del | idx_art_val_del | 5 | store_dwh.t1.deleted,store_dwh.p15.artikleid | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p13b | ref | supplierid,validfrom,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.p13.artikleid,store_dwh.p13.supplierid | 1 | Using where; Using index |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
8 rows in set (0.00 sec)
Der von Thomas vorgeschlagene Weg:
Zitat:
Hast Du schon mal
Code:
$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);
zum Ermitteln der Treffer probiert? Dann entfällt der Vorab-Query mit dem COUNT() komplett und die Blätterfunktion kann problemlos erzeugt werden.
|
hingegen fällt flach, da hier tatsächlich die durch LIMIT 0,30 eingegrenzte Suche auch nur die Anzahl 30 ausgibt. Ohne Limit wiederum dauerts so lange wie mit COUNT.
Den selben Query als SELECT DISTINCT p15.currid (für die Erzeugung eines Dropdowns mit allen gefundenen unterschiedlichen Währungen) habe ich mit allen möglichen Indizies zu beschleunigen versucht, aber das Problem letztendlich liegt daran, dass trotz neuer Indizies auf p15.currid und/oder mehrere Felder trotzdem einmal durch die ganze Tabelle gelesen werden muss. Dafür sind die Zeiten akzeptabel. Es liegt einfach an der Summe der nacheinander ablaufenden Queries, SELECT, SELECT COUNT und 2-4 Mal SELECT DISTINCT - je nach Anzahl der zu erzeugenden Dropdowns. Ich kann auch ohne Letztere leben.
EXPLAIN für DISTINCT: (neue Indizies wurden vorher wieder entfernt)
Code:
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+-------------------------------------------+
| 1 | PRIMARY | p12 | index | deleted,idx_art_del_sup | idx_art_del_sup | 9 | NULL | 61271 | Using where; Using index; Using temporary |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY,deleted,idx_id_deleted | PRIMARY | 4 | store_dwh.p12.artikleid | 1 | Using where |
| 1 | PRIMARY | p11 | ref | lang,idx_labels_art_lang_del | idx_labels_art_lang_del | 43 | store_dwh.t1.id,const,const | 1 | Using index |
| 1 | PRIMARY | r20 | eq_ref | PRIMARY,deleted | PRIMARY | 4 | store_dwh.p12.supplierid | 1 | |
| 1 | PRIMARY | p13 | ref | supplierid,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.t1.id,store_dwh.r20.id | 1 | Using index |
| 1 | PRIMARY | p15 | ref | idx_art_val_del | idx_art_val_del | 5 | const,store_dwh.t1.id | 1 | |
| 3 | DEPENDENT SUBQUERY | p15b | ref | validfrom,idx_art_val_del | idx_art_val_del | 5 | store_dwh.t1.deleted,store_dwh.p15.artikleid | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p13b | ref | supplierid,validfrom,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.p13.artikleid,store_dwh.p13.supplierid | 1 | Using where; Using index |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+-------------------------------------------+
8 rows in set (0.00 sec)
Grüsse und bis demnäx
David
|
04.11.2010, 08:26:36
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Hallo David,
Zitat:
Zitat von droehn
Für Interessierte:
Die Performance liess sich nochmal deutlich steigern, indem ich dem COUNT Statement ein LIMIT 0,30 spendierte. Ich kann es mir nicht erklären, welchen Einfluss LIMIT hier nimmt, aber der Query spuckt tatsächlich die korrekte Anzahl von 61'271 Datensätzen aus und wir sind neu bei 2,2 Sekunden.
|
Das ist interessant, vermutlich bringt der LIMIT MYSQL dazu, die Daten eher im RAM abzulegen, da LIMIT ja zumeist bedeutet, "Ich möchte nur wenig Daten".
Eigentlich sollte der COUNT(*) - Query auch mit LIMIT 1 das korrekte Ergebnis bringen, kannst Du dies bitte nochmal ausprobieren?
Der EXPLAIN liefert leider zuwenig detailierte Informationen zur MySQL-Internen Entscheidungsfindung, um den "LIMIT-Effekt" irgendwie zu erkennen.
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
04.11.2010, 21:30:10
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Zitat:
Zitat von droehn
Den selben Query als SELECT DISTINCT p15.currid
|
Mich würde mal interessieren, ob SQL_SMALL_RESULT an dieser Stelle etwas bringt, bzw. ähnlich dem obigen LIMIT Effekt reagiert.
Also
Code:
EXPLAIN SELECT SQL_SMALL_RESULT DISTINCT p15.currid ...
Code:
EXPLAIN SELECT SQL_SMALL_RESULT COUNT (t1.id) ...
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
05.11.2010, 19:33:12
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Moin Thomas,
Zitat:
Eigentlich sollte der COUNT(*) - Query auch mit LIMIT 1 das korrekte Ergebnis bringen
|
Yep, tut er auch. Ich benutze übrigens COUNT(t1.id) mit mysql_result($result,0,0).
Die Performance bleibt dabei unverändert.
Zitat:
Code:
EXPLAIN SELECT SQL_SMALL_RESULT DISTINCT p15.currid ...
|
Die Performance wird davon leider nicht besser. EXPLAIN:
Code:
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | p12 | ALL | deleted,idx_art_del_sup | NULL | NULL | NULL | 61271 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY,deleted,idx_id_deleted | PRIMARY | 4 | store_dwh.p12.artikleid | 1 | Using where |
| 1 | PRIMARY | p11 | ref | lang,idx_labels_art_lang_del | idx_labels_art_lang_del | 43 | store_dwh.t1.id,const,const | 1 | Using index; Distinct |
| 1 | PRIMARY | r20 | eq_ref | PRIMARY,deleted | PRIMARY | 4 | store_dwh.p12.supplierid | 1 | Distinct |
| 1 | PRIMARY | p13 | ref | supplierid,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.t1.id,store_dwh.r20.id | 1 | Using index; Distinct |
| 1 | PRIMARY | p15 | ref | idx_art_val_del | idx_art_val_del | 5 | const,store_dwh.t1.id | 1 | Using index; Distinct |
| 3 | DEPENDENT SUBQUERY | p15b | ref | validfrom,idx_art_val_del | idx_art_val_del | 5 | store_dwh.t1.deleted,store_dwh.p15.artikleid | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p13b | ref | supplierid,validfrom,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.p13.artikleid,store_dwh.p13.supplierid | 1 | Using where; Using index |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+----------------------------------------------+
Zitat:
Code:
EXPLAIN SELECT SQL_SMALL_RESULT COUNT (t1.id) ...
|
Hier verschlechtert sich die Performance wieder zurück auf die Werte, die ich ohne LIMIT habe.
Code:
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
| 1 | PRIMARY | p12 | index | deleted,idx_art_del_sup | idx_art_del_sup | 9 | NULL | 61271 | Using where; Using index |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY,deleted,idx_id_deleted | PRIMARY | 4 | store_dwh.p12.artikleid | 1 | Using where |
| 1 | PRIMARY | p11 | ref | lang,idx_labels_art_lang_del | idx_labels_art_lang_del | 43 | store_dwh.t1.id,const,const | 1 | Using index |
| 1 | PRIMARY | r20 | eq_ref | PRIMARY,deleted | PRIMARY | 4 | store_dwh.p12.supplierid | 1 | |
| 1 | PRIMARY | p13 | ref | supplierid,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.t1.id,store_dwh.r20.id | 1 | Using index |
| 1 | PRIMARY | p15 | ref | idx_art_val_del | idx_art_val_del | 5 | const,store_dwh.t1.id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | p15b | ref | validfrom,idx_art_val_del | idx_art_val_del | 5 | store_dwh.t1.deleted,store_dwh.p15.artikleid | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p13b | ref | supplierid,validfrom,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.p13.artikleid,store_dwh.p13.supplierid | 1 | Using where; Using index |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
Füge ich zum letzten Query wieder LIMIT hinzu, bin ich bei den bisherigen Bestwerten wie vorher, d.h. ich kann LIMIT mit SQL_SMALL_RESULT wie auch mit nur SELECT verwenden.
Grüsse
David
|
05.11.2010, 19:59:34
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Hallo David,
vielen Dank für den Test. Das würde die (gelesene) Aussage bestätigen, dass SQL_SMALL_RESULT wenig bis nichts bringt (oder ich habe den Sinn vollkommen missverstanden).
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
06.11.2010, 22:25:10
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Moin Thomas,
Ich arbeite jetzt wieder auf meinem Server, einem Win 2K3 Xeon 2GHz Quad mit 2GB RAM
(mein Laptop läuft auf Vista mittels Core 2Duo 2.26GHz und 4GB RAM).
Beide XAMPP sind der Version 1.7.0 und MySQL der Version 5.1.30. Datenbanken, Indizies und Queries sind exakt übereinstimmend. Auch der Explain gibt auf beiden Maschinen die selben Angaben aus. Trotzdem läuft der COUNT auf dem Server - mit fast doppelt soviel Bums - um eine viertel Sekunde langsamer.
Wahrscheinlich liegts am RAM, was bedeuten würde, dass Deine Theorie
Zitat:
vermutlich bringt der LIMIT MYSQL dazu, die Daten eher im RAM abzulegen
|
goldrichtig wäre.
SERVER
Code:
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 33554432|
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144|
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+---------+
12 rows in set (0.00 sec)
NOTEBOOK
Code:
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 33554432|
| 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 |
+-------------------------+----------+
Grüsse
David
|
07.11.2010, 13:25:36
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Zitat:
Zitat von droehn
Ich arbeite jetzt wieder auf meinem Server, einem Win 2K3 Xeon 2GHz Quad mit 2GB RAM
(mein Laptop läuft auf Vista mittels Core 2Duo 2.26GHz und 4GB RAM).
Beide XAMPP sind der Version 1.7.0 und MySQL der Version 5.1.30. Datenbanken, Indizies und Queries sind exakt übereinstimmend. Auch der Explain gibt auf beiden Maschinen die selben Angaben aus. Trotzdem läuft der COUNT auf dem Server - mit fast doppelt soviel Bums - um eine viertel Sekunde langsamer.
|
Der Server hat zwar ein QUAD Prozessor (vier Kerne) aber ich weiß nicht, ob MySQL bei einer Abfrage eines Benutzers ( der SELECT COUNT(*) ....) die Last auf mehrere Kerne aufteilt.
Zudem ist auf dem Server noch InnoDB aktiv (in der Config), auf dem Laptop nicht. Das kostet nochmal RAM. Der Server hat zwar vermutlich schnellere Platten als der Laptop, aber dies kann die GHz und RAM nicht ausgleichen. Folglich hat der Server in dem Fall "...auch nicht mehr Bums, als der Laptop...", könnte man behaupten.
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)
|
|
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 01:33:10 Uhr.
|