SEO

I am extensively using string functions on a table fields to extract
suitable data.

Unfortunately, I do not understand how to avoid to repeat them (that I
assume is wasting CPU cycles).

Just an example

SELECT * FROM a bunch of tables WHERE other conditions AND
SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1) BETWEEN
X AND Y OR SUBSTRING_INDEX(REPLACE(REPLACE(peso,",","."),"
g",""),"-",1) BETWEEN X AND Y OR
(SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1)<X
AND SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1)>Y)

Ok, ok there is an error in the function usage, but this is not the
point, it is only an example.

You can see that "SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".") ,"
g",""),"-",1)" is reapeted 4 times, is there any way to use it only
once?

Thanks!
M.

On 23 Apr, 06:49, incuso <numismat...@gmail.com> wrote:
> I am extensively using string functions on a table fields to extract
> suitable data.
>
> Unfortunately, I do not understand how to avoid to repeat them (that I
> assume is wasting CPU cycles).
>
> Just an example
>
> SELECT * FROM a bunch of tables WHERE other conditions AND
> SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1) BETWEEN
> X AND Y OR SUBSTRING_INDEX(REPLACE(REPLACE(peso,",","."),"
> g",""),"-",1) BETWEEN X AND Y OR
> (SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1)<X
> AND SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1)>Y)
>
> Ok, ok there is an error in the function usage, but this is not the
> point, it is only an example.
>
> You can see that "SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".") ,"
> g",""),"-",1)" is reapeted 4 times, is there any way to use it only
> once?
>
> Thanks!
> M.

Put it in the SELECT part, alias it and use the alias in a HAVING
clause

On 23 Apr, 06:49, incuso <numismat...@gmail.com> wrote:
> I am extensively using string functions on a table fields to extract
> suitable data.
>
> Unfortunately, I do not understand how to avoid to repeat them (that I
> assume is wasting CPU cycles).
>
> Just an example
>
> SELECT * FROM a bunch of tables WHERE other conditions AND
> SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1) BETWEEN
> X AND Y OR SUBSTRING_INDEX(REPLACE(REPLACE(peso,",","."),"
> g",""),"-",1) BETWEEN X AND Y OR
> (SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1)<X
> AND SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".")," g",""),"-",1)>Y)
>
> Ok, ok there is an error in the function usage, but this is not the
> point, it is only an example.
>
> You can see that "SUBSTRING_INDEX(REPLACE(REPLACE(peso,",",".") ,"
> g",""),"-",1)" is reapeted 4 times, is there any way to use it only
> once?
>
> Thanks!
> M.

Also, you might want to re-visit a design that causes you to have to
use such expressions in the select clause in the first place.

On 23 Apr, 12:06, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> Also, you might want to re-visit a design that causes you to have to
> use such expressions in the select clause in the first place.

Thanks, it works fine and faster!

the DB already contains a lot of data (it is a wiki like web site) and
therefore I prefer to not touch its structure at least when the
problem is not so critical. Basically I improved a search function not
often used.

Thanks again,
Massimo
--
http://numismatica-italiana.lamoneta.it