Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

thanx!

share|improve this question
    
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 7 '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. –  MainMa Jul 7 '10 at 15:22
4  
Efficiency will depend on having the right indexes, which depends on what queries you want to do. –  pjc50 Jul 7 '10 at 16:18
    
thanx for the answers guys. –  numediaweb Jul 8 '10 at 10:18
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
+1 for InfoBright and InfiniDB. –  MainMa Jul 7 '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 8 '10 at 9:29
add comment

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.

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.