List:General Discussion« Previous MessageNext Message »
From:Nathan Harmston Date:February 19 2010 10:47pm
Subject:Optimising a very large table
View as plain text  
Hi everyone,

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,

Nathan
Thread
Optimising a very large tableNathan Harmston19 Feb
  • Re: Optimising a very large tablewalter harms20 Feb