> > Design for understanding, logic and maintenance, not performance.
> > If you need more performance, throw more hardware at it -
> > a larger cache (settings -> memory), faster disks and a faster CPU.
> Sorry, but I can't agree with you. Years ago I had to put the DMOZ
> (http://www.dmoz.org/) database (2 million records, 100,000 or so
> at the time) in a MySQL database. Next we had to calculate the number of
> sites in a certain category. The 'path' to the category was known, but a
> regexp was needed to select the path of one level up. The query took > 30
> After adding a column for "one level up", adding indexes, optimizing the
> query it took only a few hundreds of seconds.
Of course, indices should be added to get acceptable performance.
That's what they are here for.
Nevertheless, your database design should be based on logic
and all data should be stored normalized. If you're de-normalizing
your design to get better performance, then there's something
wrong with the database engine (whatever engine that may be).
A design should be logical. This doesn't mean that there's just
one particular design to cover one particular problem, taking
a different your while designing could lead to a different end-result
design (although according to the normalization rules, it shouldn't).
> I really don't know how much hardware you would like to use to get these
Lots of ;-)
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL