generated_column.sql

/*
'year' and 'month' columns are in seperate integer columns.
This creates an issue when wanting reports between two different dates.
How can we fix this? One solution, a generated column with an index on it. 
*/
MBP@landregistry> select * from summary order by total_price desc limit 3;
+----------+------+-------+------------+----------------+--------------+-------------+
| postcode | year | month | county     | district       | total_price  | houses_sold |
+----------+------+-------+------------+----------------+--------------+-------------+
| W1T      | 2015 |     7 | CAMDEN     | GREATER LONDON | 367075000.00 |           7 |
| SS14     | 2014 |     1 | BASILDON   | ESSEX          | 356644505.00 |          27 |
| UB3      | 2013 |    12 | HILLINGDON | GREATER LONDON | 348528830.00 |          39 |
+----------+------+-------+------------+----------------+--------------+-------------+
3 rows in set (0.46 sec)

-- Develop a function to join data from the two columns and convert into a date
MBP@landregistry> select date(concat_ws('-',year,month,'01')) from summary limit 3;
+--------------------------------------+
| date(concat_ws('-',year,month,'01')) |
+--------------------------------------+
| 1995-01-01                           |
| 1995-01-01                           |
| 1995-01-01                           |
+--------------------------------------+
3 rows in set (0.01 sec)

-- Use the function you developed and ass it as a generated column to the table
MBP@landregistry> alter table summary add vdate date GENERATED ALWAYS 
AS (date(concat_ws('-',year,month,'01'))) STORED;
Query OK, 890950 rows affected (4.25 sec)
Records: 890950  Duplicates: 0  Warnings: 0

-- Add an index on that column (could have been done in the previous statement as well)
MBP@landregistry> alter table summary add index vdate(vdate);
Query OK, 0 rows affected (1.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- Test to see if it works.
MBP@landregistry> select sum(houses_sold) from summary 
where vdate between '2010-01-01' and '2010-12-01';
+------------------+
| sum(houses_sold) |
+------------------+
|           662817 |
+------------------+
1 row in set (0.26 sec)