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