CODEWITHSUNDEEP
mysql
Dan
Dan

Reputation: 224

How to structure a MYSQL table with lots of data?

I have statistics for every user in our system. These statistics need to be updated frequently.

The statistics looks something like this:

Campaign : Performance
Campaign : Performance

Each campaign has it's own data stored in another table in MYSQL. The performance metric needs to be updated for each user and each campaign frequently. There are over 1000 campaigns that need to be updated for over 5000 users.

At first I considered simply making a huge VARCHAR column which just stored the data as "campaignId-Performance,campaignId2-Performance,..."

When querying the DB these campaigns will be searched for by their COUNTRY first and then ordered in descending order by the highest performing. The COUNTRY data is stored in the campaigns table. In my new table I was considering making a column for every COUNTRY and splitting the campaigns with their respective performances up like that. I'm unsure whether that would be more efficient or not.

With this poor description I'm hoping someone can suggest an optimal structure. Thanks!

Upvotes: 0

Views: 159

Answers (2)

invertedSpear
invertedSpear

Reputation: 11054

I would think you would want to store this performance in a table with 3 columns: campaignID,userID,performance. This would give you the most flexibility to then query the information however you might need it. Would easily aggregate performance by campaign, or by user. and with a quick join to the campaign table would allow you to aggregate by country as well.

Upvotes: 1

Aszula
Aszula

Reputation: 163

So, your building a MySQL database that contains 1000 campaigns and 5000 users? Do the campaigns contain the performances of each country? If so, I would suggest making a column named Country followed by Performance Column. Also, it would be helpful to know what your trying to view the information in.

Upvotes: 0

Related Questions