MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Benoit St-Jean Date:February 11 2004 10:46pm
Subject:Re: InnoDb Table Performance problem
View as plain text  
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 ...

>
>
>
>  
>



Thread
InnoDb Table Performance problemMichael McTernan11 Feb
  • Re: InnoDb Table Performance problemBenoit St-Jean11 Feb
    • RE: InnoDb Table Performance problemMichael McTernan12 Feb
      • Re: InnoDb Table Performance problemChris Nolan12 Feb
        • RE: InnoDb Table Performance problemMichael McTernan12 Feb
          • Re: InnoDb Table Performance problemChris Nolan12 Feb
            • RE: InnoDb Table Performance problemMichael McTernan12 Feb
      • Re: InnoDb Table Performance problemBenoit St-Jean12 Feb
        • RE: InnoDb Table Performance problemMichael McTernan12 Feb