Performance issue in using SELECT *? [duplicate]
Take a look at this post:
What is the reason not to use select *?
and these:
- Performance benefit when SQL query is limited vs calling entire row
- Which is faster or best, select * or select column1 colum2 column3
- SQL query question select * from view or select col1 col2 col3 from view
- Which is faster, select * or select column1 column2 etc
If you need a subset of the columns, you are giving bad help to the optimizer (cannot choose for index, or cannot go only to index, ...)
Some database can choose to retrieve data from indexes only. That thing is very very helpfull and give an incredible speedup. Running SELECT * queries does not allow this trick.
Anyway, from the point of view of application is not a good practice.
Example on this:
- You have a table T with 20 columns (C1, C2, ..., C19 C20).
- You have an index on T for (C1,C2)
- You make
SELECT C1, C2 FROM T WHERE C1=123 - The optimizer have all the information on index, does not need to go to the table Data
Instead if you SELECT * FROM T WHERE C1=123, the optimizer needs to get all the columns data, then the index on (C1,C2) cannot be used.
In joins for multiple tables is a lot helpful.
The only performance issue will be if your application only needs a subset of the fields returned by select *. There is no performance difference in the database as they are effectively the same thing.