SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru Angry Coder's Avatar
    Join Date
    May 2002
    Location
    Canada
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation MySQL: "SELECT * FROM TABLE" OR "SELECT filed1, filed2 FROM TABLE" Is Faster?!

    Let's say I have a table with three fileds:

    1. ID
    2. Name
    3. Email


    I was wondering, which one is faster from the fallowing statements:

    "SELECT * FROM TABLE"

    OR

    "SELECT ID, Name, Email FROM TABLE"

    ?

    Thank you.
    Why It Doesn't Work?!

  2. #2
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your case, they're the same, I should think.

    The difference is when you name less columns than the table has, in which case it's faster.
    ----Adopt-a-Sig----
    Your message here!

  3. #3
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select benchmark(100000000, "select * from test.test")

    1.78 seconds

    select benchmark(100000000, "select id, name, email from test.test")

    1.80 seconds

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hey, that's neat

    i would've figured the "select star" to take slightly longer

    anyhow, it's as good as a dead heat

    therefore, perhaps we should look at other considerations

    instead of pure database timings (where did the records go? i am certain the "benchmark" function did not ship a hundred million records across a network), let's try the same test in php

    let's make sure the table has several wide columns, such as a couple of large varchars with real data in them

    now, compare the timings for shipping entire rows from mysql across to php, versus shipping only the columns you will actually use

    make it a realistic test, ship a few gigabytes you don't need

    true, if as in this case you need all the columns, then sure, knock yourself out with that asterisk

    but in most database queries, you aren't using all the columns

    rudy

  6. #6
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    ...compare the timings for shipping entire rows from mysql across to php, versus shipping only the columns you will actually use
    The original question was; is "select * ..." faster/slower than "select field1, field2, field3 ..." when there are only these three fields in the table

    The answer is; no

    Good practise is; always specify the fields, particularly when you have blob fields, e.g. "select id, name, email, image, long_description ..." is better than "select image, long_description, id, name, email ..."

  7. #7
    SitePoint Guru Angry Coder's Avatar
    Join Date
    May 2002
    Location
    Canada
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry seanf, I searched (Ctrl+F) for MySQL but I didn't found it

    Thanks a lot guys.
    Why It Doesn't Work?!


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
  •