MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing Substring Query
Posted by: spicedh
Date: May 02, 2006 01:01PM


Apologies if this is the wrong forum - as I'm kind of stuck with how exactly to approach this problem I wasn't sure where to put it.

I have a large (about 1.5 million rows) database of products, with the product name being a text field that always has the pattern "xxxxxxxxxx/yyyyy" where x and y are any alphanumeric characters. The number of x and y's is variable. Basically there is a product identifier which contains a "/" and then some other characters which identify from which manufacturer the given product originates. Hope that makes sense.

At the moment, with an index on this field, searching for an _exact_ product identifier is blazingly fast, all is fine and good. However I would also like to be able to say "search for any product with this suffix" and that's where the problem lies. Doing a SUBSTRING_INDEX query on the whole database to accomplish this obviously takes a long time.

Does anyone have any suggestions for optimizing this case? I would be tempted to have a trigger set to fill in a "manufacturer" column based on a SUBSTRING_INDEX on INSERT - but duplication of any data always makes me uncomfortable! Having said that what alternative is there? Perhaps a VIEW adding a "manufacturer" column might be more sensible? If anyone can tell me what blindingly obvious solution I'm missing then that would be great. :-)


Options: ReplyQuote

Written By
Optimizing Substring Query
May 02, 2006 01:01PM
May 02, 2006 02:39PM
May 02, 2006 02:45PM
May 02, 2006 03:00PM

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.