title: Table structure for calendar - Store data based on date
body: <p>I have a calendar used to store informations based on a date (not a datetime) for any user.</p>
  
  <p>On my website, a user can select a specific date and fill some information about himself for this date.
  At this point, my table structure look like this</p>
  
  <pre><code>+----+---------+------------+-----------+
  | id | user_id | event_date | data      |
  +----+---------+------------+-----------+
  |  1 |      25 | 2015-08-25 | Some Data |
  +----+---------+------------+-----------+
  </code></pre>
  
  <p>In reality, the column data doesn't exist, there's multiple boolean columns instead but it's simplier this way.<br/> 
  What's important is that I need get all data field's for every user for one day. And it need to be as fast as possible.<br/> 
  For now, I'm just running the following query.</p>
  
  <pre><code>SELECT `data` FROM `calendar` WHERE `event_date` = '2015-07-08'
  </code></pre>
  
  <p>My problem is that with this structure, my table's size is gradually increasing over time and it's becoming slower and slower to SELECT from this table (it has currently ~20 000 000 lines).<br/>
  I'm already removing data older than a year but since the number of users is increasing, so do my table.<br/></p>
  
  <p>One small precision, on the website, user's are able to fill the calendar using some kind of recurring events. It looks like the following:</p>
  
  <blockquote>
    <p>For Every Monday &amp; Saturday From [start_date] to [end_date], set
    data="Some Value".</p>
  </blockquote>
  
  <p>Because of this, I was wondering if using a table structure to store recurring events wasn't better than  current table. 
  I've seen <a href="https://stackoverflow.com/questions/5183630/calendar-recurring-repeating-events-best-storage-method">this answer</a> (and other similar) which propose the following structure</p>
  
  <blockquote>
    <p>Assuming I have two tables, one called events like this:</p>
  
  <pre><code>ID    NAME
  1     Sample Event
  2     Another Event
  </code></pre>
    
    <p>And a table called events_meta like this:</p>
  
  <pre><code>ID    event_id      meta_key           meta_value
  1     1             repeat_start       1299132000
  2     1             repeat_interval_1  432000
  </code></pre>
  </blockquote>
  
  <p>But this structure doesn't seems to fit my need :</p>
  
  <ul>
  <li>It doesn't seems to handle exception (the event is repeating every Saturday, but not this one)</li>
  <li>I'm afraid that the computation time required to obtain a date from <code>repeat_start</code> and <code>repeat_interval</code> will be longer than the current select time.</li>
  </ul>
  
  <p>Is there a better table structure to store data for a date ? As I said, my need is to get every user's data for a specific date as fast as possible.</p>
  
  <p>PS : I already have an INDEX on my <code>event_date</code> column.</p>
  
  <p>Here is the explain of the query &amp; the result of SHOW CREATE TABLE</p>
  
  <pre><code>+----+-------------+----------+------+---------------+------------+---------+-------+--------+-------+
  | id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows   | Extra |
  +----+-------------+----------+------+---------------+------------+---------+-------+--------+-------+
  |  1 | SIMPLE      | calendar | ref  | event_date    | event_date | 3       | const | 127591 | NULL  |
  +----+-------------+----------+------+---------------+------------+---------+-------+--------+-------+
  
  CREATE TABLE IF NOT EXISTS `calendar` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(10) unsigned NOT NULL,
    `event_date` date NOT NULL,
    `data` varchar(128) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_index` (`user_id`,`event_date`),
    KEY `event_date` (`event_date`)
  )
  </code></pre>