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

SELFPHP
Professional CronJob-Service
Startseite
Aktuelles
SELFPHP
shopware Partner
SELFPHP Dokumentation
Premium-Partner
Anbieterverzeichnis
CronJob-Service
Services
Produktempfehlung
Internet Security

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 ::

Webseiten professionell erstellen

Webseiten professionell erstellen 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)
Seite neu laden COUNT mit JOINs und sub-queries kriechend langsam
Hilfe Benutzerliste Kalender Suchen Heutige Beiträge Alle Foren als gelesen markieren

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

Antwort
Seite 1 von 3 1 23
 
Themen-Optionen Ansicht
  #1  
Alt 27.10.2010, 20:47:23
droehn droehn ist offline
Anfänger
  
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
COUNT mit JOINs und sub-queries kriechend langsam
Moin zusammen,

ich habe mit 6 Tabellen eine Artikeldatenbank unter MYSQL / MyISAM zusammengebastelt.
Darin sind gegenwärtig je 60'000 Positionen gespeichert - ausser bei der Tabelle 'labels', die für jeden Artikel eine Beschreibung in drei Sprachen enthält, also rund 180'000 Einträge. Die Tabellen haben zusammen ein Gewicht von ca. 30MB.

Die grundsätzlich erzeugte Liste (d.h. ohne Filtereinstellungen) zeigt jeweils 30 Sätze pro Seite an, und um weitere Seiten anwählen zu können, benötige ich einen COUNT, um die Anzahl der Folgeseiten auszurechnen, bzw. die Anzahl der gefundenen Datensätze anzuzeigen.

Der COUNT sieht folgendermassen aus:

Code:
SELECT COUNT
	(t1.id) 
FROM 
	articles AS t1 
LEFT JOIN
	labels AS p11
ON
	(
		p11.artikleid = t1.id
	AND
		p11.lang = 'deutsch'
	AND
		p11.deleted != '1'
	)
JOIN
	suppliers AS p12
ON
	(
		p12.artikleid = t1.id
	AND
		p12.deleted != '1'
	)
LEFT JOIN	
	suppliernames as r20
ON
	(
		r20.id = p12.supplierid
	AND
		r20.deleted != '1'
	)
LEFT JOIN
	purchprices AS p13
ON
	(
		p13.artikleid = t1.id
	AND
		p13.supplierid = r20.id
	AND
		p13.deleted != '1'
	AND
		p13.validfrom = (
				SELECT 
					MAX(p13b.validfrom)
				FROM
					purchprices AS p13b 
				WHERE
					p13b.artikleid = p13.artikleid
				AND 
					p13b.supplierid = p13.supplierid
				AND 
					p13b.deleted != '1' 
				AND 
					p13b.validfrom < '1285521549'
		)
	)
LEFT JOIN
	salesprices AS p15
ON
	(
		p15.artikleid = t1.id
	AND
		p15.deleted != '1'
	AND
		p15.validfrom = (
				SELECT 
					MAX(p15b.validfrom)
				FROM
					salesprices AS p15b 
				WHERE
					p15b.artikleid = p15.artikleid
				AND 
					p15b.deleted != '1' 
				AND 
					p15b.validfrom < '1288203628'
		)
	)
WHERE
	t1.deleted != '1'
Die Auswertung in PHP geschieht über
Code:
mysql_result($num, 0, 0)
Die JOINS backen naturgemäss die unterschiedlichen Teilinformationen zu einem kompletten Artikelstammsatz zusammen und die Sub-Queries filtern aus, welches die gültigen Preise gemäss dem heutigen Datum sind.

Diese Abfrage dauert auf einem Dual Core 1,6 MHz MS Vista Laptop rund 10 Sekunden.
Durch entfernen der Subqueries werden daraus 5 Sekunden - aber leider brauche ich diese Routinen ja...

Dieselbe Abfrage als SELECT und LIMIT 0,30 dauert 0,0675 Sekunden.

Natürlich habe ich alle relevanten Spalten ordnungsgemäss indiziert (glaub' ich wenigstens). COUNT(*) und mysql_num_rows habe ich mit leicht schlechteren Ergebnissen ausprobiert.

Ich halte die paar Einträge eigentlich nicht für so viel, wenn man berücksichtigt, dass MySQL mit Millionen von Einträgen prima umgehen kann. Aber vielleicht zwinge ich MySQL mit der o.g. Abfrage tatsächlich in die Knie? Entsprechen die von mir gemachten Angaben tatsächlich der Realität oder gibt es gegenteilige Meinungen, bzw. Vorschläge, wie ich es besser machen kann? Im letzteren Fall würde ich gerne die Tabellenstrukturen, Indizies, EXPLAIN-Auswertungen etc. posten.

Vielen Dank für Euer Feedback und bis denne
David
Mit Zitat antworten
  #2  
Alt 27.10.2010, 21:01:48
thomas_w thomas_w ist offline
Junior Member
  
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: COUNT mit JOINs und sub-queries kriechend langsam
Um bei Performancefragen richtig helfen zu können, bedarf es folgender Informationen (für alle beteiligten Tabellen):

* SHOW CREATE TABLE tbl; -- liefert engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'; -- liefert die Tabellengrößen
* EXPLAIN SELECT ...; -- EXPLAIN Ausgabe für den Zugriffpfad
* SHOW VARIABLES LIKE '%buffer%'; -- wichtige MySQL Server Einstellungen

Beispiel EXPLAIN..

Code:
mysql> EXPLAIN SELECT * FROM kunde;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | kunde | system | NULL          | NULL | NULL    | NULL | 0    | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.19 sec)

mysql>
Das Wichtigste wäre mal der EXPLAIN. Bitte sauber formatieren, sonst kann man ihn nicht richtig lesen.

Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
Mit Zitat antworten
  #3  
Alt 27.10.2010, 22:53:39
droehn droehn ist offline
Anfänger
  
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
AW: COUNT mit JOINs und sub-queries kriechend langsam
Moin Thomas,

Danke fürs Antworten.

anbei die letzteren drei; falls die Tabellenstrukturen notwendig werden, schiebe ich sie nach, denn die brauchen ein bisschen mehr Platz ;-)

Code:
SHOW TABLE STATUS LIKE 'tbl'
+---------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| Name          | Engine | Version | Row_format | Rows   | Avg_row_length |Data_length  | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       |Checksum  | Create_options     | Comment |
+---------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| articles      | MyISAM |      10 | Dynamic    | 61270  |             56 |     3447360 | 281474976710655 |      1503232 |         0 |          61370 | 2010-09-27 23:37:30 | 2010-10-22 22:50:13 | 2010-09-29 20:13:52 | utf8_general_ci |     NULL |                    |         |
+---------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| labels        | MyISAM |      10 | Dynamic    | 132169 |             64 |     8570880 | 281474976710655 |      4090880 |         0 |         132173 | 2010-09-29 21:44:42 | 2010-10-22 22:50:15 | 2010-09-29 21:44:43 | utf8_general_ci |     NULL |                    |         |
+---------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| suppliers     | MyISAM |      10 | Dynamic    | 61271  |             48 |     2957212 | 281474976710655 |      2321408 |         0 |          61371 | 2010-09-29 22:55:11 | 2010-10-22 22:50:16 | 2010-09-29 22:55:12 | utf8_general_ci |     NULL | row_format=DYNAMIC |         |
+---------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| suppliernames | MyISAM |      10 | Dynamic    | 61271  |             48 |     2957212 | 281474976710655 |      2321408 |         0 |          61371 | 2010-09-29 22:55:11 | 2010-10-22 22:50:16 | 2010-09-29 22:55:12 | utf8_general_ci |     NULL | row_format=DYNAMIC |         |
+---------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| purchprices   | MyISAM |      10 | Dynamic    | 61272  |             37 |     2318820 | 281474976710655 |      2948096 |         0 |          61372 | 2010-09-28 20:14:03 | 2010-10-22 23:10:22 | 2010-09-29 20:13:11 | utf8_general_ci |     NULL | row_format=DYNAMIC |         |
+---------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| salesprices   | MyISAM |      10 | Dynamic    | 61271  |             41 |     2563864 | 281474976710655 |      2321408 |         0 |          61371 | 2010-09-29 22:55:37 | 2010-10-22 23:10:22 | 2010-09-29 22:55:37 | utf8_general_ci |     NULL | row_format=DYNAMIC |         |
+---------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
Code:
EXPLAIN SELECT...
+----+--------------------+-------+--------+----------------------------------------+-----------+---------+---------------------------------+-------+-------------+
| id | select_type        | table | type   | possible_keys                          | key       | key_len | ref                             | rows  | Extra       |
+----+--------------------+-------+--------+----------------------------------------+-----------+---------+---------------------------------+-------+-------------+
|  1 | PRIMARY            | p12   | ALL    | deleted,artikleid                      | NULL      | NULL    | NULL                            | 61271 | Using where |
|  1 | PRIMARY            | t1    | eq_ref | PRIMARY,deleted                        | PRIMARY   | 4       | store_dwh.p12.artikleid         |     1 | Using where |
|  1 | PRIMARY            | r20   | eq_ref | PRIMARY,deleted                        | PRIMARY   | 4       | store_dwh.p12.supplierid        |     1 |             |
|  1 | PRIMARY            | p13   | ref    | deleted,artikleid,supplierid           | artikleid | 4       | store_dwh.t1.id                 |     1 |             |
|  1 | PRIMARY            | p15   | ref    | deleted,artikleid                      | artikleid | 4       | store_dwh.t1.id                 |     1 |             |
|  1 | PRIMARY            | p11   | ref    | deleted,artikleid,lang                 | artikleid | 4       | store_dwh.t1.id                 |     2 |             |
|  3 | DEPENDENT SUBQUERY | p15b  | ref    | deleted,artikleid,validfrom            | artikleid | 4       | store_dwh.p15.artikleid         |     1 | Using where |
|  2 | DEPENDENT SUBQUERY | p13b  | ref    | deleted,artikleid,supplierid,validfrom | artikleid | 4       | store_dwh.p13.artikleid         |     1 | Using where |
+----+--------------------+-------+--------+----------------------------------------+-----------+---------+---------------------------------+-------+-------------+
8 rows in set (0.00 sec)
Code:
SHOW 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      |
+-------------------------+----------+
Grüsse
David
Mit Zitat antworten
  #4  
Alt 27.10.2010, 23:56:58
droehn droehn ist offline
Anfänger
  
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
AW: COUNT mit JOINs und sub-queries kriechend langsam
...und hier endlich die Tabellenstrukturen.

Code:
SHOW CREATE TABLE tbl
+---------------+------------------------------------------------------------------------------+
| Table         | Create Table                                                                 |
+---------------+------------------------------------------------------------------------------+
| articles      | CREATE TABLE `articles` (
|               | `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
|               | `cdate` int(10) NOT NULL,
|               | `udate` int(10) NOT NULL,
|               | `userid` int(10) NOT NULL,
|               | `subsid` tinyint(4) NOT NULL,
|               | `locked` tinyint(4) NOT NULL,
|               | `deleted` tinyint(4) NOT NULL,
|               | `artiklenbr` varchar(20) NOT NULL,
|               | `cml` decimal(6,2) NOT NULL,
|               | `cmw` decimal(6,2) NOT NULL,
|               | `cmh` decimal(6,2) NOT NULL,
|               | `kg` decimal(7,3) NOT NULL,
|               | `size` int(5) NOT NULL,
|               | `unit` varchar(10) NOT NULL,
|               | `warnqty` int(5) NOT NULL,
|               | `density` decimal(5,4) NOT NULL,
|               | `eshop` tinyint(1) NOT NULL,
|               | PRIMARY KEY (`id`),
|               | KEY `locked` (`locked`),
|               | KEY `deleted` (`deleted`)
|               | ) ENGINE=MyISAM AUTO_INCREMENT=61370 DEFAULT CHARSET=utf8
+---------------+------------------------------------------------------------------------------+
| labels        | CREATE TABLE `labels` (
|               | `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
|               | `cdate` int(10) NOT NULL,
|               | `udate` int(10) NOT NULL,
|               | `userid` int(10) NOT NULL,
|               | `deleted` tinyint(1) NOT NULL,
|               | `artikleid` int(10) NOT NULL,
|               | `lang` varchar(12) NOT NULL,
|               | `label1` text NOT NULL,
|               | `label2` text NOT NULL,
|               | `description` text NOT NULL,
|               | PRIMARY KEY (`id`),
|               | KEY `deleted` (`deleted`),
|               | KEY `artikleid` (`artikleid`),
|               | KEY `lang` (`lang`)
|               | ) ENGINE=MyISAM AUTO_INCREMENT=132173 DEFAULT CHARSET=utf8
+---------------+------------------------------------------------------------------------------+
| suppliers     | CREATE TABLE `suppliers` (
|               | `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
|               | `cdate` int(10) NOT NULL,
|               | `udate` int(10) NOT NULL,
|               | `userid` int(10) NOT NULL,
|               | `deleted` tinyint(1) NOT NULL,
|               | `artikleid` int(10) NOT NULL,
|               | `supplierid` int(10) NOT NULL,
|               | `artiklenbr` varchar(20) NOT NULL,
|               | `vatid` int(10) NOT NULL,
|               | `currid` int(10) NOT NULL,
|               | PRIMARY KEY (`id`),
|               | KEY `deleted` (`deleted`),
|               | KEY `artikleid` (`artikleid`),
|               | KEY `supplierid` (`supplierid`)
|               | ENGINE=MyISAM AUTO_INCREMENT=61371 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
+---------------+------------------------------------------------------------------------------+
| suppliernames | CREATE TABLE `suppliernames` (
|               | `id` int(8) unsigned zerofill NOT NULL AUTO_INCREMENT,
|               | `cdate` int(10) NOT NULL,
|               | `udate` int(10) NOT NULL,
|               | `userid` int(10) NOT NULL,
|               | `locked` int(1) NOT NULL,
|               | `deleted` int(1) NOT NULL,
|               | `ourcustnbr` varchar(12) NOT NULL,
|               | `title` varchar(8) NOT NULL,
|               | `name` varchar(60) NOT NULL,
|               | `name2` varchar(60) NOT NULL,
|               | `street` varchar(30) NOT NULL,
|               | `street2` varchar(30) NOT NULL,
|               | `zip` varchar(6) NOT NULL,
|               | `town` varchar(30) NOT NULL,
|               | `ctry` varchar(30) NOT NULL,
|               | `website` varchar(60) NOT NULL,
|               | `currid` int(10) NOT NULL,
|               | `vatid` int(10) NOT NULL,
|               | `note` text NOT NULL,
|               | `cat` varchar(20) NOT NULL,
|               | `payments` varchar(20) NOT NULL,
|               | `duedate` tinyint(2) NOT NULL,
|               | `accnbr` varchar(30) NOT NULL,
|               | `bankname` varchar(60) NOT NULL,
|               | `bankaddress` varchar(100) NOT NULL,
|               | `ddb` tinyint(1) NOT NULL,
|               | `epay` tinyint(1) NOT NULL,
|               | PRIMARY KEY (`id`),
|               | KEY `locked` (`locked`),
|               | KEY `deleted` (`deleted`)
|               | ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
+---------------+------------------------------------------------------------------------------+
| purchprices   | CREATE TABLE `purchprices` (
|               | `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
|               | `cdate` int(10) NOT NULL,
|               | `udate` int(10) NOT NULL,
|               | `userid` int(10) NOT NULL,
|               | `deleted` tinyint(1) NOT NULL,
|               | `artikleid` int(10) NOT NULL,
|               | `supplierid` int(10) NOT NULL,
|               | `listprice` float(6,2) NOT NULL,
|               | `validfrom` int(10) NOT NULL,
|               | PRIMARY KEY (`id`),
|               | KEY `deleted` (`deleted`),
|               | KEY `artikleid` (`artikleid`),
|               | KEY `supplierid` (`supplierid`),
|               | KEY `validfrom` (`validfrom`)
|               | ) ENGINE=MyISAM AUTO_INCREMENT=61372 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
+---------------+------------------------------------------------------------------------------+
| salesprices   | CREATE TABLE `salesprices` (
|               | `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
|               | `cdate` int(10) NOT NULL,
|               | `udate` int(10) NOT NULL,
|               | `userid` int(10) NOT NULL,
|               | `deleted` tinyint(1) NOT NULL,
|               | `artikleid` int(10) NOT NULL,
|               | `currid` int(10) NOT NULL,
|               | `listpreis` float(6,2) NOT NULL,
|               | `inclplus` tinyint(1) NOT NULL,
|               | `vatid` int(10) NOT NULL,
|               | `validfrom` int(10) NOT NULL,
|               | PRIMARY KEY (`id`),
|               | KEY `deleted` (`deleted`),
|               | KEY `artikleid` (`artikleid`),
|               | KEY `validfrom` (`validfrom`)
|               | ) ENGINE=MyISAM AUTO_INCREMENT=61371 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
+---------------+------------------------------------------------------------------------------+
Mit Zitat antworten
  #5  
Alt 28.10.2010, 07:49:25
thomas_w thomas_w ist offline
Junior Member
  
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: COUNT mit JOINs und sub-queries kriechend langsam
Zitat:
Zitat von droehn Beitrag anzeigen
Code:
EXPLAIN SELECT...
+----+--------------------+-------+--------+----------------------------------------+-----------+---------+---------------------------------+-------+-------------+
| id | select_type        | table | type   | possible_keys                          | key       | key_len | ref                             | rows  | Extra       |
+----+--------------------+-------+--------+----------------------------------------+-----------+---------+---------------------------------+-------+-------------+
|  1 | PRIMARY            | p12   | ALL    | deleted,artikleid                      | NULL      | NULL    | NULL                            | 61271 | Using where |
|  1 | PRIMARY            | t1    | eq_ref | PRIMARY,deleted                        | PRIMARY   | 4       | store_dwh.p12.artikleid         |     1 | Using where |
|  1 | PRIMARY            | r20   | eq_ref | PRIMARY,deleted                        | PRIMARY   | 4       | store_dwh.p12.supplierid        |     1 |             |
|  1 | PRIMARY            | p13   | ref    | deleted,artikleid,supplierid           | artikleid | 4       | store_dwh.t1.id                 |     1 |             |
|  1 | PRIMARY            | p15   | ref    | deleted,artikleid                      | artikleid | 4       | store_dwh.t1.id                 |     1 |             |
|  1 | PRIMARY            | p11   | ref    | deleted,artikleid,lang                 | artikleid | 4       | store_dwh.t1.id                 |     2 |             |
|  3 | DEPENDENT SUBQUERY | p15b  | ref    | deleted,artikleid,validfrom            | artikleid | 4       | store_dwh.p15.artikleid         |     1 | Using where |
|  2 | DEPENDENT SUBQUERY | p13b  | ref    | deleted,artikleid,supplierid,validfrom | artikleid | 4       | store_dwh.p13.artikleid         |     1 | Using where |
+----+--------------------+-------+--------+----------------------------------------+-----------+---------+---------------------------------+-------+-------------+
8 rows in set (0.00 sec)
Das nenne ich aber mal perfekt vorbereitet und formatiert!!

Wie in dem EXPLAIN zu sehen ist, findet MySQL keinen passenden Index bei der Tabelle p12 (suppliers).
In Betracht kommen zwar die Index "deleted,artikleid", aber verwendet wird keiner (siehe Spalte "key")

Zur Lösung:

Ich empfehle mal einen Index zu entfernen und einen neuen zusammengesetzen Index einzubauen. Bitte anschließend nochmal den EXPLAIN ausführen und die aktuelle CREATE TABLE der Tabelle suppliers zeigen.

Code:
ALTER TABLE suppliers
 DROP INDEX artikleid,
 ADD INDEX idx_art_del_sup (artikleid, deleted, supplierid);
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
Mit Zitat antworten
  #6  
Alt 28.10.2010, 20:43:41
droehn droehn ist offline
Anfänger
  
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
AW: COUNT mit JOINs und sub-queries kriechend langsam
Danke für die Blumen... Du hast ja auch 'ne prima Vorlage gemacht. Allerdings ist der Dump aus dem mysql command tool ein bisschen mühsam. Was benutzt Du für ein Tool?

Performance ist leider kaum besser. Anbei die Auswertungen:

Code:
EXPLAIN SELECT COUNT(t1.id)...
+----+--------------------+-------+--------+----------------------------------------+-----------------+---------+---------------------------+-------+--------------------------+
| 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                        | PRIMARY         | 4       | store_dwh.p12.artikleid   |     1 | Using where              |
|  1 | PRIMARY            | r20   | eq_ref | PRIMARY,deleted                        | PRIMARY         | 4       | store_dwh.p12.supplierid  |     1 |                          |
|  1 | PRIMARY            | p13   | ref    | deleted,artikleid,supplierid           | artikleid       | 4       | store_dwh.t1.id           |     1 |                          |
|  1 | PRIMARY            | p15   | ref    | deleted,artikleid                      | artikleid       | 4       | store_dwh.t1.id           |     1 |                          |
|  1 | PRIMARY            | p11   | ref    | deleted,artikleid,lang                 | artikleid       | 4       | store_dwh.t1.id           |     2 |                          |
|  3 | DEPENDENT SUBQUERY | p15b  | ref    | deleted,artikleid,validfrom            | artikleid       | 4       | store_dwh.p15.artikleid   |     1 | Using where              |
|  2 | DEPENDENT SUBQUERY | p13b  | ref    | deleted,artikleid,supplierid,validfrom | artikleid       | 4       | store_dwh.p13.artikleid   |     1 | Using where              |
+----+--------------------+-------+--------+----------------------------------------+-----------------+---------+---------------------------+-------+--------------------------+
8 rows in set (0.00 sec)
Code:
SHOW CREATE TABLE suppliers
+-----------+------------------------------------------------------------------------------
| Table     | Create Table              
+-----------+------------------------------------------------------------------------------
| suppliers | CREATE TABLE `suppliers` (
|           | `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
|           | `cdate` int(10) NOT NULL,
|           | `udate` int(10) NOT NULL,
|           | `userid` int(10) NOT NULL,
|           | `deleted` tinyint(1) NOT NULL,
|           | `artikleid` int(10) NOT NULL,
|           | `supplierid` int(10) NOT NULL,
|           | `artiklenbr` varchar(20) NOT NULL,
|           | `vatid` int(10) NOT NULL,
|           | `currid` int(10) NOT NULL,
|           | PRIMARY KEY (`id`),
|           | KEY `deleted` (`deleted`),
|           | KEY `supplierid` (`supplierid`),
|           | KEY `idx_art_del_sup` (`artikleid`,`deleted`,`supplierid`)
|           | ) ENGINE=MyISAM AUTO_INCREMENT=61371 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC 
+------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
Ich habe bei der Gelegenheit festgestellt, dass in der Tabelle "articles" ein paar tinyINT Felder fälschlich 4 Stellen haben (locked, deleted). Die sind nur für Flags, also 1 und 0. Soll ich stattdessen BOOL benutzen oder ist tinyINT(1) genauso gut in Sachen Performance?

Grüsse
David
Mit Zitat antworten
  #7  
Alt 28.10.2010, 21:37:53
thomas_w thomas_w ist offline
Junior Member
  
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: COUNT mit JOINs und sub-queries kriechend langsam
Zitat:
Zitat von droehn Beitrag anzeigen
Danke für die Blumen... Du hast ja auch 'ne prima Vorlage gemacht. Allerdings ist der Dump aus dem mysql command tool ein bisschen mühsam. Was benutzt Du für ein Tool?

Performance ist leider kaum besser.
Ich habe bei der Gelegenheit festgestellt, dass in der Tabelle "articles" ein paar tinyINT Felder fälschlich 4 Stellen haben (locked, deleted). Die sind nur für Flags, also 1 und 0. Soll ich stattdessen BOOL benutzen oder ist tinyINT(1) genauso gut in Sachen Performance?
Immerhin wird beim Zugriff auf die Tabelle suppliers ein Using index; angezeigt. Das ist schon mal was.

Als Tool verwende ich "Notepad" und MySQL-Console ;-)

Die tinyINT sind das Feintuning. Soweit ich weiß, belegt ein TinyINT(1) und TinyINT(4) gleich viel Platz in der Datenbank. Es ist nur eine Anzeigeformatierung.

So nun zum zweiten Änderungvorschlag:


Code:
ALTER TABLE articles
 ADD INDEX idx_id_deleted (id, deleted);
Jetzt mal ein ANALYZE TABLE articels; ausführen.

Und nun wieder der bekannte EXPLAIN und abschließend der aktuelle CREATE TABLE von articels.

Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
Mit Zitat antworten
  #8  
Alt 28.10.2010, 21:43:21
thomas_w thomas_w ist offline
Junior Member
  
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: COUNT mit JOINs und sub-queries kriechend langsam
Zitat:
SHOW 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 |
+-------------------------+----------+
Sofern Du nur MyISAM verwendest, kann der Wert von innodb_buffer_pool_size halbiert und dafür key_buffer_size verdoppelt werden.

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 28.10.2010, 23:09:03
droehn droehn ist offline
Anfänger
  
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
AW: COUNT mit JOINs und sub-queries kriechend langsam
Tolle Programme. Wo kann ich die kaufen...?

Der neue Index ist drinne aber ich denke die EXPLAIN Auswertung gibt sich ein wenig ignorant:

Code:
EXPLAIN SELECT COUNT(t1.id)...
+----+--------------------+-------+--------+----------------------------------------+-----------------+---------+---------------------------+-------+--------------------------+
| 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            | r20   | eq_ref | PRIMARY,deleted                        | PRIMARY         | 4       | store_dwh.p12.supplierid  |     1 |                          |
|  1 | PRIMARY            | p13   | ref    | deleted,artikleid,supplierid           | artikleid       | 4       | store_dwh.t1.id           |     1 |                          |
|  1 | PRIMARY            | p15   | ref    | deleted,artikleid                      | artikleid       | 4       | store_dwh.t1.id           |     1 |                          |
|  1 | PRIMARY            | p11   | ref    | deleted,artikleid,lang                 | artikleid       | 4       | store_dwh.t1.id           |     2 |                          |
|  3 | DEPENDENT SUBQUERY | p15b  | ref    | deleted,artikleid,validfrom            | artikleid       | 4       | store_dwh.p15.artikleid   |     1 | Using where              |
|  2 | DEPENDENT SUBQUERY | p13b  | ref    | deleted,artikleid,supplierid,validfrom | artikleid       | 4       | store_dwh.p13.artikleid   |     1 | Using where              |
+----+--------------------+-------+--------+----------------------------------------+-----------------+---------+---------------------------+-------+--------------------------+
8 rows in set (0.00 sec)

Code:
SHOW CREATE TABLE articles
+---------------+------------------------------------------------------------------------------+
| Table         | Create Table                                                                 |
+---------------+------------------------------------------------------------------------------+
| articles      | CREATE TABLE `articles` (
|               | `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
|               | `cdate` int(10) NOT NULL,
|               | `udate` int(10) NOT NULL,
|               | `userid` int(10) NOT NULL,
|               | `subsid` tinyint(4) NOT NULL,
|               | `locked` tinyint(4) NOT NULL,
|               | `deleted` tinyint(4) NOT NULL,
|               | `artiklenbr` varchar(20) NOT NULL,
|               | `cml` decimal(6,2) NOT NULL,
|               | `cmw` decimal(6,2) NOT NULL,
|               | `cmh` decimal(6,2) NOT NULL,
|               | `kg` decimal(7,3) NOT NULL,
|               | `size` int(5) NOT NULL,
|               | `unit` varchar(10) NOT NULL,
|               | `warnqty` int(5) NOT NULL,
|               | `density` decimal(5,4) NOT NULL,
|               | `eshop` tinyint(1) NOT NULL,
|               | PRIMARY KEY (`id`),
|               | KEY `locked` (`locked`),
|               | KEY `deleted` (`deleted`)
|               | KEY `idx_id_deleted` (`id`,`deleted`)
|               | ) ENGINE=MyISAM AUTO_INCREMENT=61370 DEFAULT CHARSET=utf8
+---------------+------------------------------------------------------------------------------+
ANALYZE TABLE meldet: Table is already up to date.

Aber: durch die Anpassung der Buffer hat sich der COUNT von 9,8 auf 9.3 Sekunden verbessert. Tataa! ;-) Aber um die Server-Einstellungen mache ich mir eh keine Sorgen, da früher oder später das Script beim Profi gehostet wird.

Ich glaube, ich komme langsam hinter die Logik Deiner Vorschläge: Die 'deleted' Spalte wurde mit der entsprechenden 'id' zusammen indiziert und damit muss MySQL nur einmal den Aspekt t1.id / t1.deleted prüfen?
Im Moment sind dort keine 'deleted' geflagt; ergab sich deshalb keine Verbesserung? Die 'deleted' Spalte in allen Tabellen führt im Moment kein Flag. Kann das Problem eventunnel damit zusammenhängen?

Grüsse
David
Mit Zitat antworten
  #10  
Alt 29.10.2010, 08:00:34
thomas_w thomas_w ist offline
Junior Member
  
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
AW: COUNT mit JOINs und sub-queries kriechend langsam
Hallo David,

Wenn MySQL die Spalten ID + DELETED zusammen im Index findet, ist dies schneller, da sich MySQL den Zugriff auf die tatsächliche Basistabelle erspart. I/O werden weniger. Wenn DELETED wenig ausprägungen hat ( nur 0 oder 1), dann ist ein eigener Index auf diese Spalte nahezu wertlos, da die Selektivität/Kardinalität denkbar schlecht ist. Mal abgesehen vom Using index; zugriff, wie ich ihn durch die kombinierten Indices versuche.

Zwischendurch ein paar Kleinigkeiten zur SQL-Abfrage

Was für Werte soll oder kann den DELETED in Zukunft annehmen. Nur 0 ( = FALSE) oder 1 (= TRUE) ? Dann ist es sinnvoll das Prädikat immer auf = abzufragen, also t1.deleted = 0

a) Bitte mal alle xx.deleted != '1' Prädikate umschreiben auf xx.deleted = t1.deleted


alt:
Code:
ON
	(
		p11.artikleid = t1.id
	AND
		p11.lang = 'deutsch'
	AND
		p11.deleted != '1'
	)
JOIN
neu:
Code:
ON
	(
		p11.artikleid = t1.id
	AND
		p11.lang = 'deutsch'
	AND
		p11.deleted = t1.deleted
	)
JOIN
und DELETED nur einmal füllen.. Deleted ist vom Typ TinyINT. Also kann die Abfrage auch numerisch sein. Dies spart ein Type-Casting.


alt:
Code:
WHERE
	t1.deleted != '1'
neu:
Code:
WHERE
	t1.deleted = 0

So, nun bitte wieder den EXPLAIN ausführen und den kompletten neuen SQL zeigen (Damit wird immer vom Selben reden).

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
Seite 1 von 3 1 23

Stichworte
count, join, langsam, mysql, subquery

« Vorheriges Thema | Nächstes Thema »

Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
 
Themen-Optionen
Ansicht
Linear-Darstellung 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.
BB-Code ist an.
Smileys sind aus.
[IMG] Code ist aus.
HTML-Code ist aus.
Gehe zu


Alle Zeitangaben in WEZ +2. Es ist jetzt 12:41:10 Uhr.

SELFPHP - Archiv - Nach oben

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


© 2001-2024 E-Mail SELFPHP OHG, info@selfphp.de, Impressum, Kontakt