WHERE clause efficiency

2002-10-03 Thread Niclas Hedhman 


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?

Niclas

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: WHERE clause efficiency

2002-10-03 Thread Paul DuBois 

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?


Niclas


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: WHERE clause efficiency

2002-10-03 Thread Niclas Hedhman 

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

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php