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.
