1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Dangers of using SELECT *

Discussion in 'Databases' started by tlshaheen, May 27, 2010.

  1. #1
    What are the dangers of using SELECT *? I've always been taught to explicitly define what fields i want to select, i.e SELECT user_id, password, address, and not to use SELECT *. Why? Whats the problem/danger of using *?
    SEMrush
     
    tlshaheen, May 27, 2010 IP
    SEMrush
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,661
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    It's not necessarily dangerous, it's just slow, possible very slow, and lazy coding.

    If you table has 5 small columns, performance-wise, it probably doesn't matter, but if you have text or blob fields or lots of columns and you don't need all of them, select * is a huge waste of resources.

    It's also lazy coding as you then have to look at the database to find out the column names instead of looking at the query. You should always know what you are selecting and select only what you need.
     
    jestep, May 27, 2010 IP
    tlshaheen likes this.
  3. Rian

    Rian Well-Known Member

    Messages:
    1,763
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    125
    #3
    I totally agree, I made myself guilty of lazy coding by using the select * statement. I had a large number of databases, tables and fields, which significantly slowed down my performance and was a heavy load on my server resources. Fixed it all, selected only the fields I required and everything was back to normal.
     
    Rian, May 27, 2010 IP
  4. tlshaheen

    tlshaheen Peon

    Messages:
    89
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thanks for the responses, very helpful! Thanks guys!
     
    tlshaheen, May 27, 2010 IP
  5. echipvina

    echipvina Active Member

    Messages:
    145
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #5
    Select * will select data from all the columns. If you want specified columns, then you can specify it. But there wont be any performance issue when using * and all_Columns
     
    echipvina, May 27, 2010 IP
  6. Rian

    Rian Well-Known Member

    Messages:
    1,763
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    125
    #6
    I agree with you, but if you have databases like me where some databases have 145 tables - you do not want to use select* if you do not need all the tables though. I definately learned that this has serious performance issues.
     
    Rian, May 27, 2010 IP
  7. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,661
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #7
    There's huge performance issues. Like I said in my original post, if you have 5 columns or a low number it's not going to matter. Text and blobs are really bad as they will create a temp table every time you include one whether you use it or not.

    Also, let's say you are sharing or distributing code. Your query in your code contains:

    SELECT * FROM users;

    What columns will I get from that query?
     
    jestep, May 27, 2010 IP
  8. tks

    tks Well-Known Member

    Messages:
    89
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    108
    #8
    I have a similar query! If I open multiple tables using alias will it a performance issue?
     
    tks, May 29, 2010 IP
  9. iamscottj

    iamscottj Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    This can be very heavy query depending upon the size of the database. The main aim for any DBMS is to get the results quicker. By using SELECT *, we don't get any kind of performance benefits that we would get if we use some other techniques like selecting the rows and using proper filters by using a WHERE clause.
     
    iamscottj, Jun 7, 2010 IP
  10. iimmdeepak

    iimmdeepak Peon

    Messages:
    208
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #10
    i think it is also good for the security purpose may be because if * some information is out which should not go
    and another reason can b of unnecessary data which is not relevant is out due to laziness.
     
    iimmdeepak, Jun 10, 2010 IP
  11. marry outsource project

    marry outsource project Greenhorn

    Messages:
    23
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #11
    I am not sure. So I want you to suggest google
     
    marry outsource project, Jun 18, 2010 IP
  12. Skuvnar

    Skuvnar Peon

    Messages:
    32
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #12
    using * in your select statements will cause the database to return columns that you might not need, and therefore wasting the database's time.
     
    Skuvnar, Jun 29, 2010 IP
  13. mrx345

    mrx345 Peon

    Messages:
    68
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #13
    You have table1 with column A B
    You have table2 with column C
    You have query in your application: select * from table1, table2 where bla-bla-bla

    Now, imagine someone adds column B into table2

    Ops – your query doesn’t work any more!
     
    mrx345, Jul 23, 2010 IP
  14. skytide

    skytide Peon

    Messages:
    25
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #14
    I would NEVER use select *. Not just for the performance reasons, but also for maintenance reasons. If you use SELECT *, and you decide to modify your database structure in the future, it can really complicate things. It's bad practice to use SELECT * unless you are just running an ad hoc query.
     
    skytide, Jul 30, 2010 IP
  15. CoderJosh

    CoderJosh Peon

    Messages:
    45
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #15
    Performance can be an issue, depending on how much more data has to be transferred that isn't really needed.

    Another problem with "SELECT *" arises if the structure of the database is changed at a later time, depending on how you refer to the columns in your script. Explicitly SELECTing only the columns you need is much safer.
     
    CoderJosh, Aug 2, 2010 IP
  16. TomDeSuecia

    TomDeSuecia Greenhorn

    Messages:
    47
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    16
    #16
    A rule of thumb is never to select more data then you need. Simple as that. Yes, it might be very convenient to use SELECT * but for example if you go to a store and want to read about the new horse breath in Wales you don't buy all the magazines because it is cost and time consuming.

    Cost being bandwidth and time being the time of retreiving the data.
     
    TomDeSuecia, Aug 17, 2010 IP
  17. Oskar28

    Oskar28 Active Member

    Messages:
    564
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    60
    #17
    No danger at all.. but as long as you need not all the columns then just specify it on your Select statement, optimization wise..
    but if you really need to query all the columns.. then use it.. :)
     
    Oskar28, Aug 17, 2010 IP
  18. dodolls

    dodolls Well-Known Member

    Messages:
    282
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    108
    #18
    It can also cause you headache when you start to deal with the JOIN command or other queries that may require you to pull records from two or more tables.
     
    dodolls, Aug 17, 2010 IP
  19. StevenLuck

    StevenLuck Peon

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #19
    Even if you are going to use all the columns in a table, I still suggest selecting all the columns, e.g. "select column1, column2, column3, ..., columnn from tablename". It's not all about performance issue, that will make it slower, but also about future update. When you are going to add some columns on the table in the future, you will have to change all your queries in case the data you need is different from what is added. Gosh... That will be tiring. So, why not make it properly once, then use it for life-time? :p
     
    StevenLuck, Aug 19, 2010 IP
  20. Ellie33639

    Ellie33639 Peon

    Messages:
    11
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #20
    Select * will select data from all the columns. If you want specified columns, then you can specify it. But there wont be any performance issue when using * and all_Columns
     
    Ellie33639, Nov 10, 2010 IP