3

Background information:

I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; frequently it uses an even tighter time scale and queries are restricted to information gathered in the last 30 days

This system has been in existence from 2004 when there were 400 uploads, today there are 2+ million

Table structure:

CREATE TABLE `data_mediagallery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `contenttype` char(40) NOT NULL DEFAULT '',
  `filename` varchar(100) NOT NULL DEFAULT '',
  `injector` char(40) NOT NULL DEFAULT '',
  `hits` int(11) DEFAULT '0',
  `message` longtext,
  `date` datetime DEFAULT NULL,
  `title` varchar(80) NOT NULL DEFAULT '',
  `tags` varchar(255) NOT NULL DEFAULT '',
  `metadata` blob,
  `location` char(8) NOT NULL DEFAULT '',
  `uid` int(11) unsigned NOT NULL DEFAULT '0',
  `filesize` bigint(20) NOT NULL DEFAULT '0',
  `upload` datetime DEFAULT NULL,
  `privacy` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `width` int(10) unsigned NOT NULL DEFAULT '0',
  `height` int(10) unsigned NOT NULL DEFAULT '0',
  `offensive` int(10) unsigned NOT NULL DEFAULT '0',
  `sourcelocation` char(8) NOT NULL DEFAULT '',
  `autoblog` tinyint(1) NOT NULL DEFAULT '0',
  `extension` char(10) NOT NULL DEFAULT '',
  `filetype` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `conversiontime` float NOT NULL DEFAULT '0',
  `converttime` datetime DEFAULT NULL,
  `sender` varchar(100) NOT NULL DEFAULT '',
  `vhost` int(10) unsigned NOT NULL DEFAULT '0',
  `channel` int(10) unsigned NOT NULL DEFAULT '0',
  `rotation` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ofilesize` bigint(20) NOT NULL DEFAULT '0',
  `moderationstatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rating` decimal(8,6) DEFAULT NULL,
  `votecount` int(10) unsigned NOT NULL DEFAULT '0',
  `url` varchar(150) NOT NULL DEFAULT '',
  `geo_latitude` double DEFAULT NULL,
  `geo_longitude` double DEFAULT NULL,
  `length` decimal(8,2) DEFAULT '0.00',
  `parentid` int(11) NOT NULL DEFAULT '0',
  `language` char(2) NOT NULL DEFAULT '',
  `author` varchar(100) NOT NULL DEFAULT '',
  `context` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `externalid` varchar(255) DEFAULT NULL,
  `originalsaved` bit(1) NOT NULL DEFAULT b'1',
  `hidden` tinyint(4) NOT NULL DEFAULT '0',
  `commentcount` int(11) NOT NULL DEFAULT '0',
  `approvedcomments` int(11) NOT NULL DEFAULT '0',
  `notdeniedcomments` int(11) NOT NULL DEFAULT '0',
  `lastupdatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `channelleft` int(10) unsigned NOT NULL DEFAULT '0',
  `originalLocation` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `upload` (`upload`),
  KEY `vhostupload` (`vhost`,`upload`),
  KEY `vhostmodstatus` (`vhost`,`status`,`moderationstatus`,`uid`),
  KEY `complexfiletype` (`vhost`,`status`,`moderationstatus`,`filetype`,`channel`),
  KEY `vhostcontext` (`vhost`,`moderationstatus`,`context`,`parentid`,`status`,`filetype`),
  KEY `externalid` (`externalid`),
  KEY `externalcomments`.
  KEY `vhostchannel` (`vhost`,`status`,`moderationstatus`,`context`,`channelleft`)
) ENGINE=InnoDB;

Questions

Is there a way to partition the table that would make the most sense? Does partitioning even make sense? How do I deal with new data if I do partition?

  • This table is a strong candidate for normalization. Large rows, especially with BLOB data, bloat the clustered index (gen_clust_index) since row data reside in it. Partitioning will only give you nominal linear performance gains at best. – RolandoMySQLDBA Jul 31 '13 at 18:23
0

Why do you want to partition? Is you performance not good, or do you have other reasons?

I don't see an index for the lastupdatetime column, and you wrote that you query for it a lot.

An alternative to partitioning could be creating tables for each year, and use merge tables to access the subsets you need.

Would have just left a comment, alas, not enough reputation.

  • Performance gains are the primary reason for partitioning. The problem is that while I can use the vhostupload key for accessing things in the last 48 hours or the last 30 days. It still can take a long time when the tables are joined. I've been thinking about merge tables as an alternative. – Mark D Jul 31 '13 at 17:05
  • Did you use explain to see whether you indexes are properly used? – Ursula Jul 31 '13 at 17:18
  • Yeah. It's just complex joins that leave me wanting more. Additionally while I know that "old" data (older than 12 months) isn't going to be accessed often it can be. Therefore I thought it might be useful to partition the table across YYYY-MM values in the upload column. – Mark D Jul 31 '13 at 17:38
  • 1
    You could benchmark the performance with merge tables or partitioning on a test server and possibly simplify your joins. – Ursula Jul 31 '13 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.