title: MySQL Need suggestion with database structure on storing large data
body: <p>I need to store statistics of my website.</p>
  
  <p>I need these</p>
  
  <pre><code>1: I can query how many hits in day, week month, last month, etc based on a category.
  2: Then I can sort stats according to countries, day, pages etc.
  </code></pre>
  
  <p>I know these way to do it</p>
  
  <p><strong>Table all_stats</strong></p>
  
  <pre><code>| category |  page_url  | country |    date     |.......
  |    x     | index.php  |    US   |  timestamp  |.......
  |    y     | index2.php |    UK   |  timestamp  |.......
  .
  .
  </code></pre>
  
  <p>Then I can query as </p>
  
  <pre><code>SELECT * FROM all_stats WHERE category=x AND date&lt;=today AND date&gt;=last week
  
  This works fine but problem with this is 
  as database increase query takes a lot of time to execute.
  </code></pre>
  
  <p>To solve query speed store daily country based stats from above table in other table with 256 countries and island etc like</p>
  
  <p><strong>Table daily_stats</strong></p>
  
  <pre><code>| category |  page_url  |   US    |    UK   |.......|  date |
  |    x     | index.php  |   1000  |  1500   |.......|   1st |
  |    y     | index2.php |   1500  |  2000   |.......|   2nd |
  .
  .
  </code></pre>
  
  <p>Then I can query like</p>
  
  <pre><code>SELECT 
  SUM(US) AS us
  SUM(Uk) AS uk
  .
  .  //all 256 countries and island etc
  .
  .
  WHERE category=x AND date&lt;=today AND date&gt;=last week
  
  But this will be a big table with many columns
  </code></pre>
  
  <p>Another approch I see is </p>
  
  <p><strong>Creating table smart_stats</strong> </p>
  
  <pre><code>| category |  page_url  |     country_array    .......|  date   |
  |    x     | index.php  |   US=&gt;1000, UK=1500  .......|  1st    |
  |    y     | index2.php |   US=&gt;1500, UK=2000  .......|  2nd    |
  .
  .
  
  This can be smart approach if I can add all country stats in some way
  </code></pre>
  
  <p>Please suggest a way to make possible query from table smart_stats country_array that I can sort in any way I like</p>
  
  <p>Or suggest what you think will be the best approach to this</p>
  
  <p>Thanks</p>