SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Why shouldn't we use SELECT *?

    This old chestnut. I don't do it, myself, but I've found our new guy doing it a bit and, before I pull him up on it, I'd like to know what I'm talking about.

    Sure, it can confuse a multi-table join with same names, but in the instance of SELECT * FROM a_table this isn't an issue.

    Any input? Let's try not to be too vitriolic. I know what a sensitive issue this is!

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,809
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    For one thing, if the table is altered to add extra fields that the current program doesn't need then it iis retrieving fields unnecessarily. It is anyway if it isn't using all the fields returned.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It makes life a hell of a lot easier when you're displaying the results of queries in your application code, as you don't have to keep switching between your code and your database software to see what the columns are called. They're right there in front of you in the query.

    Also, it's very common to use functions and expressions when selecting data, which makes doing something like SELECT *, field + field2 AS total, LTRIM(field3) AS some_value FROM tablename; inefficient as you'll be effectively selecting values multiple times.

    Edit: it also puts more of a load on your database server as the query parser has to scan through the table definitions to find out what the columns are called.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    vitriolic? sensitive issue?

    we just don't like to see noobs hurting themselves, that's all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Edit: it also puts more of a load on your database server as the query parser has to scan through the table definitions to find out what the columns are called.
    Using select * will not increase the load on the db server. If columns are specified explicitly, the dbms still needs to access the table definition in order to verify each column and get attributes for the columns. If anything, select * requires less processing in the server as compared to specifying all columns explicitly.

  6. #6
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    #1 The main issue with it is not code/server load.

    It's network traffic.

    Once your site gets big enough, you will move your DB on some other server (not web server). And if you have SELECT * FROM .. and return 50 fields, where you only actually need 5 on that page, then you just sent over allot of extra data from the DB server to the WEB server.

    #2 The second issue with it it's memory.

    Say your displaying an inbox, and have your message table like: id, from, to, title, body.

    If you SELECT *, you load the "body" of all message to be shown on the page, and keep them in memory (while the script executes), even if you never use it.

    This in turn using more ram, and if you have enough traffic to run out of ram, you go in swap mode, slowing down your site (until you have enough free ram).

    #3 for most small sites, it's like r937 said it. Novice programmers will mess up th field/variable names, specially when adding new fields. So it's better to only deal with what your 100% sure that your program will have to deal with, rather than with everything (including things you never thought will be added),


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
  •