MySQL performance selection

Question

Possible duplicate:
Which is faster / better? SELECT * or SELECT column1, colum2, column3 etc.

I have a question about performance in MySQL. Sorry for my bad english before starting.

The question arises: what are the differences in performance between asterisks and field names in comma-separated.

For example: a has a table in the name "example_table" and 5 fields "f1, f2, f3, f4, f5" with the name.

select * from `example_table`; 

or

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

Thanks for answers.

Answer 1

If you select all fields, there will be no noticeable difference in performance.

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

If you do not select all the fields, the second form will have slightly better performance, because MySQL will send less data to the network.

This can also result in less disk reads if the table contains TEXT or BLOB columns, and you do not query these columns. These types are not stored directly in strings, so this will avoid reading extra disks.

If you select all fields, there will be no noticeable difference in performance.

Answer 2

There will be no performance difference when executing this request; two queries are equivalent. Both select all columns in the table.

It will probably take a few extra nanoseconds to parse the second query simply because there are more characters for the syntax, but this will not cause a significant (or even measurable) difference.

However, consider what happens if an additional column is added to the table later. If this column is not needed in the original query, it will still be projected into the query and passed to the calling application. This can really cause a noticeable slowdown in the application. Therefore, it is still recommended that you select only the columns you need. (If you write code to display entire database tables, then of course * will be appropriate, since the task is to "select all columns in the table.")