Benjamin,
>
> In order to avoid the re-indexing I was thinking of instead creating a
> new
> table each month (building its indexes and etc) and accessing them all
> through a view. This way I only have to index the new data each month.
>
Take a look at bizgres.org (based on postgres).
They have a parent-child structure.
The way i use it, is I have about 30M records a day that are inserted into
the database.
Each day is a "child" table to the "parent".
so example:
the parent table is called "logfile"
each day, is a child, with the structure like "logfile_YYMMDD"
the "child" inherits the table structure of the parent, such that you could
query the child table name directly, or you run the query against the parent
(ie. logfile table) and get all the data.
the indexes are done on a per table basis, so new data that comes in, is a
lesser amount, and doesn't require re-indexing.
example:
select * from logfile_070825 where datafield = 'foo'
if i knew i wanted to specifically go into that child, or:
select * from logfile where datafield = 'foo'
and all child tables are searched and results merged. You can perform any
kind of sql query and field structures are you normally do.
the downside is that the queries are run sequentially.
so if you had 100 child tables, each table is queried via indexes, then
results are merged.
but, this approach does allow me to dump alot of data in, without having the
re-indexing issues you are facing.
at some point, you could roll up the days, in to weekly child tables, then
monthly tables, etc.
I believe Bizgres has a new version of their system that does parallel
queries which would certainly speed things up.
For your documents, you can do it by the day it was checked in, or maybe you
have some other way of logically grouping, but the parent/child table
structure really helped to solve my problem of adding in millions of records
each day.
The closest thing in mysql is using merge tables, which is not really
practical when it comes time to do the joins to the tables.
-brandon
http://www.t3report.com - marketing intelligence for online marketing and
affiliate programs