MySQL Query Performance on Calendar Database

Question

Hello all. I am creating a calendar webapp and I'm kinda stuck between a performance vs storage issue in the creation and subsequent queries of my events table. The concept is "how to make a table with repeating events (daily/weekly)?" Here is my current solution:

CREATE TABLE `events` (
  `eventid` int(10) NOT NULL AUTO_INCREMENT,    //primary key
  `evttitle` varchar(255) NOT NULL,             //title of event
  `createdby` char(8) NOT NULL,                 //user identification (I'm using
  `evtdatestart` date NOT NULL,                 ////another's login system)
  `evtdateend` date NOT NULL,
  `evttimestart` time NOT NULL,
  `evttimeend` time NOT NULL,
  `evtrepdaily` tinyint(1) NOT NULL DEFAULT 0,   //if both are '0' then its
  `evtrepweekly` tinyint(1) NOT NULL DEFAULT 0,  //a one time event
  `evtrepsun` tinyint(1) NOT NULL DEFAULT 0,
  `evtrepmon` tinyint(1) NOT NULL DEFAULT 0,
  `evtreptue` tinyint(1) NOT NULL DEFAULT 0,
  `evtrepwed` tinyint(1) NOT NULL DEFAULT 0,
  `evtrepthu` tinyint(1) NOT NULL DEFAULT 0,
  `evtrepfri` tinyint(1) NOT NULL DEFAULT 0,
  `evtrepsat` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`eventid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I also have a very small table of numbers from 0 to 62 which can be used for many different things, but in the query of looking up events, it is used via MOD(num,7) as the day of the week. Here is that query:

SELECT date, evttitle, evttimestart, evttimeend
FROM (                  //create result of all days between the span of two dates
  SELECT DATE_ADD(startdate, INTERVAL (num-startday) DAY) AS date,
    IF(MOD(num,7)=0,7,MOD(num,7)) AS weekday                      //1=sun...7=sat
  FROM (
    SELECT '@startdate' AS startdate, DAYOFWEEK('@startdate') AS startday,
      '@enddate' AS enddate, DATEDIFF('@enddate','@startdate') AS diff
  ) AS span, numbers                                            //numbers is 0-62
  WHERE num>=startday AND num<=startday+diff
) AS daysinspan, events
WHERE evtdatestart<=date AND evtdateend>=date AND (
  (evtdatestart=evtdateend) OR     //single event
  (evtrepdaily) OR                 //daily event
  (evtrepweekly AND (              //weekly event
    (weekday=1 AND evtrepsun) OR   ////on Sunday
    (weekday=2 AND evtrepmon) OR   ////on Monday
    (weekday=3 AND evtreptue) OR   ////on Tuesday
    (weekday=3 AND evtrepwed) OR   ////on Wednesday
    (weekday=3 AND evtrepthu) OR   ////on Thursday
    (weekday=3 AND evtrepfri) OR   ////on Friday
    (weekday=3 AND evtrepsat)      ////on Saturday
  ))       //end of repeat truths
)
ORDER BY date, evtstarttime;

I like this way mostly because it is a pure SQL way of generating the results, it saves me from having to duplicate the event 50+ times for repeating events, and it makes it easier to modify the repeating events. However, it is unacceptable for performance to be slow as this is likely the most common query performed by users.

So another way would be to not include the evtrep columns and simply recreate a new, slightly different, event as many times is needed for the span. But I don't like this idea as the thought of duplicating that much data makes me cringe. However, if it will guarentee me significantly faster results (and clearly the lookup query would be much easier and faster), then I guess it can justify the extra storage.

Which do you all think to be the better plan? Or is there another one that I have not thought of/mentioned here?

Thanks in advance!

Update 1: person-b made a good suggestion. I believe that person-b is suggesting that my table may not be in first normal form which could be true assuming that many (more than ~30%) of the events are non-repeating (in my case, however, 80%+ of the events will likely be repeating events). However, I think my question is due for a restating, as, in his first update, person-b's suggested change or adding a reptimes would simply push off the date processing to the back end (which in my case is PHP), whose processing time I still have to account for. But my main concern (question) is this: what is the fastest way on an average per query basis to compute the dates and times of every event without manually creating x number of entries for repeating events?

Answer 1

Try normalising your tables - you would separate the event information from the (multiple) date and recurrence information.

Update: Here's an example:
Table 1:

CREATE TABLE `events` (
  `eventid` int(10) NOT NULL AUTO_INCREMENT,    //primary key
  `repeatid` int(10) NOT NULL,
  `evttitle` varchar(255) NOT NULL,             //title of event
  `createdby` char(8) NOT NULL,                 //user identification (I'm using
  `evtdatestart` date NOT NULL,                 ////another's login system)
  `evtdateend` date NOT NULL,
  `evttimestart` time NOT NULL,
  `evttimeend` time NOT NULL,
  PRIMARY KEY (`eventid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table 2:

CREATE TABLE `repeats` (
  `repweek` tinyint(1) NOT NULL DEFAULT 0, // if 0, don't repeat, otherwise, the day 1..7 of the week
  `repday` tinyint(1) NOT NULL DEFAULT 0,   // repeat daily if 1
  `reptimes` int(10) NOT NULL DEFAULT 0, // 0 for indefinite, otherwise number of times
)

Then, use a query like this (untested):

SELECT e.evttitle, r.reptimes FROM events e, repeats r WHERE e.eventid = 9

More information (both from the same guide) on Simple joins and Normalisation.

This will make your system more flexible, and hopefully faster too.