string function speed?

[fristi]

Hi,

I`m working on a huge DB, couple of million records. So Query optimization is very important. And I was wondering what is the fastest way to go.
Using alot of string funtions and 1 simple select, or use only 1 string function but 2 selects.

SImple Example to show what I`m talking about.
Because there are more WHERE clauses to use the indexes ect.

SELECT
    REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX())) AS 'column'
    REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX()))/200 AS 'result'
FROM
    TABLE
WHERE
    REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX())) > 1000
AND
    REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX()))/200 < 50
AND
    REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX()))/200 > 20

OR

SELECT
    `column`,
    `column`/200 AS 'result'
FROM
    (SELECT
          REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX())) AS 'column'
     FROM
          TABLE
     ) AS temp
WHERE    
    `column` > 1000
AND
    `column`/200 < 50
AND
    `column`/200 > 20

Thanks for any reply

[r937]

the best way to determine if there is a difference in performance is to try both methods against a table with, say, a couple of million rows

i wonder who might have such a table handy...



actually, i don't think there will be a difference, but i'd be interested to find out your results

[fristi]

i wonder who might have such a table handy...

Cool, you know somebody like that? Maybe you can ask him

the best way to determine if there is a difference in performance is to try both methods against a table with, say, a couple of million rows

The problem I have to test it, is that it caches the results, so after I ran it only once, I cant benchmark it anymore.

Is there a way around the cache without turning it off completely?

[r937]

Is there a way around the cache without turning it off completely?

reboot your server