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)
Best Answer
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