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.