MySql calendar table and performances

Question

for a project i'm working on, i have a single table with two dates meaning a range of dates and i needed a way to "multiply" my rows for every day in between the two dates.

So for instance i have start 2017-07-10, end 2017-07-14 I needed to have 4 lines with 2017-07-10, 2017-07-11, 2017-07-12, 2017-07-13

In order to do this i found here someone mentioning using a "calendar table" with all the dates for years.

So i built it, now i have these two simple tables:

CREATE TABLE `time_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` varchar(16) DEFAULT NULL,
  `end` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`societa_id`),
  KEY `start_idx` (`start`),
  KEY `end_idx` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=222 DEFAULT CHARSET=latin1;

This table contains my date ranges, start and end are indexed, the primary key is an incremental int. Sample Row:

id  start   end
1   2015-05-13  2015-05-18

Second table:

CREATE TABLE `time_dimension` (
  `id` int(11) NOT NULL,
  `db_date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `td_dbdate_idx` (`db_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This has a date indexed for every day for many years to come. Sample row:

id  db_date
20120101    2012-01-01

Now, i made the join:

select * from time_sample s join time_dimension t on (t.db_date >= start and t.db_date < end);

This takes 3ms. Even if my first table is HUGE, this query will always be very quick (max i've seen was 50ms with a lot of records).

The issue i have is while grouping results (i need them grouped for my application):

select * from time_sample s join time_dimension t on (t.db_date >= start and t.db_date < end) group by db_date;

This takes more than one second with not so many rows in the first table, increasing dramatically. Why is this happening and how can i avoid this?

Changing the data types doesn't help, having the second table with just one column doesn't help.

Can i have suggestions, please :(

Answer 1

I cannot replicate this result...

I have a calendar table with lots of dates: calendar(dt) where dt is a PRIMARY KEY DATE data type.

DROP TABLE IF EXISTS time_sample;

CREATE TABLE time_sample (
  id int(11) NOT NULL AUTO_INCREMENT,
  start date not NULL,
  end date null,
  PRIMARY KEY (id),
  KEY (start,end)
);

INSERT INTO time_sample (start,end) VALUES  ('2010-03-13','2010-05-09);

SELECT * 
  FROM calendar x 
  JOIN time_sample y 
    ON x.dt BETWEEN y.start AND y.end;
+------------+----+------------+------------+
| dt         | id | start      | end        |
+------------+----+------------+------------+
| 2010-03-13 |  1 | 2010-03-13 | 2010-05-09 |
| 2010-03-14 |  1 | 2010-03-13 | 2010-05-09 |
| 2010-03-15 |  1 | 2010-03-13 | 2010-05-09 |
| 2010-03-16 |  1 | 2010-03-13 | 2010-05-09 |
...
| 2010-05-09 |  1 | 2010-03-13 | 2010-05-09 |
+------------+----+------------+------------+
58 rows in set (0.10 sec)

EXPLAIN
SELECT * FROM calendar x JOIN time_sample y ON x.dt BETWEEN y.start AND y.end;
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | y     | system | start         | NULL    | NULL    | NULL |    1 |                          |
|  1 | SIMPLE      | x     | range  | PRIMARY       | PRIMARY | 3       | NULL |   57 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)

Even with a GROUP BY, I'm struggling to reproduce the problem. Here's a simple COUNT...

SELECT SQL_NO_CACHE dt, COUNT(1) FROM calendar x JOIN time_sample y WHERE x.dt BETWEEN y.start AND y.end GROUP BY dt ORDER BY COUNT(1) DESC LIMIT 3;
+------------+----------+
| dt         | COUNT(1) |
+------------+----------+
| 2010-04-03 |        2 |
| 2010-05-05 |        2 |
| 2010-03-13 |        2 |
+------------+----------+
3 rows in set (0.36 sec)

EXPLAIN
SELECT SQL_NO_CACHE dt, COUNT(1) FROM calendar x JOIN time_sample y WHERE x.dt BETWEEN y.start AND y.end GROUP BY dt ORDER BY COUNT(1) DESC LIMIT 3;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | y     | index | start         | start   | 7       | NULL |       2 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | x     | index | PRIMARY       | PRIMARY | 3       | NULL | 1000001 | Using where; Using index                     |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------------------------------------------+