MySQL Forums
Forum List  »  Optimizer & Parser

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

Peter Brawley wrote:
> MySQL doesn't yet index on functions. In any event, the column as you define
> it breaks the atomicity rule. Why not split the field on input into id and
> manufacturer, index both, then CONCAT(id,'/',manu) when you have to?

Thanks Peter.

Sorry if I wasn't clear, the product name as I have it is the _actual_ catalogue number we use, it's not just composed of a concatenation at insert time. Just so you know why they're stored in this way at the moment. It's more like having a table of barcodes, but it just so happens that you know that the last digit of a barcode is interesting by itself. If that makes sense!

Having said that, yes, I could split them at an earlier stage and CONCAT - but then searching for productname='xyz' will have to call CONCAT and I'll be in the same situation I was before, except searching by manufacturer will be fast and exact product name will be slow. No?

Unless CONCAT(name, man) is significantly faster than SUBSTRING_INDEX(name, '/', -1) - which I suspect it is. I'll try some tests.

Edit: Sorry, if I'd thought about it more I'd have realised that I can split the query after the user enters it by the last /, then do WHERE name='nn' AND manu='mm' or whatever. Thanks :)

Edited 1 time(s). Last edit at 05/02/2006 02:58PM by spicedh.

Options: ReplyQuote

Written By
May 02, 2006 01:01PM
May 02, 2006 02:39PM
Re: Optimizing Substring Query
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.