?
Solved

SQL Performance question * vs field names

Posted on 2006-06-22
7
Medium Priority
?
1,035 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
6 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

589 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