I have a table messages that contains data as shown below:
Id Name Insert_Date Other_Columns
-------------------------------------
1 A 20160101 A_data_1
2 A 20160202 A_data_2
3 A 20160401 A_data_3
4 B 20160111 B_data_1
5 B 20160121 B_data_2
6 C 20160301 C_data_1
this table has 2 index:
index_1 : (Name)
index_2 : (Name, Insert_Date)
User can input a date (example: 20160225) and I want to search the last records which near the input date most, in each group.
Example: user input 20160225, then the result is:
Name Insert_Date Other_Columns
---------------------------------
A 20160201 A_data_3
B 20160121 B_data_2
C 20160301 C_data_1
I used the solution in this theard:
http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group
However, It still not good because my table have about 16 million records (It's very big), And In the case there is not any record has Insert_Date < input date then It take many time.
I'm really heard about OLAP for searching dimension table but I don't have knowledge about it. There is any other solution ?