-1

Possible Duplicate:
Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

Is there a big performance impact in doing a SELECT * FROM table_name than doing SELECT specific_columns FROM table_name?

Community
  • 1
  • 1
Anthony Gainor
  • 1,149
  • 2
  • 10
  • 9
  • 2
    shortly put, yes there is. listen to your elders ... :P – Alex Jul 24 '12 at 21:36
  • 3
    The list after the SELECT keyword selects **columns** not rows. –  Jul 24 '12 at 21:37
  • 1
    In most cases there is no visible difference (even though a lot of people think there is) – zerkms Jul 24 '12 at 21:37
  • There is never a need to use `SELECT *` unless you plan to use most of those columns. Selecting the ones you need is better most of the time. – Nathan Jul 24 '12 at 21:37
  • http://stackoverflow.com/questions/2232468/select-statement-performance-using-versus-a-list-of-field-names – Alex Jul 24 '12 at 21:42
  • The biggest performance hit is in network bandwidth use (thereby causing slower performance for other things). The more columns you return, the more bandwidth is used to transport it from the server to the workstation. Why ask for columns you're not using and use the extra bandwidth just because you're trying to avoid some keystrokes? – Ken White Jul 24 '12 at 21:44
  • @Ken White: it's indeed valid, but *only* for huge projects, when we have: 1) a lot of rows 2) a lot of columns 3) a lot of simultaneous queries 4) a lot of rows to be fetched. If we have all 4 items in a moment - there is a chance there is a developer who know how to deal with all of it :-) – zerkms Jul 24 '12 at 21:46
  • @zerkms: Not true. Smaller apps with lots of users will matter as well. Doing things wrong just to avoid doing them right is never a valid reason. What happens when, two years from now, the number of columns has grown dramatically, or the data you thought would only be a few hundred rows grows to a few million, or the number of users you thought would only be a handful has now turned into hundreds? Do it right the first time and then you don't have to worry about it later. – Ken White Jul 24 '12 at 21:48
  • @Ken White: no one, even facebook and twitter, do things right from the very beginning. They solve problems continuously as they appear. And I can assure that the discussed problem won't be the number one in case if you grow from the project written by newbie to the project used by millions every day. – zerkms Jul 24 '12 at 21:50
  • Thank you very much I will now edit mysql queries to select only the rows that I really need. I don't want my application slowing down in the future – Anthony Gainor Jul 24 '12 at 21:51
  • @Ken White: you see - OP even confuses rows and columns (and even after there was a comment mentioning it is wrong)... We may imagine the quality of the overall "architecture" now ;-) So `SELECT *` is not even a problem – zerkms Jul 24 '12 at 21:51
  • You do them as right as you can, and limit changes needed later when possible. As I said, doing thing the wrong way to avoid doing them right is never the answer. Regarding your last comment: Shouldn't we be trying to teach *the right way* here when possible? – Ken White Jul 24 '12 at 21:52
  • @Ken White: we should, yes. But this is a sort of micro-optimization, that always proposed as a panacea for all DB-related issues. And this makes me sad. And that's why I advocate `SELECT *` – zerkms Jul 24 '12 at 21:56
  • 1
    @zerkms: I guess we'll have to agree to disagree, then. :-) It's been discussed to death in the linked question, anyway. (And I disagree with the `micro-optimization`, BTW; that's like saying it's OK to write your own code instead of using the compiler's RTL because you don't know the RTL code is faster, or that you don't need indexes on your tables until you actually notice that queries are running slower.) – Ken White Jul 24 '12 at 21:59
  • The latter is slower, assuming you ave an average typing speed. – nanofarad Jul 25 '12 at 15:23

3 Answers3

1

Yes, it affect performances, especially when you select multiple rows. Select only fields you really need.

Let's take a simple username existance check as example:
Why you'd select everything, when you can select only ID? Both doing the job, but selecting only one field is much better solution.

SELECT `id` FROM users WHERE `username` = 'Nikola K.'

rather than:

SELECT * FROM users WHERE `username` = 'Nikola K.'
Nikola K.
  • 7,093
  • 13
  • 31
  • 39
0

I would suggest using EXPLAIN to find the best method to suit your needs. I suppose it would depend on how many columns you have in your table.

The following are some useful sites on MySQL Explain:

http://dev.mysql.com/doc/refman/5.0/en/explain.html

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

http://weevilgenius.net/2010/09/mysql-explain-reference/

ajtrichards
  • 29,723
  • 13
  • 94
  • 101
  • `EXPLAIN` will be different for them only in specific cases. So *generally* `EXPLAIN` is the same for both – zerkms Jul 24 '12 at 21:40
0

Select only what you need! When in development I use * because it's easier when I don't really know what I'll be needing, but in the final version you really should be specific. Why not?

Viktor
  • 487
  • 2
  • 8
  • 26