Re: InnoDb Table Performance problem

Benoit St-Jean Wed, 11 Feb 2004 14:47:23 -0800

Michael McTernan wrote:

Hi there,

I'm making something similar to a file revision control system, and using
MySQL on Linux as the database to drive it.  Almost all my tables are
InnoDB, and generally it is going very well, with the exception of one table
that is always very slow.

This table holds the files within the database. It is defined as follows:

CREATE TABLE `files` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `revision` int(10) unsigned NOT NULL default '0',
 `filenameid` int(10) unsigned NOT NULL default '0',
 `pathid` int(10) unsigned NOT NULL default '0',
 `extensionid` int(10) unsigned NOT NULL default '0',
 `isDeleted` enum('0','1') NOT NULL default '0',
 `filepathname` tinytext NOT NULL,
 `contentsGz` longblob NOT NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `revision` (`revision`,`filepathname`(255)),
 KEY `fpeindex` (`filenameid`,`pathid`,`extensionid`,`revision`),
 KEY `filepathname` (`filepathname`(255)),
 CONSTRAINT `0_3570` FOREIGN KEY (`filenameid`) REFERENCES `filenames`
(`id`),
 CONSTRAINT `0_3571` FOREIGN KEY (`extensionid`) REFERENCES
`fileextensions` (`id`),
 CONSTRAINT `0_3572` FOREIGN KEY (`pathid`) REFERENCES `filepaths` (`id`)
) TYPE=InnoDB;

The 'contentsGz' column will have the contents of the file and will
typically be a couple of hundred kilobytes, but in some rare cases as large
as 20 Megabytes.

Selects on this table always go very slowly.  I've used EXPLAIN to look at
what is going on, and carefully added a couple of multi-column indexes that
have improved SELECT performance (this table is updated rarely, so I'm not
too worried about INSERT performance).  However, the performance is still
really bad.

I tried creating an identical table with the exception that it doesn't have
the 'contentsGz' column:

CREATE TABLE `filemetadata` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `revision` int(10) unsigned NOT NULL default '0',
 `filenameid` int(10) unsigned NOT NULL default '0',
 `pathid` int(10) unsigned NOT NULL default '0',
 `extensionid` int(10) unsigned NOT NULL default '0',
 `isDeleted` enum('0','1') NOT NULL default '0',
 `filepathname` tinytext NOT NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `revision` (`revision`,`filepathname`(255)),
 KEY `fpeindex` (`filenameid`,`pathid`,`extensionid`,`revision`),
 KEY `filepathname` (`filepathname`(255)),
 CONSTRAINT `0_3651` FOREIGN KEY (`filenameid`) REFERENCES `filenames`
(`id`),
 CONSTRAINT `0_3652` FOREIGN KEY (`extensionid`) REFERENCES
`fileextensions` (`id`),
 CONSTRAINT `0_3653` FOREIGN KEY (`pathid`) REFERENCES `filepaths` (`id`)
) TYPE=InnoDB;

I used UPDATE ... SELECT to copy all data from the 'files' table to
'filemetadata'.

Here is something I found suprising:

mysql> SELECT COUNT(1) FROM files;
+----------+
| COUNT(1) |
+----------+
|   101013 |
+----------+
1 row in set (32.42 sec)

mysql> SELECT COUNT(1) FROM filemetadata; +----------+ | COUNT(1) | +----------+ | 101013 | +----------+ 1 row in set (0.29 sec)

SELECT COUNT(*) for InnoDB tables is a know problem... The table handler (for InnoDB) has to do a table scan to count all rows... This particular case is optimized with MyISAM ...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDb Table Performance problem

Reply via email to