List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 11 1999 9:10pm
Subject:Re: SELECT from several tables
View as plain text  
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
Thread
SELECT from several tablesTim Groove11 Sep
  • Re: SELECT from several tablesMartin Ramsch11 Sep
  • search engined e l   d h a n o a12 Sep
    • Re: search engineBenjamin David Hildred12 Sep
    • Re: search engineAlexander I. Barkov12 Sep
Re: SELECT from several tablesTim Groove12 Sep
  • Re: SELECT from several tablesMartin Ramsch12 Sep
    • Re: SELECT from several tablesPaul DuBois12 Sep