List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:July 9 2004 1:26pm
Subject:Re: Cost of joins?
View as plain text  
On Fri, 9 Jul 2004 14:01:46 +0100, alec.cawley@stripped
<alec.cawley@stripped> wrote:
> 
> 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?

Sure. In PostgreSQL I would do:
CREATE INDEX the_idx ON table (secondary start + primary finish -
primary start);


> Or is this just a defect in MySQL?

The absence of an optimization is the absence of an optimization, not a defect.

Jochem
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