On May 15, 6:57 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> 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 pagehttp://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.

Ok, but nowhere does that say that the expression will or will not be
internally evaluated a second time. If the database is smart enough
to recognize when you have an expression in the group by clause that
is not in the select list (in which case it complains), I would expect
that it is smart enough to match an expression in the select list to
an expression in the order by clause.