3

I'm planing to design a database that contains only 2 tables (stats1 and stats2), but, this tables will contain rows going to 40 000 000 records!

All the records will be "positive/unsigned" integers.

MySQL client version: 5.0.91

Here's some info concerning both tables:

stats1

total current records = 10 000 000

contains 3 columns that stores fixed numerals as follow:

  • colA = 12345678901
  • colB = 1234
  • colC = 12345678

stats2

total current records = 5 000 000

contains 3 columns that stores fixed numerals as follow:

  • colA = 1234567890
  • colB = 12345678901234
  • colC = 12345678

I know a little bit about MySQL, PHPmyadmin but need your advice concerning making this efficient.

masegaloeh
  • 18,236
  • 10
  • 57
  • 106
numediaweb
  • 134
  • 5
  • 40 million records? I Do not do mySql - but on commercial SQL server that is not really something large. On Sql Server I would consider it non-trivial, but not something BIG. THat would start around 1 billion rows. – TomTom Jul 07 '10 at 15:14
  • @TomTom: SQL Server is much more robust. But I agree with you, even in MySQL 120 million integers is not enormous. "That would start around 1 billion rows.": IMHO, it is less about the number of rows, and more about the size of the whole data. A thousand of records containing each one 100 MB blobs *is* huge. – Arseni Mourzenko Jul 07 '10 at 15:22
  • 4
    Efficiency will depend on having the right indexes, which depends on what queries you want to do. – pjc50 Jul 07 '10 at 16:18
  • thanx for the answers guys. – numediaweb Jul 08 '10 at 10:18

2 Answers2

3

MySQL can handle that size without serious problems (we store well over 100 million).

What is the growth rate of the data? 1000 inserts a minute/hour/day? Will the growth be constant or will records be added in bulk (i.e. 100000 records in a batch)?

Your usage is the key. If you are doing arbitrary queries with aggregate functions (sum, etc.) performance will suffer.

If the data is relatively static and your performing analytic type functions on it, I would look at a column oriented database engine. Something like InfoBright or InfiniDB. Both of these work with MySQL clients. If you now how to use MySQL, you can use these. But, they may not work for you depending on how you want to access and query your data.

Craig
  • 1,354
  • 6
  • 14
  • +1 for InfoBright and InfiniDB. – Arseni Mourzenko Jul 07 '10 at 16:37
  • Thank you guys for your support At the moment I will just read from the database; will not perform "complex" queries. For the moment i'll stick with row oriented databse engine (I'm not advanced on database world, so i'll keep it simple riht now!) thanx ;-) – numediaweb Jul 08 '10 at 09:29
2

First, 4×10⁷ columns with three integers in each is not toooo big. I suppose even Sqlite or less robust databases can handle this number of records.

Now, if you ask about efficiency, everything depends on the usage of the database. All you said is how is stored the data, but more important is how is it used. Consider the following situations:

  • The numbers are sent from a satellite and stored in database for further analysis,
  • The numbers are collected from an experiment and you must check they are unique before storing them,
  • The numbers are used for statistics and retrieved one by one, from a single machine,
  • The numbers are accessed randomly by hundreds of machines in parallel.

Every case requires a different approach.

Arseni Mourzenko
  • 2,275
  • 5
  • 28
  • 41