---
title: Finding the Busiest Day of the Year
---

## Finding the Busiest Day of the Year

In this query, we are finding the day of the year that had the highest number of bike trips:

```
MySQL [bikeshare]> SELECT COUNT(*) c, DATE(start_date) as day_of_year FROM trips GROUP BY day_of_year ORDER BY c DESC LIMIT 10;
+-------+-------------+
| c     | day_of_year |
+-------+-------------+
| 16896 | 2017-04-15  |
| 16346 | 2017-08-05  |
| 16191 | 2017-03-25  |
| 16038 | 2017-04-14  |
| 15767 | 2017-07-30  |
| 15549 | 2017-07-26  |
| 15517 | 2017-07-08  |
| 15477 | 2017-10-07  |
| 15434 | 2017-04-13  |
| 15403 | 2017-07-04  |
+-------+-------------+
10 rows in set (4.73 sec)
```

In `EXPLAIN`:

```
MySQL [bikeshare]> EXPLAIN SELECT COUNT(*) c, DATE(start_date) as day_of_year FROM trips GROUP BY day_of_year ORDER BY c DESC LIMIT 10;
+--------------------------+------------+------+-------------------------------------------------------------------------------------------------+
| id                       | count      | task | operator info                                                                                   |
+--------------------------+------------+------+-------------------------------------------------------------------------------------------------+
| Projection_7             | 10.00      | root | 2_col_0, date(bikeshare.trips.start_date)                                                       |
| └─TopN_10                | 10.00      | root | 2_col_0:desc, offset:0, count:10                                                                |
|   └─HashAgg_14           | 3390469.43 | root | group by:date(bikeshare.trips.start_date), funcs:count(1), firstrow(bikeshare.trips.start_date) |
|     └─TableReader_18     | 3757777.00 | root | data:TableScan_17                                                                               |
|       └─TableScan_17     | 3757777.00 | cop  | table:trips, range:[-inf,+inf], keep order:false                                                |
+--------------------------+------------+------+-------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
```

So lets start by describing what's happening:

* We have a tablescan again, because there is no WHERE clause or ability to filter rows.

* The tablescan operation feeds into TiDB (TableReader), where rows are in parallel inserted into a hash table to produce the grouping (summing the number of rows per date).  This is a bit different to the previous query, because the coprocessor is not able to be used.  While the date function is supported by the coprocessor, because each region is organized by a key range.....

* A top-N is then performed to retrieve the Top 10 rows.

This query is already quite optimized, the only commentary I have is that in the version of TiKV I am using, the coprocessor is unable to handle the `DATE` function.

Add index on start_date:

```
ALTER TABLE trips ADD INDEX s (start_date);
EXPLAIN SELECT COUNT(*) c, DATE(start_date) as day_of_year FROM trips GROUP BY day_of_year ORDER BY c DESC LIMIT 10;
```

Here we can see X.  The advantage of the index is two fold:
- Coprocessor
- Smaller scan area.




## Other info 

Our first example analytics query is to find out what are the busiest days of the year to rent a bike:


If you are coming from a MySQL background, there are some features here which you will not be familiar with.  Lets explain what's happening here:

* A tablescan operation is reading each TiKV node to find rows that match.  There is no where clause, so every row matches: it's expected that the range be -inf to +inf.

* The tablescan operation feeds into TiDB (TableReader), where rows are in parallel inserted into a hash table to produce the grouping (summing the number of rows per date).

* A top-N is then performed to retrieve the Top 10 rows.

This query is already quite optimized, the only commentary I have is that in the version of TiKV I am using, the coprocessor is unable to handle the `DATE` function.


* Which day of the week is the busiest for trips
* Which month is the busiest.
* What is the average number of trips per bike each day.