MySQL: what query is faster and why?

Discussion in 'MySQL' started by N_F_S, Jul 8, 2007.

0
  1. #1 
    SELECT * FROM table WHERE ID=23 AND pin BETWEEN 60 AND 70 ORDER BY pin DESC;
    Code (markup):
    OR

    SELECT * FROM table WHERE ID=23 AND pin > 60 ORDER BY pin DESC LIMIT 10;
    Code (markup):
    Both produce 0.00 time execution, so they are almost the same?
     
    N_F_S, Jul 8, 2007 IP
  2. tamilsoft

    tamilsoft Banned

    Messages:
    1,155
    Likes Received:
    78
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Check again with larger data
     
    tamilsoft, Jul 9, 2007 IP
  3. ProgrammersTalk

    ProgrammersTalk Peon

    Messages:
    684
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I'm not sure with this, but I guess it would be the same because the way computer think is one way anyway, unless if it's a dual core CPU...?

    the way we interpret it will be the same thing.. so i guess it's just same thing :-/
     
    ProgrammersTalk, Jul 9, 2007 IP
  4. xdimension

    xdimension Peon

    Messages:
    180
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #4
    It depends to your data, if there are many records which have pin between 60 and 70 I guess the second query will be faster, because it doesn't have to find all of records with pin between 60 and 70 (it will stop once the row count hit 10). Otherwise it would be slightly the same.
     
    xdimension, Jul 9, 2007 IP
  5. N_F_S

    N_F_S Active Member

    Messages:
    2,475
    Likes Received:
    56
    Best Answers:
    0
    Trophy Points:
    90
    #5
    I tested it on my local XP machine, but the real server is Dual Core

    nah, the pin field is primary, so there can only be 10 of them (unique)
     
    N_F_S, Jul 10, 2007 IP
  6. empservices

    empservices Peon

    Messages:
    975
    Likes Received:
    26
    Best Answers:
    0
    Trophy Points:
    0
    #6
    The second one as queries with 'between', 'like', 'not like', 'in' are usually CPU hogs.

    Disclaimer: This si from my oracle knowledge, not 100% sure on mysql but I would guess the same.
     
    empservices, Jul 10, 2007 IP
  7. Synch

    Synch Peon

    Messages:
    76
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #7
    They should be about the same.

    Speed note: When writing a query you should always put the where statements that eliminate the most data first (which you did).
     
    Synch, Jul 13, 2007 IP
  8. rps111

    rps111 Peon

    Messages:
    71
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    MySQL is free and well integrated with PHP....Postgres has a reputation of being slow but slight configuration can increase the speed much above the mysql.....oracle is paid but is the industry gold standard for years.

    Problems: MySQL DBs corrupt frequently when size of DB reaches above a few GBs. MS SQL and Oracle are paid. Postgres requires configuration for faster querying.
     
    rps111, Jul 30, 2007 IP