SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    messing with my mind fristi's Avatar
    Join Date
    Feb 2009
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    string function speed?

    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.

    Code SQL:
    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

    Code SQL:
    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
    To PHP or to Perl, that is the question!
    (Bucket - simpletest) User

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    messing with my mind fristi's Avatar
    Join Date
    Feb 2009
    Posts
    292
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i wonder who might have such a table handy...
    Cool, you know somebody like that? Maybe you can ask him




    Quote Originally Posted by r937 View Post
    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?
    To PHP or to Perl, that is the question!
    (Bucket - simpletest) User

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by fristi View Post
    Is there a way around the cache without turning it off completely?
    reboot your server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •