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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
