Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have billions of rows in my MySQL table. So when I do a select for a specific date range , it takes minutes to return results, or sometimes even hangs up.

Is there any config settings in MySQL so that it saves data on disk monthwise/yearwise?

I know that MySQL saves table data in disk in table.myd file. I am looking for some config changes in MySQL by which we have multiple myd files for the same table for each month/year.

share|improve this question
add comment

1 Answer

You can use MySQL Partitioning. You can partition your data on the basis of month/year.

Example: I have a table which I have partitioned by year

CREATE TABLE `RTestAll` (
  `recordDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `networkId` bigint(11) NOT NULL DEFAULT '0',
  `publisherId` bigint(11) NOT NULL DEFAULT '0',
  `feedId` bigint(11) NOT NULL DEFAULT '0',
  `subPublisherId` bigint(11) NOT NULL DEFAULT '0',
  `subId` varchar(100) NOT NULL DEFAULT '',
  `searches` bigint(20) DEFAULT NULL,
  `matches` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`recordDate`,`networkId`,`publisherId`,`feedId`,`subPublisherId`,`subId`),
  KEY `K_networkId` (`networkId`),
  KEY `K_publisherId` (`publisherId`),
  KEY `K_feedId` (`feedId`),
  KEY `K_subPublisherId` (`subPublisherId`)
) ENGINE=InnoDB CHARSET=latin1
 PARTITION BY RANGE (YEAR(recordDate))
(
 PARTITION p0 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN MAXVALUE ENGINE = InnoDB
) ;

For reference please have a look at Partitioning Types in MySQL.

For use of partitioning, your SELECT queries should be designed in such a way that they use the partition key.

Going ahead please also have a look at Limitations of partitioning.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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