Results 1 to 6 of 6

Thread: "select * " vs "select col1, col2 "

  1. #1
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,731
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default "select * " vs "select col1, col2 "

    Two questions. Which is faster? select * from table1 where (col1 like term) and (col2 like term).

    or is this faster:

    select col1, col2 from table1 where (col1 like term) and (col 2 like term).

    My queries are dynamic and therefore can get quite large quickly depending on the user. Would it better (ie faster) if I break the queries up into bite size pieces and maybe combine them with a bit of PHP later, or just leave them as is?

  2. #2
    Join Date
    Sep 2006
    Location
    St. George, UT
    Posts
    2,769
    Thanks
    3
    Thanked 157 Times in 155 Posts

    Default

    I would have to say this would be faster due to the byte size of it.

    Code:
    select * from table1 where (col1 like term) and (col2 like term)
    The bigger the total file size, the slower the script will run. Hope this helps.
    Last edited by thetestingsite; 04-01-2007 at 05:09 PM.
    "Computer games don't affect kids; I mean if Pac-Man affected us as kids, we'd all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music." - Kristian Wilson, Nintendo, Inc, 1989
    TheUnlimitedHost | The Testing Site | Southern Utah Web Hosting and Design

  3. #3
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,731
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    cool, thanks. That actually will allow me to write far smaller queries with the use of arrays to make queries that are maybe 100 characters long as opposed to 2000 or so.

    What I was thinking was that the query was sent to the database which receives it, then processes it there. If that were the case I would imaging that it might be faster under certain circumstances to write longer scripts that specify exactly what the server that contains the database should search as opposed to shorter ones where the server must search through the complete table before coming up with the desired results. heh, I guess I could have said all that in my first post.

    Either way, thanks for the info and the quick response

  4. #4
    Join Date
    Sep 2006
    Location
    St. George, UT
    Posts
    2,769
    Thanks
    3
    Thanked 157 Times in 155 Posts

    Default

    Yea, no problem. As far as I know, the entire script must pass through the PHP processor first, then (once it finds the mysql stuff) it goes to that and queries the database. However, testing different scenarios would be the best bet (to figure out what would be faster).

    Hope this helps.
    "Computer games don't affect kids; I mean if Pac-Man affected us as kids, we'd all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music." - Kristian Wilson, Nintendo, Inc, 1989
    TheUnlimitedHost | The Testing Site | Southern Utah Web Hosting and Design

  5. #5
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,878
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    That's not a problem, the query is compiled into a binary form before sending and is really rather tiny. The execution's pretty fast too. However, if you have 15,000 rows in table users, which has twelve columns, and you select * when you only want a list of email addresses, you're transferring 12× more data than you need to. Selecting only the columns you need is always more efficient.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  6. #6
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,731
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Ah, thank you. I do have very small tables, but as tables grow I want to be sure that the search queries that I have will search them in the most efficient way. The cols contain fields that can be as large as pages of text or as small as a few words.

    Thanks for the help. What you said makes a lot of sense.

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
  •