List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:July 9 2004 1:10pm
Subject:Re: Cost of joins?
View as plain text  
Alec,

> > 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).
>
> Nearly always, but not absolutely always. I have a table with columns
>         primary start
>         primary finish
>         secondary start
>         secondary finish
>
> Since it is defined that the distance between start and finish is the same
> for both primary and secondary, this is denormalised, since secondary
> finish is always given by secondary start + (primary finish - primary
> start). However, I want to use all four fields as indexes, including
> secondary finish. Does any database allow indexes on complex functions of
> columns rather than on columns themselves? Or is this just a defect in
> MySQL?

Indices, by themselves, are a-logical. They're physical. They
exist because of performance problems. A JOIN is logical and
doesn't have anything to do with indices.

The question is not:
should I avoid joins to gain performance?

The question is:
should I bother the vendor to increase performance despite
my logical joins?

The answer is: Yes, you should.

If the vendor answer is: add a (funtion) index, and if that helps,
then it's fine. If it doesn't, then the vendor should think of another
trick so that we designers can use their product (database
engine) the way it's supposed to be used.

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