what if we use the function at the right side of the equation? such as select anything from table1, table2 where table1.id=left(table2.id,somenumber)
having both id in table were indexed dont you think the index in table1 would still be used.. :) cmiiw -leo- ----- Original Message ----- From: Erik Osterman To: [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:17 AM Subject: RE: Aliases > From: Matt W [mailto:[EMAIL PROTECTED] > Sent: Monday, November 10, 2003 5:47 PM > To: Erik Osterman; [EMAIL PROTECTED] > No, Roger's method can't use an index. :-) But yes, using WHERE is > better than HAVING. Ah... right indeed. In this case it wouldn't work. My mistake... :) > To get the WHERE to use an index, don't use a function in the > comparison: Though you can use functions on indexed columns so long as you (generally) aren't using table columns which lie in your domain. E.g. FROM_UNIXTIME(1068520546) or NOW() will use indexes, but FROM_UNIXTIME(col) will not -- since col is in your input domain. Unless we're talking about MIN/MAX functions and those are an exception! So for clarification, David, those functions that do operate on indexed columns will only work in WHERE clauses and not work in HAVING clauses. Thanks for the correction, Erik Osterman http://osterman.com/
