4

I don't want to assume here, I've been bitten/proven wrong before.

Any help would be appreciated

liamfriel
  • 107
  • 1
  • 11
  • 1
    possible duplicate of [What is the reason not to use `select *` ?](http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select) – Gabriele Petrioli Nov 17 '10 at 10:24

7 Answers7

16

SELECT field is faster than select *.

Because if you have more than 1 field/column in your table then select * will return all of those, and that requires network bandwidth and more work for the database to fetch all the other fields. But if you only require one field/column, the database load is less and it doesnt need to transmit unneeded information and thus take bandwidth resources uncesseraly.

rapadura
  • 5,242
  • 7
  • 39
  • 57
  • 1
    I'd upvote you, but I don't think you've actually answered the question as asked. He's trying to avoid making assumptions and accepting canned wisdom. I think he wants to see benchmark numbers; I do, too. This may explain why he hasn't accepted your answer yet, despite all the upvotes. – Warren Young Nov 18 '10 at 14:57
  • Several other benefits are raised in another answer to the same questions, I think for this question the canned wisdom is about right. The only benefit I can think of is if you're lazy and dont want to write out all the columns... for benchmarks, you an attempt it yourself as mentioned in another thread just time the commands to your database. – rapadura Nov 18 '10 at 23:36
  • I don't have big enough data sets to measure the difference. I can fake up some data and measure that, but then I've just measured fakery. I'm monitoring this question to see if anyone is going to put up a definitive answer with actual data measured on a real system. *That* I can learn something from. I've already read the canned wisdom. Now tell me something I don't already know. – Warren Young Nov 19 '10 at 00:19
  • As I said in this case the "canned wisdom" *is* the wisdom, as I mentioned before you have many more advantages of using select fields from than the star /other than/ the speed advantage - which you doubt. @Warren, Can you give us some other advantages to using select * ? Without regard to speed or performance. – rapadura Nov 21 '10 at 17:51
  • I'm not saying you should over-pull data. What I'm after is, how big is the difference? I can imagine that some DB engines would have very little speed advantage because of the way they use the disk, which will swamp any memory or IPC advantage from transferring unused data. If the difference is under, say, 5%, it's probably not worth worrying about; computers get 5% faster every 5 weeks or so. – Warren Young Nov 22 '10 at 04:21
6

Further to @AntionoP's answer, another benefit from using SELECT field is that if the field is indexed, MySQL can return the value directly from the index without needing to lookup the data row to get the result.

Jarod Elliott
  • 15,460
  • 3
  • 35
  • 34
  • any reference on `directly from the index without needing to lookup the data row to get the result.` ? – ajreal Nov 17 '10 at 12:29
  • @ajreal - This link makes reference to the fact: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html – Jarod Elliott Nov 17 '10 at 20:19
  • 1
    This is only true (or meaningful, anyway) if your query has a `WHERE` clause and the match criterion is in the same form as the index. That is to say, queries like `select field from table` or `select field from table where field LIKE 'foo%'` require a full column scan, so it won't matter that you're querying on an indexed column. – Warren Young Nov 18 '10 at 15:03
4

Select * is said to be slower that select <field list>, because the * required an extra lookup to get all columns.
Further, if that table contains more columns (that you are not interested in), why get them only to ignore them?

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111
2

Select * will retrieve all the columns which might be very big, specially if you have blob's. The golden rule is to select the fields that you need.

Luixv
  • 8,590
  • 21
  • 84
  • 121
2

You can read

Community
  • 1
  • 1
Gabriele Petrioli
  • 191,379
  • 34
  • 261
  • 317
1

Since you mention mysql, I just tested that "SELECT field" is the faster one. Why don't you just test it by yourself?

On Linux:

time (echo "SELECT * FROM table" | mysql -u username --password=passwd database > /dev/null)

time (echo "SELECT field FROM table" | mysql -u username --password=passwd database > /dev/null)
Simone
  • 11,655
  • 1
  • 30
  • 43
  • Thanks - I do not have a local development environment in work, unprofessional I know. Thanks for showing me how to test these in Linux. I have many comparisons I wish to make to optimise my queries. – liamfriel Nov 17 '10 at 11:01
  • Then I'd say that the first thing you must get is a local development environment :) – Simone Nov 17 '10 at 11:03
  • Don't execute the listed commands because they will end up in clear text in your bash history. – Natalie Adams Apr 02 '13 at 20:04
1

When using SELECT * in a nested query, sometimes the query optimizer removes the fields that you don't use in subsequent queries. So in that case it wouldn't matter. But do you trust the query optimizer of MySQL? The only way to know for sure if the SELECT * doesn't add unnecessary fields to the query plan is to directly compare both query plans by outputting them using EXPLAIN (..query..).

That said, the easiest way to be really sure is just not to use SELECT * in production queries.

littlegreen
  • 7,290
  • 9
  • 45
  • 51