?
Solved

SQL Performance question * vs field names

Posted on 2006-06-22
7
Medium Priority
?
1,027 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 66

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 66

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 75 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 75 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

Technology Partners: 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!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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