Dangers of using SELECT *

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 *?

 

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.

 

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.

 

Thanks for the responses, very helpful! Thanks guys!

 

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

 

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.

 

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?

 

I have a similar query! If I open multiple tables using alias will it a performance issue?

 

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.

 

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.

 

I am not sure. So I want you to suggest google

 

using * in your select statements will cause the database to return columns that you might not need, and therefore wasting the database's time.

 

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!

 

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.

 

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.

 

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.

 

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..

 

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.

 

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?

 

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