1

i have plan to create a analytic's table. Monthly 1 million records approx can be insert to the table. help me better way of optimizing and also fetch records fast way. Alternatively i have planned to split the table monthly (Running a cron on server to create table) all insertions will insert rows to respective month table. Is this better way to design the tables.

Please advise on database design

Sam Dufel
  • 17,560
  • 3
  • 48
  • 51
Jayam bala
  • 11
  • 6
  • 1
    have in mind that databases are meant to parse through large amounts of data – ikromm Jul 18 '13 at 07:15
  • Showing us the table definition might help: but assuming you're adding analytics records, the most obvious is to [partition](http://dev.mysql.com/doc/refman/5.6/en/partitioning.html) it against the date created column, either by year or by month/year - but if you create a table for each month/year then you'll hit problems the instance somebody wants a quarterly or annual summary, or a comparison with this month last year – Mark Baker Jul 18 '13 at 07:18
  • `code` CREATE TABLE IF NOT EXISTS `webanalytics_mm_yy` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `companyid` bigint(20) NOT NULL, `visits_count` int(8) NOT NULL, `country_name` varchar(64) NOT NULL, `referal_source` varchar(128) NOT NULL, `source_name` varchar(128) NOT NULL COMMENT 'This based on source give the name', `created_date` datetime NOT NULL, `ip_address` varchar(128) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=63 ; – Jayam bala Jul 18 '13 at 07:52

1 Answers1

0

I suggest Sharding the database. More information can be found here importance of sharding

and approaches towards MySQL sharding. Hope this helps.

Community
  • 1
  • 1
Puneet
  • 603
  • 9
  • 18