On Sunday 06 Oct 2002 10:39 am, Tom Cross wrote:
> Much to my horror I got an error message while attempting to back up my
> database tonight:
[snipped discussion of a design with many thousands of tables versus one large
table with many millions of rows]
> Am I barking up the wrong tree here with all these dynamically generated
> tables? Is it really more reasonable to search a table with 10 million
> entries for the 1000 I want every time a user wants to read something? Are
> there other pitfalls that I need to be aware of? Is there a key book that
> discusses this sort of thing that I could have on my desk?
Given the number of hoops you're trying to jump through (to keep the dog
metaphor) I'd say you are barking up the wrong tree.
Databases are designed to handle large tables and retrieve information
efficiently if indexed correctly. Try adding a "topic_id" column and indexing
it - generate a million test messages and see how it goes. A useful tuning
might be to gradually increase the "sort_mem" value in your config file.
In general, one table for each type of thing in your database is the rule.
- Richard Huxton