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)
mysql>
Note that I was careful to ensure that the query cache was not active for
these queries. I'm fully aware that a smaller table should go faster, but
in the MySQL manual is says this about BLOB columns, at the end of section
11.3.2:
"Note that each BLOB or TEXT value is represented internally by a separately
allocated object. This is in contrast to all other column types, for which
storage is allocated once per column when the table is opened."
I took this to mean that the BLOB data is stored outside the table, meaning
that the table should still be 'small', but that you get a double seek hit
accessing the BLOB contents (which would be absolutely fine for what I am
doing since I access files much less than examining the metadata).
I'm wondering if I should change my schema to have the BLOBs in a new table
and just keep the meta-data in a table of it's own, but I thought that using
a BLOB would effectively do this for me?
Are my assumptions wrong, and is separating the BLOB contents into a
different table a good solution?
Any help appreciated,
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
