CODEWITHSUNDEEP
mysqlsqlperformancedynamic-tables
Indigenuity
Indigenuity

Reputation: 9740

Use one giant MySQL table?

Say I have a table with 25,000 or so rows:

item_id, item_name, item_value, etc...

My application will allow users to generate dynamic lists of anywhere from 2-300 items each.

Should I store all of these relationships in a giant table with columns dynamic_list_id, item_id? Each dynamic list would end up having 2-300 rows in this table, and the size of the table would likely balloon to the millions, or even billions.

This table would also be queried quite frequently, retrieving several of these dynamic lists each second. Is a giant table the best way to go? Would it make sense to split it up into dynamic tables, perhaps named by user?

I'm really at a loss when it comes to preparing databases for giant amounts of data like this, so any insight would be much appreciated.

Upvotes: 1

Views: 189

Answers (3)

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

Yes, I recommend going with yor proposed design: "a giant table with columns dynamic_list_id, item_id."

Performance can easily be addressed as required, through index selection, and by increasing the number of spindles and read/write arms, and SSD caching.

And inthe grand scheme of things, this database does not look to be particularly big. These days it takes dozens or hundreds of TB to be a BIG database.

Upvotes: 2

Michael Benjamin
Michael Benjamin

Reputation: 2915

With such a large table make sure to set your engine to InnoDB for row level locks.
Make sure you're using indexes wisely.
If your query starts to drag, increase the size of the Innodb_buffer_pool to compensate.

Upvotes: 1

MattW
MattW

Reputation: 4628

It's a relational database, it's designed for that kind of thing - just go for it. A mere few million rows doesn't even count as "giant". Think very carefully about indexing though - you have to balance insert/update performance, storage space and query performance.

Upvotes: 3

Related Questions