I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.
This application will have 1M users, each user will have their own dashboard where they can create their own users/roles, pages and posts.
I am thinking about two possible solutions
- Use only 1 table for all users, pages and posts and they will have a foreign key of the owner
- Use separate tables for all users, like user1_pages, user1_posts, user1_users
I Think with #1 the query will be slow as all users will be sharing one table, second one also don't seems a perfect solution as number of tables will increase exponentially.
Please suggest me a best possible solution and share tips to design an optimized MySQL schema.