List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:July 9 2004 12:55pm
Subject:Re: Cost of joins?
View as plain text  
> > 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
categories
> 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
> seconds.
> 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
> results?

Lots of ;-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

Thread
Cost of joins?Margaret MacDonald9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
    • RE: Cost of joins?Lachlan Mulcahy12 Jul
      • Re: Cost of joins?Martijn Tonies12 Jul
      • Re: query gets count wrongGerald Taylor12 Jul
  • Re: Cost of joins?Alec.Cawley9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
    • Re: Cost of joins?Jochem van Dieten9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
    • Re: Cost of joins?Alec.Cawley9 Jul
      • Re: Cost of joins?Jochem van Dieten9 Jul
    • Re: Cost of joins?(Michael Johnson)9 Jul
  • Re: Cost of joins?Jigal van Hemert9 Jul
  • Re: Cost of joins?Martijn Tonies9 Jul
RE: Cost of joins?Bob)9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: Cost of joins?Martijn Tonies9 Jul
Re: query gets count wrongSGreen12 Jul