Optimizing Substring Query

[spicedh]

Hi,

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. :-)

Thanks.