One huge table or thousands of tiny tables?

Forum Moderators: open

Message Too Old, No Replies

One huge table or thousands of tiny tables?

MySQL and Big Data

adder

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

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.

graeme_p

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

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.

Demaestro

7:04 pm on Mar 31, 2015 (gmt 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.

adder

12:43 pm on Apr 15, 2015 (gmt 0)

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