I have a big table, with important data , has foreign keys . The problem is it's getting bigger . Not having any issues with speed yet search is optimised very well. It grows every day. But the efficient data in table is data for last 7 days . After it data can become historical and be used for reports . But it can't be removed cause has a lot of relations with other tables . I tried to partition it with some columns but have limitation cause it has foreign keys . I fill that soon I will get in trouble . I need somehow make mysql to look only for last 7 days data. I can set some date ranges but it will not help still index table will become huge too it will be cached and I will have to upgrade hardware.
Asked
Active
Viewed 315 times
0
-
How many days are in the table now? How many records per day? – Thorsten Kettner Oct 09 '17 at 21:34
-
50k per day,6m now @Thorsten Kettner – Vahan Oct 09 '17 at 21:37
-
Okay, so it's big, but not one of those huge tables that are so extremely hard to handle. A day is less then 1% of the table and that percentage will get smaller and smaller. I'd say don't mind the size, have an index on date descending, maybe with one or two additional columns that are typical for where clauses and you shouldn't run into problems. Here is a thread about MySQL and large tables, you might want to read: https://stackoverflow.com/questions/39700330/handling-very-large-data-with-mysql – Thorsten Kettner Oct 09 '17 at 21:48