6

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

I have a question about performance in MySQL. Before the start sorry for my bad English.

It's the question: what is performance differences between asterisk and field names in comma separated.

For example: a have a table in "example_table" named and 5 fields "f1, f2, f3, f4, f5" named.

select * from `example_table`;

or

select f1, f2, f3, f4, f5 from `example_table`;

Thanks for replies.

Community
  • 1
  • 1
ahmetertem
  • 242
  • 6
  • 14
  • 1
    I know that this is a dupe but seems SO doesn't allow you to search for `*` – Martin Smith Aug 27 '11 at 20:26
  • My colleagues, much better than me in database management, always told me not to use `SELECT *` because it takes a lot of time more than expliciting fields you need... – Marco Aug 27 '11 at 20:29

2 Answers2

6

If you select all fields there will be no measurable performance difference.

This improves code readability, as you know exactly which fields the query will return.

If you don't select all fields the second form will have a slightly better performance because MySQL will send less data on the network.

This may also cause less disk reads if the table contains TEXT or BLOB columns and you don't ask these columns. These types are not stored directly in the rows, so this would avoid some extra disk reads.

There will be no measurable performance difference if you select all fields, though.

Arnaud Le Blanc
  • 98,321
  • 23
  • 206
  • 194
4

There will be no performance difference in the execution of this query; the two queries are equivalent. Both select all columns in the table.

It will probably take some extra nanoseconds to parse the second query simply because there are more characters to parse, but that will not cause any significant (or even measurable) difference.

However, consider what will happen if an extra column is added to the table later. If this column is not needed in the original query, then it will still be projected in the query and transferred to the calling application. This may indeed create measurable slowdown in the application. Therefore, it is still recommended to only select the columns you need. (If you are writing code to display entire database tables, then of course * will be appropriate as the task at hand is "fetch all the columns in the table.")

cdhowie
  • 158,093
  • 24
  • 286
  • 300
  • I think @arnaud576875 is right if a table contains many fields and user needs only some... – Marco Aug 27 '11 at 20:27
  • 1
    He is right in that scenario, but that is not the scenario the OP is asking about. – cdhowie Aug 27 '11 at 20:28
  • Oh, you're right, I didn't notice example_table has exactly five fields. OK, so you're right !!! :) – Marco Aug 27 '11 at 20:31