where is null

Hallo.

Ich habe mal eine Nachfrage zur Optimierung einer Abfrage,
bei der die Daten gefunden werden sollen, die nicht in einer
anderen Tabelle entsprechende Daten haben, z.B.

select id from artikel a
left join artikeleigenschaft b on b.artikelid = a.id
where b.artikelid is null

Wenn die Tabellen ENGINE=MyISAM haben, dann dauert die Abfrage
ca. 4x so lange wie bei ENGINE=InnoDB (Serverauslastung jeweils in
etwa gleich). Das habe ich durch Zufall festgestellt und daher einfach
mal
mit gesendet.

Ein foreign key bringt keine Geschwindigkeitserhöhung. Als Lösung sehe
ich nur, das man in der Tabelle artikel ein Flag
_hatartikeleigenschaft mit
einbringt, welchen man über trigger automatisch aktualisieren kann.

Hat jemand andere Vorschläge? Oder was mache ich falsch?

Gruß
Andreas

P.S. Das ermitteln der Artikel mit eigenschaftid = 5 geht
verständlicherweise recht schnell


Als Beispiel: (es gibt 500 000 Artikel mit je 5 Artikeleigenschaften)

mysql> show create table artikel \G
*************************** 1. row ***************************
Table: artikel
Create Table: CREATE TABLE `artikel` (
`id` int(11) NOT NULL auto_increment,
`name` char(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table artikeleigenschaft \G
*************************** 1. row ***************************
Table: artikeleigenschaft
Create Table: CREATE TABLE `artikeleigenschaft` (
`artikelid` int(11) default NULL,
`eigenschaftid` int(11) default NULL,
KEY `artikelid` (`artikelid`),
KEY `eigenschaftid` (`eigenschaftid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count( * ) from artikel a
left join artikeleigenschaft b on b.artikelid = a.id where b.artikelid
is null \G
*************************** 1. row ***************************
count( * ): 0
1 row in set (19.51 sec)

mysql> explain select count( * ) from artikel a
left join artikeleigenschaft b on b.artikelid = a.id where b.artikelid
is null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 500000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: artikelid
key: artikelid
key_len: 5
ref: test.a.id
rows: 5
Extra: Using where; Using index
2 rows in set (0.00 sec)

mysql> alter table artikel engine = innodb ;
Query OK, 500000 rows affected (9.35 sec)
Records: 500000 Duplicates: 0 Warnings: 0

mysql> alter table artikeleigenschaft engine = innodb ;
Query OK, 2500000 rows affected (1 min 19.23 sec)
Records: 2500000 Duplicates: 0 Warnings: 0

mysql> show create table artikel \G
*************************** 1. row ***************************
Table: artikel
Create Table: CREATE TABLE `artikel` (
`id` int(11) NOT NULL auto_increment,
`name` char(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table artikeleigenschaft \G
*************************** 1. row ***************************
Table: artikeleigenschaft
Create Table: CREATE TABLE `artikeleigenschaft` (
`artikelid` int(11) default NULL,
`eigenschaftid` int(11) default NULL,
KEY `artikelid` (`artikelid`),
KEY `eigenschaftid` (`eigenschaftid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count( * ) from artikel a
left join artikeleigenschaft b on b.artikelid = a.id where b.artikelid
is null \G
*************************** 1. row ***************************
count( * ): 0
1 row in set (5.14 sec)

mysql> explain select count( * ) from artikel a
left join artikeleigenschaft b on b.artikelid = a.id where b.artikelid
is null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 500397
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: artikelid
key: artikelid
key_len: 5
ref: test.a.id
rows: 2
Extra: Using where; Using index
2 rows in set (0.00 sec)

mysql> insert into artikel ( name ) values ( 'ohne eigenschaften' ) ;
Query OK, 1 row affected (0.03 sec)

mysql> alter table artikeleigenschaft add foreign key ( artikelid )
references artikel ( id ) ;
Query OK, 2500000 rows affected (1 min 45.81 sec)
Records: 2500000 Duplicates: 0 Warnings: 0

mysql> select count( * ) from artikel a
left join artikeleigenschaft b on b.artikelid = a.id where b.artikelid
is null \G
*************************** 1. row ***************************
count( * ): 1
1 row in set (5.17 sec)

mysql> explain select count( * ) from artikel a
left join artikeleigenschaft b on b.artikelid = a.id where b.artikelid
is null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 500398
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: artikelid
key: artikelid
key_len: 5
ref: test.a.id
rows: 2
Extra: Using where; Using index
2 rows in set (0.00 sec)

mysql> select count( * ) from artikel a
left join artikeleigenschaft b on b.artikelid = a.id where
b.eigenschaftid = 1 \G
*************************** 1. row ***************************
count( * ): 25160
1 row in set (0.56 sec)

mysql> explain select count( * ) from artikel a
left join artikeleigenschaft b on b.artikelid = a.id where
b.eigenschaftid = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: artikelid,eigenschaftid
key: eigenschaftid
key_len: 5
ref: const
rows: 39224
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.b.artikelid
rows: 1
Extra: Using index
2 rows in set (0.00 sec)

mysql> select version() \G
*************************** 1. row ***************************
version(): 5.0.22-Debian_4-log
1 row in set (0.00 sec)