MySQL Forums
Forum List  »  Newbie

Re: Use of SUBSTR with an index
Posted by: David Wynter
Date: July 22, 2005 07:02AM

Cannot find anything in the User Manual that indicates why MySQL does not use the index I have on the 1st 20 char of the EVENT_DESCRIPTION column. Any takers?

I changed the query in the second posting here slightly

SELECT * FROM CORAX_EVENT, CORAX_CAPITAL_CHANGE
WHERE PROCESSED=1 AND
CAP_CHNG_MARKET_LVL_ID = CAP_CHNG_MARKET_LEVEL_ID
AND (LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 8)) IN ('buy-back', 'buy back', 'buyback ')
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 14)) IN ('share buy back', 'share buyback ', 'share buy-back')
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 16)) = 'share repurchase'
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 10)) = 'repurchase')
AND SUBSTR(EVENT_DESCRIPTION, 0, 2) NOT IN ('1)' , '(1' )

This was after reading how the index would not be used with a leading wildcard %

David

Options: ReplyQuote


Subject
Written By
Posted
Re: Use of SUBSTR with an index
July 22, 2005 07:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.