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 ?