List:General Discussion« Previous MessageNext Message »
From:Niclas Hedhman Date:October 4 2002 5:44am
Subject:Re: WHERE clause efficiency
View as plain text  
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
Thread
WHERE clause efficiencyNiclas Hedhman4 Oct
  • Re: WHERE clause efficiencyPaul DuBois4 Oct
    • Re: WHERE clause efficiencyNiclas Hedhman4 Oct