On May 13, 9:42 am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> ZeldorBlat wrote:
> > On May 12, 3:53 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
> >> ZeldorBlat wrote:
> >>> On May 12, 3:44 pm, genzeryk <genze...@gmail.com> wrote:
> >>>> Hi Folks!
>
> >>>> I'm a newbie MySQL programmer. While doing my project I have faced
> >>>> following problem:
>
> >>>> I have five columns in my table (indexed 'id' and four with
> >>>> numerical values). I would like to sum the numerical values into a
> >>>> kind of virtual column and sort the table accordingly to the
> >>>> decreasing values of the sum (of the virual column).
>
> >>>> I have searched in numerous tutorials and FAQs but have not found
> >>>> description of even similar situation.
>
> >>>> Please help
>
> >>>> Best regards,
>
> >>>> Genz
>
> >>> select id, (a + b + c + d) theSum
> >>> from someTable
> >>> order by (a + b + c + d) desc
>
> >> That seems needlessly wasted on resources, by summing the columns
> >> twice. Surely either of these 2 would be better:
>
> >> SELECT
> >> `id`,
> >> (`a` + `b` + `c` + `d`) `theSum`
> >> FROM `someTable`
> >> ORDER BY `theSum`
>
> >> or
>
> >> SELECT
> >> `id`,
> >> (`a` + `b` + `c` + `d`) `theSum`
> >> FROM `someTable`
> >> ORDER BY 2
>
> > Do you know for a fact that MySQL will internally calculate the sums
> > twice? If the database engine is properly optimized I would guess
> > that it won't.
>
> Yes I do know that for a fact. No matter how well the database engine is
> likely to be optimised, it is not going to bother tryjng to recognise that
> two distinct pieces of calculation happen to have the same fields. That is
> why you are able to refer to previously calculated fields. Optimisers assume
> some level of intelligence from the programmer.

I was unable to find that documented anywhere. Do you have a source?
I'm curious now...