I am currently working on an application where I have a very large
table called intervals ( atm its 80 000 000 records and growing ), and
a smaller table ( token ) which join with it.
interval is just an id, start, end, word
token is id, interval_id, type, processed_by
There is a many to one......ie one interval can have many tokens. The
idea being that I as used different tools I generate different tokens
but the underlying intervals tend to be the same. When I add a new
token I first need to search the intervals table to see if one exists
in there. Of course theres an index on intervals to deal with this.
But as I add more and more new fields I notice a massive slow down in
processing. I think this due to the increase in new records being
added and indexed. The problem is I can't turn indexes off as I have
80 million records.
Does anyone have any suggestions for optimising this design? Or where
to start from? One option and at the moment the only option I have is
to denormalise my schema but this will complicate stuff at the
application level considerably.
Any suggestions welcome,