I have two tables Customers and Orders and each has 100 million records.

Now I have to join these two tables on the column customer_id in both Customer and Order table to generate a report. customer_id is the primary key in customer table and foreign key in order table.

I have heard from senior folks that it is close to impossible to do it MySQL(or any other RDBMS) because a single system has to perform this join on a huge volume of data. Is that correct RDBMS like MySQL(or Oracle) are not good for such a large volume of data even for simple operations like join with proper indexes ?

Also I would like to know what data size per table is considered good for RDBMS table so that simple operations like join, aggregation with proper indexes works well ? I know answer may be "It depends" or vary based on number of factors. But consider best possible tuning is done, still is there any broad level standard data size limit that is considered good ?

  • Why such a big report? Who's going to read a report with 100M records? At 100 records per page, that's a million pages. You want aggregation. All of the RDBMS dbs today would find no problem with aggregates over large numbers of records - with good indexes. Those and an SSD and you're good to go!
    – Vérace
    May 6 '19 at 3:54
  • 1
    Anything under INFINITY is good. May 6 '19 at 5:04
  • 100kk customers? really? I think it is possible only in a case when the data is extremely de-normalized...
    – Akina
    May 6 '19 at 6:03
  • 2
    But search and join has to run on 100M records. No.
    – Akina
    May 6 '19 at 7:37
  • 1
    @Vérace As @Akina points out, with indexes, nothing will have to run over 100M records! What I mean here is , For one table it has to go through 100M records and for join to other table it will use the index(that can be alomost O(1)). DDL are not created yet , this design is under discussion yet
    – emilly
    May 7 '19 at 2:33

Basically you are talking about performance. There are so many factors/parameters responsible for performance. With no doubt 100 million records are feasible in RDBMS, it depends upon configuration you have made for your server. If we look for some parameters be like :

1) Hardware : Looking into your physical table size, proper RAM allocation is done or not. For feasible amount of RAM proper CPU cores are divided or not.

( Specifically of MySQL )

2) Sort Buffer : Whether optimal values are configured for Sort buffer global variables.

3) Temporary Table Size : Temporary table size should be suitable enough

4) Join Buffer : Join buffer should be configured with optimal value.

And many more parameters need to look in.

Hope this helps.

Your Answer

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

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