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
