Solved

SQL Performance question * vs field names

Posted on 2006-06-22
7
1,015 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 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
Independent Software Vendors: 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!

 
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…

738 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