MySQL Forums
Forum List  »  InnoDB

Re: optimizing delete from table with million of records
Posted by: Daniel Foobar
Date: October 07, 2013 01:08PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: optimizing delete from table with million of records
1856
October 07, 2013 01:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.