Howto solve this huge query

  • Filter
  • Time
  • Show
Clear All
new posts

  • Howto solve this huge query


    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!