Solved

SQL Performance question * vs field names

Posted on 2006-06-22
7
1,006 Views
Last Modified: 2009-08-05
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
Comment
Question by:jerelw
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16963022
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
 
LVL 3

Author Comment

by:jerelw
ID: 16963054
I heard a rumour that in MySQL, it's faster to select *,... just checking if there's any truth to the rumour.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16963130
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 25 total points
ID: 16963298
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
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16981468
"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
 
LVL 5

Accepted Solution

by:
nakul_vachhrajani earned 25 total points
ID: 16981515
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now