MySQL Forums
Forum List  »  Optimizer & Parser

Re: Substring ignores index
Posted by: Toa Sty
Date: September 29, 2006 09:48AM

Hi Matt,

Unfortunately it's not a bug. MySQL doesn't support function indexes, and isn't smart enough to know that the result of a substring() starting from the first character is usable for index lookup.

In your query you'd still probably see a table scan of one table even if your WHERE were:
"on a.dodaac = b.don"
but MySQL may choose to scan the 'a' table rather than the 'b' table in this case, depending on the tables' sizes.

(BTW the explain plan shows that it's scanning the table b, performing the substring for each row in turn, then using this substring value to index lookup on table a using the DODAAC index, so it is using an index to some extent, it just can't use the index on b.don)

So... depending on data sizes you might find it's better to create a temp table with the substringed values from b,don, index it, then join against it. This still won't be a particularly fast process though.


email: 'toasty'*3 at gmail

Options: ReplyQuote

Written By
September 29, 2006 08:59AM
Re: Substring ignores index
September 29, 2006 09:48AM
February 09, 2007 04:01AM

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.