CODEWITHSUNDEEP
mysqldatabase-designcalendar
Gary Olsson
Gary Olsson

Reputation: 1217

Table structure for calendar - Store data based on date

I have a calendar used to store informations based on a date (not a datetime) for any user.

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

+----+---------+------------+-----------+
| id | user_id | event_date | data      |
+----+---------+------------+-----------+
|  1 |      25 | 2015-08-25 | Some Data |
+----+---------+------------+-----------+

In reality, the column data doesn't exist, there's multiple boolean columns instead but it's simplier this way.
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.
For now, I'm just running the following query.

SELECT `data` FROM `calendar` WHERE `event_date` = '2015-07-08'

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).
I'm already removing data older than a year but since the number of users is increasing, so do my table.

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:

For Every Monday & Saturday From [start_date] to [end_date], set data="Some Value".

Because of this, I was wondering if using a table structure to store recurring events wasn't better than current table. I've seen this answer (and other similar) which propose the following structure

Assuming I have two tables, one called events like this:

ID    NAME
1     Sample Event
2     Another Event

And a table called events_meta like this:

ID    event_id      meta_key           meta_value
1     1             repeat_start       1299132000
2     1             repeat_interval_1  432000

But this structure doesn't seems to fit my need :

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.

PS : I already have an INDEX on my event_date column.

Here is the explain of the query & the result of SHOW CREATE TABLE

+----+-------------+----------+------+---------------+------------+---------+-------+--------+-------+
| 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`)
)

Upvotes: 0

Views: 2933

Answers (1)

Rick James
Rick James

Reputation: 142208

There is no improvement.

You have INDEX(event_date). The real 'problem' is that there are about 127K rows for the event_date used in that EXPLAIN. It takes a long time to fetch that many rows from the disk.

OK, there may be a way to improve this query -- but it might be at the expense of other queries. In order to know what (and whether) to make the suggestion, please provide

  • SHOW CREATE TABLE
  • The other important SELECTs.
  • How many rows for a typical day? How many rows for a typical user?

Do you actually use all 127K rows in your client? Or do you do further filtering? Or consolidation (summing, counting, etc)? Maybe some of that stuff could be moved to the SELECT.

Upvotes: 1

Related Questions