Welcome to WebmasterWorld Guest from 54.162.224.176

Forum Moderators: open

Message Too Old, No Replies

One huge table or thousands of tiny tables?

MySQL and Big Data

     
5:49 pm on Mar 31, 2015 (gmt 0)

Preferred Member from GB 

10+ Year Member Top Contributors Of The Month

joined:July 25, 2005
posts:402
votes: 16


Hi,
I'm developing a multi-user toolset. We're talking about 5 data-intensive tools and predicted 1,000 users.

Currently each tool has a separate table and whenever I run a tool, it dumps up to 20,000 rows into a table. It's ok now that I'm the only person using the toolset. What happens when other users join? Tens of millions of rows!

Some tools are interlinked and querying/comparing several tables.

Somebody suggested I should automatically create a set of personal tables for each user who signs up, but then it's 5,000 tables or potentially more.

From the perspective of server resources and page load times, what would you choose? Keep five massive tables or deal with thousands of small tables?

thanks.
7:00 pm on Mar 31, 2015 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Nov 16, 2005
posts: 2773
votes: 112


Five massive tables, appropriately indexed. People get decent performance out of much larger tables than that. Lots of small tables can also cause performance problems, and you will not know how well each will work until you try it.

If the massive tables do not work well, after indexing and tuning, then look at partitioning properly.

You may find it useful to read some of what people who run really massive databases have to say. I found some interesting articles on the Instagram and Disqus blogs, but they both you Postgres.
7:04 pm on Mar 31, 2015 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2610
votes: 0


Without seeing your schema and some sample of the data in there it is hard to say, but it sounds like you could benefit from some normalizing, adding some relational tables for each tool table would be ideal if it reduces the rows in the main table.

I would say as a general rule NEVER create a table per user unless each user has it's own separate installation and hosted environment. But if this is all living on an application server, with 1 installation and many users, then don't go the route of a table per user, it will make reporting an absolute nightmare and it not considered a good approach at all.

If you want to post your table schema for one of the tools and some sample data I can help you determine if you can break that into some relational tables.
12:43 pm on Apr 15, 2015 (gmt 0)

Preferred Member from GB 

10+ Year Member Top Contributors Of The Month

joined:July 25, 2005
posts:402
votes: 16


@graeme_p and @Demaestro, thanks for setting my mind at ease! Five tables it is then!
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members