On Friday 04 October 2002 11:04, Paul DuBois wrote:
> At 10:43 +0800 10/4/02, Niclas Hedhman wrote:
> >If I have a table,
> >
> >Name VARCHAR(100)
> >L DOUBLE
> >a DOUBLE
> >b DOUBLE
> >
> >and execute the incredible
> >
> >SELECT * FROM Colors WHERE
> >SQRT(
> > SQ(
> > ABS( L - 45.5 )
> > ) +
> > SQ(
> > ABS( a - 13.2 )
> > ) +
> > SQ(
> > ABS( b + 23.4 )
> > )
> >) < 2.5 ;
> >
> >Could MySQL benefit in any way by indexing the L,a,b fields? Or will it
> > just plough through all the records one by one, anyway?
>
> It has to read each row in order to evaluate the expression. An index
> will do you no good.
>
> How could it be otherwise?
Well, in "WHERE L < 2.5" an index can help, which is equal to "WHERE L - 2.5 <
0", so how much "formula reversion" does it manage? None, some or a lot?
SQRT( a + b + c ) < 2.5
a + b + c < 6.25
SQ( a' ) + SQ( b' ) + SQ( c' ) < 6.25
is only true if and only if
-2.5 < a' < 2.5, and
-2.5 < b' < 2.5, and
-2.5 < c' < 2.5
-2.5 < ABS( a" ) < 2.5, means that a" must be
0 < a" < 2.5, and so on....
I can apply logic, since I am a thinking being, but I was wondering if MySQL
was smart enough to do this in a generic formula reversal/optimization thing.
I realize that I probably have to resort to custom code hooked into MySQL to
do what I want in a really optimized way, but...
Niclas
