0

I have to make 25 modification historic tables from 26 tables. I need a design for each table that could grow more than 75,000,000 of rows.

I use InnoDB for 25 tables and 1 with MyISAM. I have one design in mind but I want to see other choices vs what I got.

My company uses Birt as BIT(business intelligence tools) and uses historic logs a lot.

Legend:

  • OTPK: Original Table Primary Key.

  • HTNAME: Historic Table Name.

  • IDX: It has an index.

My design tables:

ChangeInfo:

  • ChangeID INT(3) (PK)
  • ChangeName VARCHAR(75)

HTNAME_LOG:

  • OTPK_Id` INT (PK)
  • ChangeTime DATETIME (PK)
  • ChaValue VARCHAR(255) (Varchar value of the OTPK)
  • IntValue BIGINT(15) (Int value of the OTPK)
  • ChangeID INT(3) (PK) (Change Info PK)
  • DataType BIT(1) IDX (If true, integer(IntValue), varchar(ChaValue). This is for reading queries)

PD: All the datatypes depend of the Original Table.

Any suggestion will be appreciated.

Improve this question
8
  • Let's approach it from the other side -- What queries to you need to run against the new tables? – Rick James Jun 29 '15 at 16:10
  • I can't tell you at this time, the amount of queries they're going to use could be 0-1000. It's going to depend of which tables they'll need to make reports. I just want to be sure it wont matter if the historic tables have 1 row or 75,000,000, the report speed it's going to depend of the table, indexes, rows and query building. MySQL server has good hardware and software. – oNare Jun 29 '15 at 16:21
  • 1
    No. "Reports" on 75M row tables need summary tables. And/or re-design to make them smaller. It can take hours to shovel through that many rows. Now is the time to "think ahead". – Rick James Jun 29 '15 at 16:45
  • 1
    You list 3 tables, but say there are 26. Please clarify. – Rick James Jul 29 '15 at 1:54
  • 1
    If your changes could rise up to over 75,000,000, shouldn't ChangeID column be a bigint? – Ozzie Jul 29 '15 at 16:11

Your Answer

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

Browse other questions tagged or ask your own question.