On 13 May, 22:41, ZeldorBlat <zeldorb...@gmail.com> wrote:
> 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...- Hide quoted text -
>
> - Show quoted text -

Sorry for the delay in answering this one. My source is the manual.
On the page http://dev.mysql.com/doc/refman/5.0/en/select.html
Where it says:

"Columns selected for output can be referred to in ORDER BY and GROUP
BY clauses using column names, column aliases, or column positions."

And of course it defines an Order By statement as:

ORDER BY {col_name | expr | position}

It does not say that columns selected for output can be referred to by
using an expression that happens to match the expression that created
the data for that column. Thus, unless you use one of the methods to
refer to the output column, you are left with an expression that will
have to be evaluated.