Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1030
  • Last Modified:

SQL Performance question * vs field names

What returns a quicker result set:

A) SELECT * FROM TABLE WHERE COLUMN='STRING';

B) SELECT FIELD.TABLE, FIELD2.TABLE, FIELD3.TABLE WHERE COLUMN='STRING';
0
jerelw
Asked:
jerelw
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The * returns all columns, and your other query returns three, so assuming you are going up against the same table in both queries, the one with the fewer number of columns would be faster.
0
 
jerelwAuthor Commented:
I heard a rumour that in MySQL, it's faster to select *,... just checking if there's any truth to the rumour.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'm not aware of that.  Since your question is MySQL-specific, it would be better if it was asked in the MySQL Topic Area.
http://www.experts-exchange.com/Databases/Mysql/
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
In general specifying the actual column names you require will be faster as that allows an optimum
access path to be chosen by the database manager...

in the case where a whole row is being returned anyway then * is possibly faster as less parsing time may be
required...

however how often will the application require access to all columns on the row... (very infrequently in my
experience)

so specifying column names will NORMALLY be the best option...

0
 
MohanKNairCommented:
"select * from tabename" can be slow in some cases where large text fields stored in different blocks are invovled in the query. If all columns can be fetched from the same block then both queries take same access time.
0
 
nakul_vachhrajaniCommented:
The selection of specific columns is faster than selecting all columns by using SELECT *. I agree with "MohanKNair" when he says that the performance hit is particularly felt when you have large text fields stored in different blocks.
Also, using SELECT * is a bad programming practice - even if you want to select all columns, use SELECT a, b, ... - that not only executes your queries faster, but prevents you from unwanted effects of addition of a new column which you might not want in your result-set.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now