I’m in a bit of a mess here.
I run an web application thats hosts campaigns.
Each campaign has it’s own table with it’s activity (impression, click, order). The reason for this is that a single campaign can have more than 1 000 000 impressions, 100 000 clicks, 10 000 orders for example.
At the moment I have about 100 campaigns and therefore about 100 campaign tables.
I have partners that use my campaigns at their sites and I want to show partners how many impressions, clicks and orders they generated so they can see how much money they made.
The problem is when i want to show, let’s say the number of imps, clicks and orders for this week, ordered by date, and then ordered by campaign name.
At this moment i join the campaign table (1 row per campaign with campaign info) with the corresponding activitytable and check if theres been som activity within the given period, and then a UNION ALL with the next campaign etc.
This query will just grow and grow the more campaings I host.
How would you guys solve this problem?
Would you change the database design?
Even today I have problems with mysql crashing from time to time.
I would really be grateful if you try to help me, thanks!