Hi Tim,
Thanks for the reply. Good guess, but no dice. The table structure is
as follows:
CREATE TABLE `f_trans_lines` (
`TLINE_SEQ` int(11) default NULL,
`TLINE_TRANS_KEY` int(11) default NULL,
`TLINE_WCODE` varchar(8) default NULL,
`TLINE_ACCOUNT` varchar(8) default NULL,
`TLINE_UNITS` float default NULL,
`TLINE_TEETH` varchar(22) default NULL,
`TLINE_UNIT_COST` float default NULL,
`TLINE_DISCOUNT` float default NULL,
`TLINE_SELL_PRICE` float default NULL,
`TLINE_TAX` varchar(22) default NULL,
`TLINE_TNC` int(11) default NULL,
`TLINE_ENTRY_DATE` date default NULL,
`TLINE_INV_DATE` date default NULL,
`TLINE_TMT_DATE` date default NULL,
`TLINE_TYPE` int(11) default NULL,
`TLINE_DEPT` varchar(12) default NULL,
`TLINE_GROUP` varchar(12) default NULL,
`TLINE_SALES_PERSON` varchar(3) default NULL,
`TLINE_DR_GROUP` varchar(4) default NULL,
`TLINE_PRIMARY_KEY` float NOT NULL default '0',
PRIMARY KEY (`TLINE_PRIMARY_KEY`),
KEY `Index_1` (`TLINE_WCODE`),
KEY `Index_2` (`TLINE_ACCOUNT`),
KEY `Index_3` (`TLINE_SALES_PERSON`),
KEY `Index_4` (`TLINE_INV_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
I tried wrapping the query up as you recommended:
SELECT
TLINE_WCODE,
SUM(TLINE_UNITS)
FROM
f_trans_lines
WHERE
TLINE_INV_DATE
BETWEEN
STR_TO_DATE(DATE_FORMAT(SYSDATE(), '%Y-%m-01'), '%Y-%m-%d')
AND
LAST_DAY(SYSDATE())
GROUP BY
TLINE_WCODE
But, it still insists to use Index_1 instead of Index_4
Any other ideas?
Thanks!
Mike
-----Original Message-----
From: Little, Timothy [mailto:[EMAIL PROTECTED]
Sent: March 12, 2007 3:38 PM
To: Caplan, Michael; mysql@lists.mysql.com
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.
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]
