0

I have 10k users in my MySQL database and for every user i need to store the last 500 events.

Please advise me, how I should design my database? One MegaEventsTable or dedicated tables for every user?
Pros of many small tables:

  • I can pick fast, for example, 50 events by type from 500 entries than from 500*10k entries.
  • It is faster get top 500 entries, and kill all other entries in table for this user
  • I can shard my entries database some time in future

Contras:

  • i feel that 10k tables is HORRIBLE idea (and what if there will be 100K)
Improve this question
2
  • 3
    One table (10k * 500 isn't much data), data properly indexed. Properly indexed being the key
    – Philᵀᴹ
    Nov 17, 2015 at 12:20
  • The title is misleading. It's one table to contain them all, not to rule them all. and yeah, I get the LOTR reference, but cuteness aside, you are only confusing yourself.
    – Walter Mitty
    Nov 19, 2015 at 11:32

1 Answer 1

Reset to default
3

Exactly NOT 10k! - it always bad idea, for each open table MySQL will spend memory, cpu time, disk resources and etc

Much better give all this resources back to MySQL and it will manage queries for big table. With proper queries and indexes - millions and billions of rows in production databases.

One single table or may be few depend from other logic.

Possible solutions: 1 Table for active events Second for archived

Other split ways like event types - also possible. But generally - for open table MySQL can spend same or more time than for search single event from VERY big table

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.