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 ?
Best Answer
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.