MySQL Forums
Forum List  »  Newbie

Use of SUBSTR with an index
Posted by: David Wynter
Date: July 20, 2005 11:02AM

I need to run an update that restricts on the first 20 characters of a TEXT field. I added a INDEX ont he fiirst 20 character sof the text field but it does not get used.

The query in select form looks like this

SELECT * FROM CORAX_EVENT, CORAX_CAPITAL_CHANGE
WHERE PROCESSED=1 AND
CORAX_EVENT.CAP_CHNG_MARKET_LVL_ID = CORAX_CAPITAL_CHANGE.CAP_CHNG_MARKET_LEVEL_ID
AND (LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'buyback%' OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'share buyback%'
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'buy-back%' OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'buy back%'
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'share buy-back%' OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'share buy back%'
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'share repurchase%' OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'repurchase%')
AND LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) NOT LIKE '1)%'

Ideally the final part of the restriction clause would look like this NOT LIKE '%1)%' but I was concerned it would not use the index if a leading wildcard was used. This was needless as it does not use the new index anyway. There is an index on CAP_CHNG_MARKET_LVL_ID which is utilised and CAP_CHNG_MARKET_LEVEL_ID is the 2nd column in the primary key index of CORAX_CAPITAL_CHANGE which is not utilised.

Suggestion on getting the index involved?

Thx,

David

Options: ReplyQuote


Subject
Written By
Posted
Use of SUBSTR with an index
July 20, 2005 11: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.