Re: Use of SUBSTR with an index
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