Re: Cost of joins?

[Jochem van Dieten]

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