On Fri, Oct 12, 2012 at 7:44 AM, Chitra Creta <chitracreta@gmail.com> wrote:
> Hi,
>
> I currently have a table that is growing very quickly - i.e 7 million
> records in 5 days. This table acts as a placeholder for statistics, and
> hence the records are merely inserted and never updated or deleted.
>
> Many queries are run on this table to obtain trend analysis. However, these
> queries are now starting to take a very long time (hours) to execute due to
> the size of the table.
>
> I have put indexes on this table, to no significant benefit. Some of the
> other strategies I have thought of:
> 1. Purge old data
> 2. Reindex
> 3. Partition
> 4. Creation of daily, monthly, yearly summary tables that contains
> aggregated data specific to the statistics required
>
> Does anyone know what is the best practice to handle this situation?
>
> I would appreciate knowledge sharing on the pros and cons of the above, or
> if there are any other strategies that I could put in place.
Partitioning is prolly your best solution. 3 & 4 sound like
variations on the same thing. Before you go that route, you should
make sure that your bottleneck is really a result of the massive
amount of data, and not some other problem. Are you sure that the
indices you created are being used, and that you have all the indices
that you need for your queries? Look at the query plan output from
EXPLAIN, and/or post here if you're unsure.
Reindexing shouldn't make a difference unless something is wrong with
the indices that you already have in place.
Purging old data is only a good solution if you do not need the data,
and never will need the data.