Well, that is correct although it would still need to iterate over all rows in table2.
Given the nature of the original problem, we're dealing with a single table in which case it would always need to iterate over all rows of the table. Indeed another good clarification! A more accurate generalization: Indexes may be used where a function's input domain does not reside in the table itself. Unfortunately for David, he's still SOL. Let's see, what else am I neglecting? =) Regards, Erik Osterman http://osterman.com/ -----Original Message----- From: Leo [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2003 7:34 PM To: [EMAIL PROTECTED] Subject: Re: Aliases 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
