Possible Duplicates:
Select * vs Specifying Column Names
Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc.
Is there a performance difference between select * from tablename and select column1, column2 from tablename?
When it is select * from, the database pulls out all fields/columns which are more than 2 fields/columns. So does the first query cost more time/resources?
In each case you always should test your changes. You could use a profiler to do that.
For mysql see : http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
If you do select * from, there are two performance issues:
there is difference, specially in case when the other columns are BLOB or (big) TEXT fields. in case, your table contains just the two columns, there is no difference.
I've checked with the profiler - and it seems that the answer is no - both queries took the same time to execute. The table had relatively small fields so the result set wasn't bloated with large quantities of data I potentially wouldn't need. If you have large data fields you don't need in your result-set don't include them in your query
The answer is YES in general! Because for small databases you don't see a performance difference ... but with biggest databases could be relevant differences if you use the unqualified * selector as shorthand!
In general, it's better to instantiate each column from which you want to retrieve data!
I can suggest you to read the official document about how to optimize SELECT and other statements!
And while this may not make a big difference with one query run one time, if you use select * through the application, and especially if you use it when you are doing joins where you are definitely returning unneeded data, you are clearly slowing down the system for no good reason other than developer laziness. Select * should almost never be used on a production system.
Well, I guess it would depend on which type of performance you're talking about: Database or Programmer.
Speaking as someone who has had to clean up database queries that were written in the
select * from foo
format, it's a total nightmare. The database can and does change, so now the neat and tidy normalized database has become denormalized for performance reasons, and the host of sloppy queries written to grab everything are now slogging tons more data back.
If you don't need it, don't ask for it. Take a moment, think of the people who will follow after you and need to deal with your choices.
I've still got an entire section in our LIMS to uncluster, thanks for reminding me. -- Sigh