Thanks for your answers. I will digg into the articles provided by Rick. Looking at the EXPLAIN didn't help me yet. But may I have to join the file table because of the CASCADE reference or doing something else to "emulate" the delete?
regards
Daniel
create table statements
CREATE TABLE `category` (
`categoryid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`name`),
UNIQUE KEY `categoryid` (`categoryid`)
) ENGINE=InnoDB AUTO_INCREMENT=2133880 DEFAULT CHARSET=utf8
CREATE TABLE `package` (
`fk_categoryid` int(11) unsigned NOT NULL,
`packageid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`version` varchar(255) NOT NULL,
`compiled` datetime DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`fk_categoryid`,`name`,`version`),
UNIQUE KEY `packageid` (`packageid`),
KEY `name` (`name`),
KEY `compiled` (`compiled`),
CONSTRAINT `package_ibfk_1` FOREIGN KEY (`fk_categoryid`) REFERENCES `category` (`categoryid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8817177 DEFAULT CHARSET=utf8
CREATE TABLE `file` (
`fk_packageid` int(11) unsigned NOT NULL,
`fileid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`path` varchar(255) NOT NULL,
`file` varchar(5000) NOT NULL,
`misc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`fk_packageid`,`path`,`name`),
UNIQUE KEY `fileid` (`fileid`),
KEY `name` (`name`,`path`),
KEY `name_2` (`name`),
KEY `file` (`file`(255)),
CONSTRAINT `file_ibfk_1` FOREIGN KEY (`fk_packageid`) REFERENCES `package` (`packageid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2084920112 DEFAULT CHARSET=utf8
DELETE query
DELETE FROM package WHERE packageid = 7749
Explain of corresponding(?) SELECT query
mysql> EXPLAIN EXTENDED SELECT * FROM package WHERE packageid = 7749;
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | package | const | packageid | packageid | 4 | const | 1 | 100.00 | |
+----+-------------+---------+-------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
MySQL Version
# mysql --version
mysql Ver 14.14 Distrib 5.1.67, for pc-linux-gnu (x86_64) using readline 5.1