Hi Brent, That's the ticket! I swapped SYSDATE out for NOW.
Thanks to the two of you!!! Mike -----Original Message----- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: March 12, 2007 3:50 PM To: Little, Timothy; Caplan, Michael; mysql@lists.mysql.com Subject: Re: Function in Query has Terrible Impact on Indexes Used The SYSDATE() function is not evaluated once, it's evaluated for each record. An index can't be used because the value may change at any time. Just as if you were using RAND() instead, it can't be preparsed. You can put the result of SYSDATE() into a variable and then use the variable in the query, then it's a constant and an index can be used. SYSDATE() is not a constant, it returns a changing value. ----- Original Message ----- From: "Little, Timothy" <[EMAIL PROTECTED]> To: "Caplan, Michael" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com> Sent: Monday, March 12, 2007 2:38 PM Subject: RE: Function in Query has Terrible Impact on Indexes Used My guess, without seeing your database CREATE TABLE statement for this particular table would be that the DATE_FORMAT returns a string, while the LAST_DAY function returns a date-time. If your TLINE_INV_DATE is a VAR/CHAR then use DATE_FORMAT around the LAST_DAY. If TLINE_INV_DATE is a DATE/TIME then use a STR_TO_DATE around the Date_Format to see if it makes a difference. Just my humble guess. Tim... -----Original Message----- From: Caplan, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, March 12, 2007 1:12 PM To: mysql@lists.mysql.com Subject: WOW: Function in Query has Terrible Impact on Indexes Used Hi There, I'm having terrible results with a query that uses functions to define dynamically conditions in the WHERE clause. However, if I change the query to not use these functions, the query flies because it uses proper indexes. The query without functions is as follows: SELECT TLINE_WCODE, SUM(TLINE_UNITS) FROM f_trans_lines WHERE TLINE_INV_DATE BETWEEN '2007-03-01' AND '2007-03-31' GROUP BY TLINE_WCODE When describing this query, it says that it is using the index_4 index, which is an index of the TLINE_INV_DATE column. 1, 'SIMPLE', 'f_trans_lines', 'range', 'Index_4', 'Index_4', '4', '', 1, 'Using where; Using temporary; Using filesort' However, here I swap out the statically defined dates for functions: SELECT TLINE_WCODE, SUM(TLINE_UNITS) FROM f_trans_lines WHERE TLINE_INV_DATE BETWEEN DATE_FORMAT(SYSDATE(), '%Y-%m-01') AND LAST_DAY(SYSDATE()) GROUP BY TLINE_WCODE Describe says that it is using a different key (index_1), which is an index of TLINE_WCODE. This execution path offers terrible performance. 1, 'SIMPLE', 'f_trans_lines', 'index', '', 'Index_1', '11', '', 520366, 'Using where' Any ideas why using the functions forces an index change, and how can I fix this so I don't have to give up the flexibility of the functions? Thanks, Mike E-mail messages may contain viruses, worms, or other malicious code. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective action against such code. Henry Schein is not liable for any loss or damage arising from this message. The information in this email is confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this e-mail by anyone else is unauthorized. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] E-mail messages may contain viruses, worms, or other malicious code. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective action against such code. Henry Schein is not liable for any loss or damage arising from this message. The information in this email is confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this e-mail by anyone else is unauthorized. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
