PHP Forum
Seite 1 von 3 1 23
10 Beiträge dieses Themas auf einer Seite anzeigen

PHP Forum (http://www.selfphp.de/forum/index.php)
-   MySQLi/PDO/(MySQL) (http://www.selfphp.de/forum/forumdisplay.php?f=22)
-   -   COUNT mit JOINs und sub-queries kriechend langsam (http://www.selfphp.de/forum/showthread.php?t=23706)

droehn 27.10.2010 20:47:23
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

thomas_w 27.10.2010 21:01:48
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

droehn 27.10.2010 22:53:39
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

droehn 27.10.2010 23:56:58
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
+---------------+------------------------------------------------------------------------------+

thomas_w 28.10.2010 07:49:25
AW: COUNT mit JOINs und sub-queries kriechend langsam
 
Zitat:
Zitat von droehn (Beitrag 138746)
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

droehn 28.10.2010 20:43:41
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

thomas_w 28.10.2010 21:37:53
AW: COUNT mit JOINs und sub-queries kriechend langsam
 
Zitat:
Zitat von droehn (Beitrag 138753)
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

thomas_w 28.10.2010 21:43:21
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

droehn 28.10.2010 23:09:03
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

thomas_w 29.10.2010 08:00:34
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


Alle Zeitangaben in WEZ +2. Es ist jetzt 11:25:54 Uhr.
Seite 1 von 3 1 23
10 Beiträge dieses Themas auf einer Seite anzeigen

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