At 4:02 PM -0500 9/11/99, Martin Ramsch wrote:
>- And a maybe crazy idea: Because I assume, that updating the indexes
> is the main bottleneck, you maybe could go with two identical
> tables, one with only an index on an auto_increment field id, the
> other version fully indexed. And then every five or ten minutes,
> you call
> INSERT INTO indexedtable
> SELECT *
> FROM nonindexedtable
> WHERE id > lastremeberedid;
> This "batch" insert should be faster than 1000s of single inserts.
> So (in my theory) you should have the fasted inserts possible
> on the input side (nonindexed table) and fast indexed selects
> on the output side, with the only drawback, that the latter data
> only is updated every some minutes.
That's not such a crazy idea. It's a good idea under certain circumstances.
One drawback is that it doesn't apply if you absolutely must have new
records available immediately as soon as they've been entered into the
database. If you can afford to have them be "dormant" for a while, it
may be a good solution. You will get better performance by "batching"
the inserts this way than by doing a bunch of individual inserts.
Paul DuBois, paul@stripped