SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: efficiency

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    efficiency

    Code:
     where1
    where message not like '%myWord'
    
    
    where2
    where right(`message`,6) <>'myWord'
    Which is more efficient in performance between where1 and where2?

    I think that 'Index' doesn't work Both where1 and where2.


    I guess where2 is more efficient because where1 is indefinate but where2 is definate....

    What is your guess?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    according to the mysql manual, neither will use indexes. if you will be doing lots of searches like that, it may be worth it to store the data in a second column where the text is reversed and indexed. doing [b]like 'droWym%'[b] does use an index.

    note that <> is case sensitive, but like is not.

  3. #3
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is your guess?
    No need to guess.
    Code:
    mysql> select benchmark(1000000, 'abcdefoo' not like '%foo');
    +------------------------------------------------+
    | benchmark(1000000, 'abcdefoo' not like '%foo') |
    +------------------------------------------------+
    |                                              0 |
    +------------------------------------------------+
    1 row in set (0.41 sec)
    
    mysql> select benchmark(1000000, right('abcdefoo', 3) <>  'foo');
    +----------------------------------------------------+
    | benchmark(1000000, right('abcdefoo', 3) <>  'foo') |
    +----------------------------------------------------+
    |                                                  0 |
    +----------------------------------------------------+
    1 row in set (0.20 sec)
    So, right() "as such" is twice so fast, but this difference doesn't really matter. Function execution time is insignificant compared to table scan time.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by stereofrog
    Function execution time is insignificant compared to table scan time.
    yup

    but what if you had two sql solutions to a particular problem, one involving a function and the other one not?

    and what if the solution involving the function forced a table scan, whereas the solution not involving a function utilized the index?

    then function execution time itself may be insignificant, but the function solution may be the wrong one to use, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, but neither of OP's constructs uses index, right? So, the answer to his/her question
    where message not like '%myWord'
    where right(`message`,6) <>'myWord'

    Which is more efficient in performance?
    would be "neither"


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
  •